21.03.2009

Bookmark Lookup and Included Columns

Bookmark lookup uses clustered index to lookup the corresponding row in the table.  It is something like, sql server optimizer says “hey i used the index you create on that column and i used clustered index to retrieve other columns you want me to get.”. Let me give an example

1.Create a table and insert some rows (Code sample is compatible with SQL Server 2008)

if exists(select * from sys.tables where name = 'tbl_tags')
    drop table tbl_tags
go
create table tbl_tags
(
    TagId            int not null identity(1,1) primary key    ,
    Name            varchar(255) not null                    ,
    [Description]    varchar(255) not null                    ,
    DLM                datetime not null default(getdate())
)    
go
declare @i int = 1000
while @i > 0
begin
    insert into tbl_tags(Name,Description) values(@i,'Description ' + CAST(@i as varchar(4)))
    set @i -=1
end




2. Create a non-clustered index on Name and retrieve rows by Filtering Name.




create nonclustered index IX_tbl_tags on tbl_tags(Name) with(maxdop = 8)
go
select Name,DLM from tbl_tags where Name = '1'




3. Look execution plan of this query, from the picture below you will see bookmark lookup.





image


4.Recreate index with using included columns, reexecute select statement and look execution plan



drop index tbl_tags.IX_tbl_tags
create nonclustered index IX_tbl_tags on tbl_tags(Name) include(DLM) with(maxdop = 8)
go
select Name,DLM from tbl_tags where Name = '1'



 image



Technorati Etiketleri: ,,

Hiç yorum yok: