Types of indexes: in SQL Server.

Types of indexes: We have basically two types of indexes in SQL Server.

• Clustered index:  It’s a type of index where the data itself is arranged at the indexed column i.e. we have the actual data at the leaf nodes. For example, in a science definition pocket book, every word is arranged alphabetically and the definition of the word is given along with it. Here there is no need to look up for the referencing page, as in case of other example like in a general book with indexes at the end of the book, contains page number along with the term or word which is then followed to get the information about the term.

In our above example, if we create cluster index on the column emp_id, the table itself will be rearranged according to the sorting order of emp_id.

Syntax:

USE [OLAP]

GO

/****** Object:  Index [IX_EmployeeDim]  ******/

CREATE CLUSTERED INDEX [IX_EmployeeDim] ON [dbo].[EmployeeDim]

(

        [emp_id] ASC

)

After executing the query, perform a select statement on the table [EmployeeDim], you will notice that all the rows are ordered by [emp_id].

SELECT * FROM dbo.EmployeeDim

Advantage:

• Seeking operations are much faster than normal indexing.

Disadvantage:

• It will rearrange the whole data at every operation of UPDATE, INSERT or DELETE on key column / cluster index column, in order to keep ordering of the data in that column.

• Non-Clustered index: Here leaf node contains index pages or reference to the actual data record instead of data pages (actual data). Refer the first example for details about the syntax. It will not affect the default order of the table rather a separate data structure is maintained for each index.

Advantage:

• Can have more than one non-clustered index on a table.
• Reordering of the table data is not necessary

Disadvantage:

• Need separate data structure. As a result consume more space
• Over indexing i.e. more number of indexes may cause performance degradation.

Cluster Indexing	Non-Cluster Indexing
• Actual data at the leaf nodes.	• Reference to the data on the leaf nodes.
• Data for the index column is sorted physically.	• Data in the index structure is sorted.
• Updating data in the cluster index column affects the whole table.	• Updating data does not affect the whole table, only the index structure has to be rebuild for the index column.
• Only 1 Cluster Index is possible for one table.	• Up to 249 Non Clustered Indexes are possible for one table in SQL Server 2005 and 999 in SQL Server 2008.
• Clustered index are unique in nature for a given table.	• This may not be unique.
• Can be efficient on fixed or rarely changing tables.	• Is good for table which is modified frequently.
• Index order matches the physical storage order of the data.	• Index order does not match with the physical order of the data.
• Does not required additional disk space to store data in the index as it is physically reordering the main table.	• Separate data structure is maintained for each index, resulting additional space consumption.

Other terms in Indexing:

Composite index / Covering index: When an index (either Clustered or non-clustered) is created on the basis of two or more columns from a table, it is called Composite index. These are very useful in case of covering query, also called as Covering index. For example, your requirement is to get the entire employees id in between 132 and 203. Here you may note that you got the data in the index itself. Suppose, you are having a non-cluster index on emp_id column, your query will be as below: