MS SQL Server - Index Internals I

I dedicate this post of mine to my most interesting subject of MS SQL Server – Index Internals, for everyone interested in getting a depth of how Indexing in SQL Server works.

While running a query in SQL Server, many of them wonder at times “why particular query performs so terrible while the other is just fine”. One of the key factors that can affect a query performance is the Indexing available on the related tables.

Best possible way to recognize how index can benefit queries and what would make things worse; is to understand how SQL Server processes these indexes internally.

Let us dive into the details of how SQL Server stores and access data in database.

SQL Server can store data in two ways:

1. In Heap: The Order in which the data is inserted is stored in the same order in the data pages of the table.


2. Order of the Clustered Indexed Column Key: The data is added to the data pages of a table sorted in the order of the column having clustered index. Index Pages are added for the table to store the indexes.

These data in SQL Server can be accessed in two ways:

1. Table Scan: The data in the heap is accessed by scanning through each row in the data pages of the table to serve a query request.


2. Using Indexes: With the help of Indexes, SQL Server scan through the Index Pages using B-Tree traversing algorithm to arrives to the resultset in the data pages. Index scan helps SQL Server to search data in the table in a similar way as we scan through the Index page of an Atlas to find a location on the book.

Below is an illustration to help you understand the data storage and access in the SQL Server database:

• Let us have two tables, TableA and TableB that collect names of the students, with the field Name.
• TableA stores the data in the Heap
• TableB has a index defined on the field Name


• The data in both the tables are entered in following order: John, Sam, Anna, Denial, Rudd, Andrew, Samantha and Brown.


• 
  
• 
  

Later in this topic I will be discussing in detail what the indexes are, how they are stored in SQL Server and how it help in improving the performance.

continued...