SQL Server Nested Views or SPs


Which is best practice. Nested views or SPs or something else?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
If views will do, they are often preferable to stored procedures because they can be used just like tables.
Select * from MyView Where ...

So a set of views to combine or filter information is usually very handy.

However, if you are dealing with large amounts of information that must frequently be filtered, using a parameter so that you filter closer to the source data often improves performance.  If I want all sales from February 2015 ... joined to ... other stuff ... filtering down those sales before the join will usually radically cut the time required.

A parameter introduces the need for a stored procedure that is called rather than selected from.  That is powerful, in part because it can do more than simply SELECT.

But it doesn't permit you to do a SELECT FROM MyProcedure(MyParam) ... JOIN ... another_table ... WHERE.

If you need that, you need a table-valued function.
Nested views normally will not be the best regarding performance (mainly because query optimizer) and complecity . but this depends on how big your data is. And levels used in NV

However SPs can be well optimized and give a central full coverage business logic

Also note that later updates to business logic can be easlly manage. And the ability to do some logic that considered hard or  impossible in NV
Scott PletcherSenior DBACommented:
FarWest is correct overall.  The biggest issue is with levels of nesting; at a certain point, the optimizer times out and just scans the full tables.

However, there is one other option: an in-line-table-valued function (but not a multi-statement TVF).  Where possible, use that, as it is as easy to use as a view, but more flexible and will perform better.

But, if the logic is too complex, you should consider a stored proc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.