Indexes in SQL

An index is an internal table structure that SQL Server uses to provide quick access to rows of a table based on the values of one or more columns. Indexes in SQL Server are like the indexes at the back of a book, which help in locating content.

Advantages of Using Indexes

The primary purpose of an index is to provide faster access to data pages. Rather than scanning each page for the required data, the server will scan the index, get the address of the data storage location, and directly access the information. Indexes are also used as a mechanism of enforcing data uniqueness. Creating a unique index will force unique values into the columns. An index speeds up the processing of queries that use join or other clauses, like ORDER BY or GROUP BY, by allowing faster access to data. An indexes are said to have the following advantages:

·         Improve the speed of the execution of queries.

·         Enforce uniqueness of data.

·         Speed up joins between tables. 

Disadvantages of Using Indexes

In effectively designed, indexes improve the performance of queries. But it is not wise to index every column of table. You need to consider a few parameters while creating indexes, like:

·         It takes time to create an index.

·         Each index created requires space to store data along with the original data source- the table.

·         An index get updated each time the data is modified.

Types of Indexes

There are two types of indexes

·         Clustered Index

·         Non Clustered Index

Clustered Index

In a clustered Index:

·         The data is physically sorted.

·         Only one clustered index can be created per table, so you should build it on attributes that have a high percentage of unique and are not modified often.

How Clustered Indexes Work

In a cluster index, data is sorted at the level of the B-tree. The data page of a table are like folders stored in an alphabetical order in the filing, and the rows of data are like the document stored in folders.

NonClustered Index

 In nonclustered index:

·         The physical order of the rows is not the same as the index order.

·         Nonclustered indexes are typically created on columns used in joins and WHERE clauses, and whose values may be modified frequently.

·         SQL Server creates nonclustered index by default when the CREATE INDEX command is given

·         There can be as many as 259 (SQL Server 2005) nonclustered indexes per table.

How Clustered Indexes Work

SQL Server creates a nonclustered index by default. The data is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table.The nonclustered index tree contains key in a sorted order, with the leaf level of the index containing the pointer to the data page and the row number in the data page.

Indexes and Heap Structures

SQL Server supports indexes defined on any column in a table, including computer columns. If a table does not have any clustered index, data is not sorted in a particular order. This structure is called a heap

Each table in SQL Server is allocated one extent(eight contiguous 8KB pages) in the database file. When this one extent is filled with data, another is allocated to the table. However, these extents are not placed physically next to each other, they are scattered in the database file. Therefore, data access on a heap is slow as compared to tables have been indexed.

Features of Indexes

·         Indexes accelerate queries that join tables and  perform sorting and grouping.

·         Indexes can be used to enforce uniqueness of rows.

·         Indexes are useful on columns in which the majority of data is unique. An index on column containing a large amount of duplicate data is not useful.

·         When you modify the data of an indexed column, the associated index are automatically updates.

·         You require time and resources to maintain indexes. You should not create an index that is not used frequently.

·         A clustered index should be created before a nonclustered index. A clustered index changes the order of rows. A nonclustered index would need to be rebuilt before a clusterd index

·         Typically, nonclustered indexes are created on foreign keys

Guidelines to Create an Index

·         Identify the tables on which the index will be created.

·         Indentify the attribute on which the index will be created.

·         Identify the type of index to be created

·         Identify the name of the index to be created (Prefix an ‘idx’ to the name of the index to help in identifying the index)