All topics about programming / development in T-SQL or other languages for SQL Server.
3 posts • Page 1 of 1
Hope that helps
- Nonclustered Indexes have Index Id that is greater than zero.
- There is a Unique Key constraint that is created on a Nonclustered Index by default.
- A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key.
- A Nonclustered Index does not actually order the data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
- It is possible for a table to not have any Nonclustered Indexes.
- The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row.
- When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created).
- It is Quicker for insert and update operations than it is on a clustered index.
- It is only possible for a table to have ONE Clustered Index.
- A Clustered Index always has Index Id of ZERO.
- A Clustered Index is created by default when creating a Primary Key on a table.
- A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key.
- If the table does not have Clustered Index it is often referred to as a "Heap".
- The leaf node of a Clustered Index contains data pages of the table on which it is created.
- Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key.
- Faster to read than non clustered as data is physically stored in index order.
Hope that helps