USEFUL SITES :
Write for Us
Preferred SQL Server Index Types
When you use Query Analyzer to produce a graphical execution plan, you will notice that there are several different ways SQL Server uses indexes.
1) Clustered Index Seek: A Clustered Index Seek uses the seeking ability of indexes to retrieve rows directly from a clustered index. In most cases, they provide the best performance on SELECT statements.
In Query Analyzer, go to pubs database. Type following query:
SELECT * FROM authors WHERE au_id LIKE'2%'
Highlight the query and press. (Cntl + L) or highlight the query and press F5. You will see the following in the “Estimated Execution Plan” tab.
Take a close look at the Argument section of the above illustration. Notice that the “UPKCL_auidind” clustered index is used to retrieve the data.
2) Index Seek: An Index Seek uses a non-clustered index to retrieve data, and in some ways, acts like a clustered index. This is because all of the data retrieved is fetched from the leaf layer of the non-clustered index, not from any of the data pages. You often see this behavior in a covering index.
In Query Analyzer, go to pubs database and type following query:
SELECT title_id, title FROM titles WHERE title LIKE 't%'
Highlight the query and press. (Cntl + L) or highlight the query and press F5. You will see the following in the “Estimated Execution Plan” tab:
In the Argument section in the above illustration, note that the “titleind” non-clustered index is used to retrieve the data.
3) Bookmark Lookup: A Bookmark Lookup uses a non-clustered index to select the data. It starts with an index seek in the leaf nodes of the non-clustered index to identify the location of the data from the data pages, then it retrieves the necessary data directly from the data pages. Leaf nodes of non-clustered indexes contain row locator that point to the actual data on data pages.
In Query Analyzer, go to the pubs database. Type following query:
SELECT * FROM titles WHERE title LIKE 't%'
In the Argument section of the Index Seek, notice that the "titlecind" non-clustered index is used, but once the data pages are identified from looking them up in the leaf pages of the non-clustered index, then a Bookmark Lookup must be performed. Again, a Bookmark Lookup is when the Query Optimizer has to lookup the data from the data pages in order to retrieve it. In the Argument section of the Bookmark Lookup, note that a Bookmark Lookup called "Bmk1000" is used. This name is assigned automatically by the Query Optimizer.
