Indexes

Question

hi, Sir i want to know what is the difference between clustered and non clustered indexes and can we create more than one clustered index in a table ?

Satyapriya Nayak · Answer

Hi Shubham, A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. You can only have one clustered index on a table. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Also Please refer Our recommended articles http://www.c-sharpcorner.com/uploadfile/nipuntomar/clustered-index-and-non-clustered-index-in-sql-server/default.aspx http://www.c-sharpcorner.com/uploadfile/6897bc/clustered-and-non-clustered-index-in-sql-2005/default.aspx Thanks

Senthilkumar · Answer

Hi Shubham, first you can't create the more than one clustered index. Lets see what is clustered index. Clustered index will have the value at the node level and while searching it will return the row directly. It can have only one per table. when you create the primary or unique then it will create the clustered index automatically. when even DML operation happens on the table, it will do the sorting based on the key column automatically. There is huge performance impact as well. Because when you have more records it needs to do sorting. Non clustered index: The non clustered index will have the index table and every record will have the reference in the index table. A table can have mutiple non clustered index. As per sql server 2005, you can create the 249 non clustered indexes and in 2008 it allows 999 indexes. when ever we search on the column, if the column is index then it will look into the non clustered index table and it will bind the equivalent record in the original table. Because every node will have the address of the original record.

Jignesh Trivedi · Answer

hi, A clustered index is a special type of index that reorders the way records in the table are physically stored.Therefore table can have only one clustered index.Accessing data using a clustered index is fastest. Clustered indexes are can slow down inserts because the physical layouts of the records have to be modified as records are put in if the new keys are not in sequential order. non clustered index is logically stored a table can have 249 non clustred index in SQL Server 2005.Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key. For SQL Server 2005: 1 Clustered Index + 249 Nonclustered Index = 250 Index http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx For SQL Server 2008: 1 Clustered Index + 999 Nonclustered Index = 1000 Index http://msdn.microsoft.com/en-us/library/ms143432.aspx hope this help.

Indexes

Answers (3)