MS SQL Server - Index Internals II

Following to my earlier post on how SQL Server stores and access data, lets now get into details of what Indexes and how are they processed by SQL Server for improving performance.

As per the definition “An Index is a pointer to data in a table”. Here when we say pointer it’s not much different from the pointers that we access in programming language like C/C++. If you have worked with pointers, you know that “A pointer is used to store the location of a variable or we say a Data Member”. Similarly in SQL Server an Index stores the locations of the data rows of a table and occupies a discrete memory in SQL Server.

Index is stored separately from the table for which the index is created. When an Index is defined, the Index pages are created by SQL Server to store the address of the data rows, based on the Indexed column. These Indexes are linked to each other in a B-tree structure. Let’s consider a table products with the following structure and data inserted in following order in data pages:


Now Suppose an Index is defined on the UnitPrice field of the table. The Index Pages will be defined for the table as shown below:

SQL Server uses these indexes to find data quickly when a query is processed. Indexes can be created or dropped with no effect on the data, as it is stored as separate entity from the table. Indexes operate 'behind the scenes' in support of the database engine to improve the performance of data retrieval.

Consider the following query on the products table above. This query retrieves products in a specific price range.

SELECT ProductID, ProductName, UnitPrice FROM Products WHERE (UnitPrice > 4.5) AND (UnitPrice <10)

If there was no index defined on the table, a table scan will be performed to find the products with price ranging between 7.5 and 10. There will be 24 iterations (navigating through each row) to find the required result.

With an index defined on the UnitPrice field, navigating through the Main Index Page, the query can be served in 4 iterations. Hence you can distinguish the remarkable difference an Index can make in the performance of a query.

Later in the tread I will be talking about the different types of Indexes in SQL Server.