Views and Indexes

[tyreejp]

Hi,

I'm working with a view that looks like this...

create view [dbo].[vallsearch]
as
select * from archiveebisql.dbo.search (nolock)
union all
select * from ebisql.dbo.search (nolock)

The two tables I'm union-ing are in two different databases, therefore I can't use schemabinding and therefore I can't create an index on the view. My question is, because there are indexes on these tables, will those indexes "bubble up" and be used when I select from the view?

Thanks

James

--
James

http://www.microsoftjr.com

 

[ptheriault]

If you use "select *" it can only scan the index and not seek on the index. An index seek is more efficiant than a scan. but yes your select would use an index. Put your query in Query Analyzer and select the show plan button. It will show which, and how it is using the indexes.

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

>>If you use "select *" it can only scan the index and not seek on the index
??????????
That is wrong
If you don't have a WHERE clause then you will get an table or index scan

however when you do include a WHERE clause you will get an index seek

Hit CTRL + K then run this two in Query Analyzer and look at the plan
select * from authors

select * from authors
where au_id = '172-32-1176'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ptheriault]

I had a post on SSWUG that said select * can only use an index scan...
I guess I miss read it.

- Paul
- Database performance looks fine, it must be the Network!

 

[tyreejp]

Ok, so given the view that i listed in my previous post, I perform a select * from vallsearch where picrefnum = 1234[\b] and looked at the execution plan. It's performing an index scan, even though picrefnum is an indexed column on the base tables.

Had I been able to apply an index to the view itself on this column, then I would have been able to perform select * from vallsearch where picrefnum = 1234[\b] and have the execution plan show an index seek?

Are my asumptions correct?

Thanks for your help and quick replies!

--
James

http://www.microsoftjr.com