This is the time when we should talk about indexes in SQL Server. Indexes my be confusing, isnt it?. Its easy to say that Indexes are performance booster but there are so many to remember. Here I will try to keep this chaper as crispy as I can. Remember, an index is always be modified by SQL Server when inserts, updates, and deletes are performed. This will lead to CPU and disk overhead, so be wise when you create indexes and test them thoroughly. First lets see what is the differences between clustered and nonclustered indexes.
CLUSTERED And NONCLUSTERED INDEXES
Clustered index is a SQL Server index that sorts and stores data rows in a table, based on key values. We will talk about Keys later in the section.Nonclustered index is a SQL Server index which contains a key value and a pointer to the data in the heap or clustered index.
The key difference between clustered and nonclustered SQL Server indexes is that a clustered index controls the physical order of the data pages. The data pages of a clustered index will always include all the columns in the table, even if you only create the index on one column. The column you specify as key columns affect how the pages are stored in the B-tree (we will talk about B-Tree Later) index structure. A nonclustered index does not affect the ordering and storing of the data.
A B-tree structure has at least two levels – the root and the leaves. If there are enough records, intermediate levels may be added as well. Clustered index leaf-level pages contain the data in the table. Nonclustered index leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.According to Knuth’s definition, a B-tree of order ‘m’ is a tree which satisfies the following properties:
- Every node has at most ‘m’ children.
- Every non-leaf node (except root) has at least [m⁄2] children.
- The root has at least two children if it is not a leaf node.
- A non-leaf node with k children contains k−1 keys.
- All leaves appear in the same level, and internal vertices carry no information
Some good points about B-Tree
- Keeps keys in sorted order for sequential traversing
- Uses a hierarchical index to minimize the number of disk reads
- Uses partially full blocks to speed insertions and deletions
- Keeps the index balanced with an elegant recursive algorithm
Now, another good point is to know the difference between PRIMARY KEY and A CLUSTERED INDEX
Primary key is a constraint to enforce uniqueness in a table. The primary key columns cannot hold NULL values.In SQL Server, when you create a primary key on a table, if a clustered index is not defin ed and a nonclustered index is not specified, a unique clustered index is created to enforce the constraint. By default, the primary key is enforced by a unique clustered index. This is only by default, not by requirement. However, there is no guarantee that this is the best choice for a clustered index for that table.
What are key columns?
A key column is the column(s) that the index is created on, the non-key column are included columns. Exmaple makes is more clear.
CREATE NONCLUSTERED INDEX idx ON Table1 (Col1, Col2) INCLUDE (Col3, Col4)
In the above example, Col1 and Col2 are key columns, Col3 and Col4 are non-key columns
CREATE CLUSTERED INDEX cidx ON Table1 (Col1)
In the above example Col1 is the key column, and all other columns in the table are classed as non-key columns, as the clustered index is the table. A column cannot be both a key and a non-key. It is either a key column or a non-key, included column.
Now, lets conclude this chapter. There is enough informtion for you now. Remember, you need to practice. Play around with this now.