Please provide guidelines on when to use DSV's as opposed to database views. Any performance issue using one vs. the other?
Environment: SQL Server 2008
+1
A:
The only real difference is that you can index a view, so you can get better performance out of it. I really only throw a view onto the DB if it's for SSRS, though. If it's for SSAS and it's processing nightly, I define the query inside SSAS, just so that it's all in one place. Also, it's a lot easier to version the DSV than it is the view in the database, so if you need to revert your changes, that's easy to do.
Therefore: If performance is most important, use a view. If maintainability is most important, use a named query. That's my rule of thumb, at least.
Eric
2009-07-21 20:39:55
Note that only enterprise edition will leverage the indexed view. Non-enterprise editions will expand the view definition and ignore the index, so they have to be coerced into using it by adding the WITH NOEXPAND clause: http://msdn.microsoft.com/en-us/library/ms181151.aspx
Remus Rusanu
2009-07-22 02:37:57
Thanks for your input
Ganesha
2009-07-22 17:33:52