CODEWITHSUNDEEP
sql-serverview
osum
osum

Reputation: 815

create view with indexes

I wanted to create a view, that will exhibit the same property as the table it queries from

CREATE VIEW Agent_View
AS 
SELECT * FROM Agent_table

I want to know if I need to create the same indexes as the Agent_table in the Agent_View also.

Do I need to declare the Agent_View as schemabound to create index. Is there any workaround to create indexes without declaring as schemabound ?

Upvotes: 1

Views: 246

Answers (3)

Ronnis
Ronnis

Reputation: 12843

Most (if not all) queries against this view will behave as if you used the underlaying table directly.

Thus if you indexed agent_name in agent_table, and performed a query like:

select *
  from agent_view
 where agent_name = 'James Bond';

...it would be rewritten by the optimizer to:

select *
  from agent_table
 where agent_name = 'James Bond';

Whether the index would be used or not is another question.

Upvotes: 2

gbn
gbn

Reputation: 432667

You can't create indexes on the view with SELECT * because you can't have WITH SCHEMABINDING

A view without indexes is just a macro that expands into the outer query anyway. So table indexes will be used anyway. The view doesn't exist from a schema perspective.

Finally, IMHO this is possibly the most pointless use of a view possible. It adds zero value.

Upvotes: 3

Nils Magne Lunde
Nils Magne Lunde

Reputation: 1824

You may want to create indexed views if you have any complex queries, but in your case the table's indexes will be used.

MSDN reference

Upvotes: 1

Related Questions