optimize query - how to make it an "Index seek"

create table t1(a varchar(50) , b varchar(50))

create index i1 on t1(a)
create index i2 on t1(b)

create view v1
as
select * from t1 where isnull(a,b) = 'test'

select * from v1

The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ??

TIA

- ForXLDBHi!

This query will make use of index i3:

create index i3 on t1(a,b)

Carsten|||Is it possible to have make an "Index seek" ??|||Just found the following behaviour:

When running:
select * from dbo.v1 => SQL Server uses an index scan

select * from dbo.v1 where a='test' => SQL Server uses an index seek

It's quite strange...|||I do use the following...
select * from dbo.v1 where a='test'

So, it works for me I guess.

Thanks !!|||quite strange...

How so?

There's no predicate...|||I'm not sure. But, it shows "Index Seek" in the plan !!|||Hi Brett,

what confused me is the point, that the DBMS changes the execution plan even if there is basically no difference in the constraint (in this case "a = 'test'").

The definition of v1 already contains this WHERE-clause. And that's why I expect SQL Server to generate the same execution plan.

Carsten

How so?

There's no predicate...|||create table t1(a datetime , b datetime)

create view v1
as
select a,b, isnull(a,b) as c from t1

create index i3 on t1(a,b)

select *
from v1
where c = '01/01/2004'

the above sql is using index scan.....can we make it to use index seek ?

--clean up
--drop table t1
--drop view v1
--drop index t1.i3|||One small thing is that you need to have a bunch of data in the table, before the optimizer thinks about the index. If the data portion of the table consists of a single page, then you will always get a table scan. This is not bad, as it is one read. If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.

A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.|||--Change view to:
alter view v1
as
select * from t1 where a = 'test'
union
select * from t1 where b = 'test'
go

--add 2 indexes and drop existing ones:
drop index t1.i1
drop index t1.i2
create index i3 on t1(a,b)
create index i4 on t1(b,a)|||...If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.This is not true when you deal with queries that do not address fields that are not part of an index.

...A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.Really? Try this using DDL changes from my previous post:

select * from v1 where isnull(a,b)='test'