We help IT Professionals succeed at work.

SQL Server Nested Views or SPs


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

Watch Question

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
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
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.