Indexed view

Question

Indexed view

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
May 29, 2013 at 7:55 am

#275812

Is it a good idea to put index on view? Some of the views are taking longer to execute and I am thinking about putting index on views. But doesn't it use the index on the underlying tables? How does index on view work?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.

You can check below mentioned link for more information

http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Remember if you're not on Enterprise Edition, you will need the WITH(NOEXPAND) hint in all queries to actually make use of the indexes.

There is also a long list of things not allowed in an indexed view, so you may find that you're not able to create them on some fairly common view definitions - check the list on the link posted above for the list.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

SQL_Surfer (5/29/2013)
Is it a good idea to put index on view? Some of the views are taking longer to execute and I am thinking about putting index on views. But doesn't it use the index on the underlying tables? How does index on view work?

The link Kingston posted is very good and should answer your question about how they work.

To the question of "Is it a good idea to put index on view?" My personal opinion is that Microsoft's Indexed Views are pretty much worthless. I have not seen them used anywhere I have worked.

We played around with them in SQL Server 2008/2008R2 and, even if your optimizer uses them, there are too many requirements (see the link below) to create them and indexed views are too restrictive. That was my experience anyhow. I have not seen any situations where an indexed view saved the day. Again, take a look at this link and see what you think: http://msdn.microsoft.com/en-us/library/ms191432(v=sql.100).aspx

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

SQL_Surfer (5/29/2013)
Is it a good idea to put index on view? Some of the views are taking longer to execute and I am thinking about putting index on views. But doesn't it use the index on the underlying tables? How does index on view work?

The answer to whether to use an indexed view is "it depends."

I have seen them be very useful. I have seen them provide no benefit.

If the views are slow, my recommendation is to first tune the queries in the slow views. You may find that you have a nesting view problem, or worse would be that you have a circular reference problem in your nested views. Evaluate the execution plans and tune appropriately.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events