In the last post
you saw that the missing index feature does not consider the order for columns to be used in an index.
In this blog I will explain another limitation of the missing index feature – it reports only include columns for some queries.
SQL Server supports two kind of row store indexes – the clustered index and then non-clustered index. It is important to understand the difference between these two indexes in order to implement the right index type to improve reading performance.
A row store index creates a B-tree based on the index key columns, where the depth-level of the B-tree depends on the number of data respectively index pages needed to store all of the data. If SQL Server looks for a specific value in an index key column it will read the root page and from there it is directed to the appropriate page of next level in the index tree. Finally, SQL Server always reads the root page, the intermediate pages and the leaf page(s). If the index is not unique, the storage engine reads backwards or forwards the appropriate leaf pages via the doubly linked chain to find all records.