knightEknight
asked on
indexed views not being used by optimizer
Please run the code below in a scratch database. On the last query, check the execution plan. Why does it do a full table scan instead of doing an index seek on the covering index of the view?
The goal is to do a case-insensitive search on the LastName column, which in my real base table is collation Latin1_General_BIN (the server default here), by way of an index seek, not a scan. I thought I could do this by creating a view of the table and setting the collation on that field to SQL_Latin1_General_CP1_CI_ AS in the view, and index this version of the column on the view.
Thanks
The goal is to do a case-insensitive search on the LastName column, which in my real base table is collation Latin1_General_BIN (the server default here), by way of an index seek, not a scan. I thought I could do this by creating a view of the table and setting the collation on that field to SQL_Latin1_General_CP1_CI_
Thanks
create table dbo.tTest(id int identity(1,1), LastName varchar(80) COLLATE Latin1_General_BIN, FirstName varchar(80) COLLATE Latin1_General_BIN)
GO
create unique clustered index tx0 on dbo.tTest(id)
GO
insert into dbo.tTest select 'Doe','John'
insert into dbo.tTest select 'Doe','Jane'
insert into dbo.tTest select 'Bach','Hellen'
insert into dbo.tTest select 'Smith','Jane'
insert into dbo.tTest select 'Smith','Mike'
insert into dbo.tTest select 'Jones','Mike'
GO
create view dbo.vTest WITH SCHEMABINDING as
select id, (LastName COLLATE SQL_Latin1_General_CP1_CI_AS) as LastName, (FirstName COLLATE SQL_Latin1_General_CP1_CI_AS) as FirstName
from dbo.tTest
GO
create unique clustered index vx0 on dbo.vTest(id)
GO
create unique index vx1 on dbo.vTest(LastName,FirstName,id)
GO
select id, FirstName, LastName
from dbo.vTest -- with (index=vx1)
where LastName = 'BACH'
GO
-- drop view dbo.vTest
-- truncate table dbo.tTest
-- drop table dbo.tTest
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
starting over ...
ASKER
Open in new window