October 3, 2010 at 1:19 am
Hi all
I am having a table with columns say A to F. A being the primary key, B unique key. I am creating clustered index on A,B.
Then in the leaf node of the index will I have all the column from A to F or Only A,B column values.
- IF it has all columns (A to F) then why sql server goes for BOOKMARK lookup of the column that is not in the index. Please explain it.
Thanks
October 3, 2010 at 2:08 am
vinothr 33504 (10/3/2010)
Hi allI am having a table with columns say A to F. A being the primary key, B unique key. I am creating clustered index on A,B.
Then in the leaf node of the index will I have all the column from A to F or Only A,B column values.
- IF it has all columns (A to F) then why sql server goes for BOOKMARK lookup of the column that is not in the index. Please explain it.
The clustered index orders the physical data pages of the table by the clustering key, which is (A,B) in your example. Since the clustered index is formed from the order of the data pages, it automatically includes (in the sense of 'included columns') the other columns in your table. This is only true of the clustered index on a table.
The non-clustered primary key on column A makes a separate copy of the data in column A only, and orders it by the values in column A. SQL Server includes the clustering key (A,B) in every non-clustered index (to allow for bookmark lookups to fetch non-covered columns), so the PK is keyed on column A and includes column B (from the clustering key).
The unique index on column B likewise makes a copy of the data in column B only, and orders it by the values in column B. This index also 'includes' column A - again contributed by the clustering key.
Whenever you use a non-clustered index, SQL Server will need to perform a bookmark lookup if you ask for columns that are not part of the index key, and which are not included in the index either explicitly (using INCLUDE syntax) or from the clustering key.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 3, 2010 at 2:42 am
Thanks Paul.:-D
October 4, 2010 at 3:53 am
Hi,
Please help me to understand your lines "A being the primary key, B unique key. I am creating clustered index on A,B." Please send the complete Script of Table.
To answer your query please send the your T-SQL query along with execution plan.
are you created Clustered Index like CREATE CLUSTERED INDEX MyCluIndex (Column A,Column B)?
------------------------------------------------------------------
Clustered indexes provide a sort order for the storage of data within a table. However, a clustered index does not provide a physical sort order. A clustered index does not physically store the data on disk in a sorted order because doing so creates a large amount of disk input/
output (I/O) for page split operations. Instead, a clustered index ensures that the page chain of the index is sorted logically, allowing SQL Server to traverse directly down the page chain to locate data. As SQL Server traverses the clustered index page chain, each row of data is read in clustering key order.
-------------------------------------------------------------------
Ram
MSSQL DBA
October 4, 2010 at 6:26 am
vinothr 33504 (10/3/2010)
Hi allI am having a table with columns say A to F. A being the primary key, B unique key. I am creating clustered index on A,B.
Then in the leaf node of the index will I have all the column from A to F or Only A,B column values.
- IF it has all columns (A to F) then why sql server goes for BOOKMARK lookup of the column that is not in the index. Please explain it.
Thanks
You get a BOOKMARK lookup most likely because your query has a where clause on either A or B (not both) and SQL Server is using either the A or B index instead of the clustered index (quicker to read a smaller index and doing a single lookup than reading the larger clustered index).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply