SQL Indices: A Mind Map

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B+-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Here you see a mind map of Indices, to speed-up the query response, check the first post here.


IndicesAn index is a separate physical data structure that enables queries to access one or more data rows fast N> it’s analogous to to a book index
1 Table scanIf a tale has no index each row is retrieved and examined to match the required result
2 index pages
2.1 index entryit consists of index key plus a pointer. N> it is significantly shorter than the corresponding row.
2.2 B plus -tree data structureAll records are stored at the leaf level of the tree; only keys are stored in interior node.. With index access, it takes only a few I/O operations to find any row of a table in a very short time
3 Clustered IndicesA clustered index determines the physical order of the data in a tab
3.1 clustered tableA clustered index is built by default for each table, for which you define the primary key using the primary key constraint
4 Nonclustered Indices– A nonclustered index does not change the physical order of the rows in the table. – The leaf pages of a nonclustered index consist of an index key plus a bookmark.
4.1 bookmark
5 Creating IndicesCREATE [UNIQUE] [CLUSTERED |NONCLUSTERED] INDEX index_name ON table_name (column1 [ASC | DESC] ,…) [ INCLUDE ( column_name [ ,… ] ) ] [WITH [FILLFACTOR=n] [[, ] PAD_INDEX = {ON | OFF}] [[, ] DROP_EXISTING = {ON | OFF}] [[, ] SORT_IN_TEMPDB = {ON | OFF}] [[, ] IGNORE_DUP_KEY = {ON | OFF}] [[, ] ALLOW_ROW_LOCKS = {ON | OFF}] [[, ]ALLOW_PAGE_LOCKS = {ON | OFF}] [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}] [[, ]ONLINE = {ON | OFF}]] [ON file_group | “default”] N> Each column of a table, except those with the text/image data types, can be indexed.
5.1 index_name
5.2 table_name
5.3 UNIQUEeach data value can appear only once
5.5 INCLUDEto specify the nonkey columns, which are added to the leaf pages
5.5.1 covering indexignificant performance gains can be achieved when all columns in a query are included in the index N> to include additional nonkey columns in the leaf pages of the nonclustered index
5.6 DROP_EXISTINGit allows you to enhance performance when re-creating a clustered index on a table
5.7.1 tempdb
5.8 IGNORE_DUP_KEYto ignore the attempt to insert duplicate values in the indexed column(s)
5.11 ONLINEwith this option you can you can create, rebuild, or drop an index online.
5.12 ex
 USE sample; CREATE INDEX i_empno ON employee (emp_no); 
 -- composite index USE sample; CREATE UNIQUE INDEX i_empno_prno ON works_on (emp_no, project_no) WITH FILLFACTOR= 80; 
6 Index Fragmentation Informationan index can become fragmented, meaning the storage of data in its pages is done inefficiently.
6.1 DMVyou can obtain information about the degree of fragmentation of rows on data pages
6.2 sys.dm_db_index_physical_stats
6.3 Rebuilding an Index
6.3.1 REBUILD option of the ALTER INDEX statement
6.3.2 DROP_EXISTING option of the CREATE INDEX statementN> All nonclustered indices must be rebuilt when a table’s clustered index is dropped.
6.3.3 DBCC DBREINDEX command
7 Removing and Renaming Indices
7.1 DROP INDEX statement
7.2 ex
 USE sample; DROP INDEX i_empno ON employee; 
8 Indices on Computed Columns
8.1 A computed columnA computed column is a column of a table that is used to store the result of a computation of the table’s data
8.2 Virtual Computed Columns
8.2.1 Persistent Computed Columns

One thought on “SQL Indices: A Mind Map

  1. Pingback: SQL Server 2008 – How to modify a table « Happy2koala's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s