THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

CTEs – query-scoped views

This T-SQL Tuesday is on the topic of CTEs, and is hosted by Bob Pusateri (@SQLBob). TSQL2sDay150x150For 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.

image

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

Published Tuesday, May 10, 2011 12:04 PM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bob Pusateri said:

Great post, Rob!  Thanks for your insight and for contributing to T-SQL Tuesday!

May 10, 2011 11:52 AM
 

John Sansom said:

Nice post Rob.

I enjoy the fact that you are approaching this months theme from a different angle, looking at the wider purpose of CTEs rather than the various T-SQL implementations/uses available.

I must admit that I had never considered temporary views before reading your post but as a concept I can certainly see their value.

May 10, 2011 2:53 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement