CTEs – query-scoped views

This T-SQL Tuesday is on the topic of CTEs, and is hosted by Bob Pusateri (@SQLBob). For a bunch more posts on the topic, follow the link to his Invitation Post, and you’ll see a pile of them, all about CTEs. If you’re reading this and it’s still May 10th 2011 (GMT), then why not put a post together yourself!

I’m sure there will be plenty of posts about how CTEs can be used to wrap ranking functions, and about recursion – useful features both – but I want to explain how I see CTEs.

CTEs are Common Table Expressions. The clue is in the name – they’re table expressions, and able to be referred to in the FROM clause, fulfilling the same role that a table might. Not a table in the sense of storing data, but in the sense that a table refers to a set of data, ready to be transformed into a result set.

There are a few things that can be used as a table expression in a FROM clause. Obviously a table, but others include views, table-valued functions (whether inline or procedural), and table-valued sub-queries. Those of you who regularly read my blog (and if you’re not reading this at sqlblog.com, where are you reading it?) will know that I like to call views ‘stored sub-queries’, and TVFs ‘stored correlated sub-queries’ or ‘parameterised views’. So perhaps there’s no surprises here that these are all table expressions too.

But this post is about CTEs. They’re half way between view and sub-queries. They exist only within a single query, and contain a table-expression. A stored sub-query if you like, but only scoped to a particular query. They’re like views, but don’t persist.

So a CTE is a view for when you don’t really want a view. It’s a query-scoped view. Very useful really – and you can even use them for UPDATEs, INSERTs and DELETEs, so long as you understand that this is subject to the same rules as views (but obviously you can’t put triggers on them).

But this query-scoping can feel like a problem. What if I want my view to persist for a bit longer? That could be quite handy, couldn’t it? What if we wanted a view to be created at the start of a stored procedure, and be scoped there? We can do this with tables, we just put a hash in front of the name: CREATE TABLE #blah (id int), and the scope is set.

Unfortunately when we try to do this with views, we get a nice error.

That’s right: “Temporary views are not allowed.” It knows what we’re trying to do, it just doesn’t let us do it. It’ll give similar errors for temporary functions too.

Luckily, you can vote for this on the Connect site. There are a couple of different suggestions on how it could be done, so I’ll let you choose the one that you prefer

http://connect.microsoft.com/SQLServer/feedback/details/640863 (Please allow creation of temporary views)

http://connect.microsoft.com/SQLServer/feedback/details/343067 (Module-level table expressions)

Maybe at some point we’ll be able to have a wider range of options over the scope of our ‘common’ table-expressions.

@rob_farley