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) and leader of the SQL User Group in Adelaide, Australia. Rob is also a Director of PASS, and runs training courses around the world in SQL Server and BI topics.

How views are changing in future versions of SQL

April is here, and this weekend, SQL v11.0 (previous known as Denali, now known as SQL Server 2012) reaches general availability. And so I thought I’d share some news about what’s coming next. I didn’t hear this at the MVP Summit earlier this year (where there was lots of NDA information given, but I didn’t go), so I think I’m free to share it.

I’ve written before about CTEs being query-scoped views. Well, the actual story goes a bit further, and will continue to develop in future versions.

A CTE is a like a “temporary temporary view”, scoped to a single query. Due to globally-scoped temporary objects using a two-hashes naming style, and session-scoped (or ‘local’) temporary objects a one-hash naming style, this query-scoped temporary object uses a cunning zero-hash naming style. We see this implied in Books Online in the CREATE TABLE page, but as we know, temporary views are not yet supported in the SQL Server.

image

However, in a breakaway from ANSI-SQL, Microsoft is moving towards consistency with their naming.

We know that a CTE is a “common table expression” – this is proving to be a more strategic than you may have appreciated.

Within the Microsoft product group, the term “Table Expression” is far more widely used than just CTEs. Anything that can be used in a FROM clause is referred to as a Table Expression, so long as it doesn’t actually store data (which would make it a Table, rather than a Table Expression). You can see this is not just restricted to the product group by doing an internet search for how the term is used without ‘common’.

In the past, Books Online has referred to a view as a “virtual table” (but notice that there is no SQL 2012 version of this page). However, it was generally decided that “virtual table” was a poor name because it wasn’t completely accurate, and it’s typically accepted that virtualisation and SQL is frowned upon. That page I linked to says “or stored query”, which is slightly better, but when the SQL 2012 version of that page is actually published, the line will be changed to read: “A view is a stored table expression (STE)”.

This change will be the first of many. During the SQL 2012 R2 release, the keyword VIEW will become deprecated (this will be SQL v11 SP1.5). Three versions later, in SQL 14.5, you will need to be in compatibility mode 140 to allow “CREATE VIEW” to work. Also consistent with Microsoft’s deprecation policy, the execution of any query that refers to an object created as a view (rather than the new “CREATE STE”), will cause a Deprecation Event to fire. This will all be in preparation for the introduction of Single-Column Table Expressions (to be introduced in SQL 17.3 SP6) which will finally shut up those people waiting for a decent implementation of Inline Scalar Functions.

image

And of course, CTEs are “Common” because the Table Expression definition needs to be repeated over and over throughout a stored procedure.

...or so I think I heard at some point. Oh, and congratulations to all the new MVPs on this April 1st.

@rob_farley

Published Sunday, April 01, 2012 12:05 AM by Rob Farley

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

 

jamiet said:

Nice try!!!

March 31, 2012 9:09 AM
 

DonRWatters said:

It's too bad that this posted before April 1st in the US.  It doesn't have the same impact when it's dated 31 March.  :)  Well done though.

March 31, 2012 12:23 PM
 

Rob Farley said:

I have to work in my own time zone, Don. :)

March 31, 2012 5:10 PM
 

Amy Lewis said:

Ha ha!  Almost fell for it.....

March 31, 2012 8:52 PM
 

Richie Rump said:

Totally fell for it. Nicely done. I say putting it up on March 31 (US) was a win.

March 31, 2012 11:22 PM
 

Rob Farley said:

Well Richie - I can't be held responsible for when people read it, only when I post it.

April 1, 2012 12:26 AM
 

Kendra Little said:

Consulting has taught me that there are more Uncommon Table Expressions being used in the wild than Common ones!

April 1, 2012 8:18 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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