B-tree indexes

A B-tree index is a kind of structure that is formed as a balanced tree with connected interleaved nodes. This tree contains a sorted copy of values and indexed columns or columns when the index is non-clustered. When an index is clustered, data rows are a leaf of index and these rows are sorted according to its index key. When SQL Server searches data, it traverses the B-tree index from its root to leaf to address rows filtered by a query.

Because a clustered B-tree index controls the order of records in a table, every record must be placed exactly to a position in accordance with the index. This is why the best practice is to design clustered indexes on small columns (for example, integer), which raise the ordering of new ...

Get Hands-On Data Science with SQL Server 2017 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.