The Downside Of Nested Views

Randolph West doesn’t mince words:

Nested views are bad. Let’s get that out of the way.

What is a nested view anyway? Imagine that you have a SELECT statement you tend to use all over the place (a very common practice when checking user permissions). There are five base tables in the join, but it’s fast enough.

Instead of copying and pasting the code wherever you need it, or using a stored procedure (because for some reason you’re allergic to stored procedures), you decide to simplify your code and re-use that SELECT in the form of a database view. Now whenever you need to run that complicated query, you can instead query the view directly.

What harm could this do?

Spoilers:  a lot.

Related Posts

Batch Mode Normalization

Paul White digs into batch mode normalization and its consequences for performance: I mentioned in the introduction that not all eight-byte data types can fit in 64 bits. This fact is important because many columnstore and batch mode performance optimizations only work with data 64 bits in size. Aggregate pushdown is one of those things. There are […]

Read More

Comparing CAST and CONVERT Performance

Max Vernon runs a performance test of CAST versus CONVERT: This post is a follow-up to my prior post inspecting the performance of PARSE vs CAST & CONVERT, where we see that PARSE is an order of magnitude slower than CONVERT. In this post, we’ll check if there is a similar difference between using CAST or CONVERT. But just to be clear, CONVERT offers […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728