Vapor Trail
Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.
Think of it like this:
- Views are like a permanent home
- Common table expressions are like a mobile home
You can put equally terrible queries in either one and expect equally terrible results.
It’s sort of like installing a toilet: If you just throw it in the middle of the kitchen floor without any consideration as to architectural design or pipe locality, it’s going to look bad immediately, and stink eventually.
Of course, since views are programmable objects, they have a bit more depth of character with creation options. common table expressions, being temporary views, don’t have much of a story arc.
For example, you can index a view, but you can’t index a common table expression. Either one is entirely capable of using indexes on the underlying table(s), of course.
What I find particularly curious is how well received common table expressions are, while views are often looked down upon, despite have many functional equivalences.
Developer 1: This stored procedure has hundreds of common table expressions in it
Developer 2: Wow that’s amazing you’re so good at SQL
vs
Developer 1: This
stored proceduredatabase has hundreds ofcommon table expressionsviews in itDeveloper 2: Wow that’s
amazinghorrible you’reso good at SQLa shabby degenerate and a skunk of a human being
Surprise!
Do That Viewdoo That You Do So Well
One thing that I see in a lot of views (STILL!) is SELECT TOP (100) PERCENT, which is a pretty good sign that I’m going to have to fix a lot of other problems from the 2005-2012 era of SQL Server development.
As a slight digression, I find it odd how many myths pervade SQL Server development from the 2005-2012 era. It was the golden age of SQL Server books.
- Guru’s Guide to Transact-SQL
- The Guru’s Guide to SQL Server Architecture and Internals
- Guru’s Guide to SQL Server Stored Procedures
- SQL Server 2005 Practical Troubleshooting: The Database Engine
- Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
- Inside Microsoft® SQL Server™ 2005: The Storage Engine
- Microsoft® SQL Server® 2008 Internals
- Microsoft SQL Server 2012 Internals
All this great knowledge was out there, but everyone seemed to find a blog post that was like “in my testing the estimated batch cost for the table variable was way lower than the estimated batch cost of the temp table, so they’re always faster”.
The dogma is real.
I know this worked in like SQL Server 2000, but that’s not excuse for keeping it around today:
CREATE OR ALTER VIEW
dbo.u
AS
SELECT TOP (1)
u.*
FROM dbo.Users AS u;
GO
CREATE OR ALTER VIEW
dbo.u_p
AS
SELECT TOP (100) PERCENT
u.*
FROM dbo.Users AS u;
GO
You’ll notice the query plan for the 100 percent view is quite conspicuously missing a TOP operator at all. The optimizer throws it away because it knows it needs to get everything anyway.
Shocking news from 20 year ago, I’m sure.
Equivalency
Getting back to the main point of the post here, common table expressions are the same as a vanilla view — unindexed, with no special create options.
WITH
ThisIsTheSameAsAView
AS
(
SELECT
u.AccountId,
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Badges AS b
ON b.UserId = u.Id
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.UserId = u.Id
AND c.PostId = p.Id
JOIN dbo.Votes AS v
ON v.PostId = p.Id
GROUP BY
u.AccountId
)
SELECT
*
FROM ThisIsTheSameAsAView AS view1
JOIN ThisIsTheSameAsAView AS view2
ON view2.AccountId = view1.AccountId;
GO
CREATE OR ALTER VIEW
dbo.ThisIsTheSameAsACommonTableExpression
AS
SELECT
u.AccountId,
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Badges AS b
ON b.UserId = u.Id
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.UserId = u.Id
AND c.PostId = p.Id
JOIN dbo.Votes AS v
ON v.PostId = p.Id
GROUP BY
u.AccountId;
GO
SELECT
*
FROM dbo.ThisIsTheSameAsACommonTableExpression AS cte1
JOIN dbo.ThisIsTheSameAsACommonTableExpression AS cte2
ON cte2.AccountId = cte1.AccountId;
If you were to capture execution plans for both of these, you’d see the same query plan pattern where the query contained by the view and the common table expression is executed twice, so the results from one reference can be joined to the results of the other reference.
I’ve talked about this many times in the past with common table expressions, but not in comparison to views.
If you’re one of those developers who disdains views for their very existence, you shouldn’t feel any differently about common table expressions in practice (at least from a performance perspective).
One place where views edge out common table expressions is that the code is contained in a module that can be referenced by any other query or module (stored procedure, function, etc.). If you fix query performance for a view, everything that references it also gets faster. Doing that for a common table expression that gets used frequently is cool but… then you have to go find and replace it all over the place.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
