Home > Software > Databases > SQL-Server
Interview Questions   Tutorials   Discussions   Programs   

SQL-Server - Difference between Cluster and Non-cluster index?

    Next Topic

asked mar August 4, 2014 04:37 AM  

Difference between Cluster and Non-cluster index?


1 Answers

answered By vikas   0  
What is an Index

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify.

Clustered Index:

1.It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. 

2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key

3. If the table does not have Clustered Index it is referred to as a "Heap"

4. A Clustered Index always has Index Id of 1

5. A Table can have ONLY 1 Clustered Index

6. A Primary Key constraint creates a Clustered Index by default

Nonclustered Index:

1. It only contains the Row Locator information in the form of Clustered Index column(if availabe) or the File Indentifier + Page Number + Total Rows in a Page. This means that the query engine must take an additional step in order to locate the actual data.

2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.

3. A table may not have any Nonclustered Indexes

4. Nonclustered Indexes have Index Id > 1

5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created

6. A Unique Key constraint created a Nonclustered Index by default

   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!