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.
create table dbo.tTest(id int identity(1,1), LastName varchar(80) COLLATE Latin1_General_BIN, FirstName varchar(80) COLLATE Latin1_General_BIN)
create unique clustered index tx0 on dbo.tTest(id)
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'
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
create unique clustered index vx0 on dbo.vTest(id)
create unique index vx1 on dbo.vTest(LastName,FirstName,id)
select id, FirstName, LastName
from dbo.vTest -- with (index=vx1)
where LastName = 'BACH'
-- drop view dbo.vTest
-- truncate table dbo.tTest
-- drop table dbo.tTest