01Aug 2016 by Grant Fritchey 1 Comment

There Is No Difference Between Table Variables, Temporary Tables and Common Table Expressions

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I actually saw the above statement posted online. The person making the claim further stated that choosing between these three constructs was "personal preference" and didn't change at all the way SQL Server would choose to deal with them in a query. Let's immediately say, right up front, the title is wrong. Yes, there are very distinct differences between these three constructs. Yes, SQL Server will absolutely deal with these three constructs in different ways. No, picking which one is correct in a given situation is not about personal preference, but rather about the differences in behavior between the three. To illustrate just a few of the differences between these three constructs, I'll use variations of this query: SELECT * FROM Sales.Orders AS o JOIN Sales.OrderLines AS ol ON ol.OrderID = o.OrderID WHERE ol.StockItemID = 227; The…
Read More
18Jul 2016 by Grant Fritchey 14 Comments

Common Table Expression, Just a Name

SQL Server, SQL Server 2016, T-SQL
The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. However, the CTE has a very unfortunate name. Over and over I've had to walk people back from the "Table" in Common Table Expression. The CTE is just a query. It's not a table. It's not providing a temporary storage space like a table variable or a temporary table. It's just a query. Think of it more like a temporary view, which is also just a query. Every time I explain this, there are people who don't…
Read More
14Jan 2015 by Grant Fritchey 13 Comments

Common Table Expressions Are Not Tables

T-SQL
There's power in naming things. Supposedly some types of magic are even based on knowing the correct names for things. The name for the T-SQL clause Common Table Expression (CTE) is actually pretty accurate. It's an expression that looks like a table and can be used in common across the entire query (at least I think that's what the common part refers to). But note, I didn't say it was a table. It's not. It's an expression. If you look at the T-SQL definition at the link, it refers to a "temporary" result set. Now, to a lot of people, that means table. But it isn't. Let's look at this in more detail. Here's a query that defines a simple CTE and then uses it to query the date in the…
Read More