Common Table Expression (CTE) Month at SteveStedman.com

This month is going to be Common Table Expressions Month at SteveStedman.com. My goal is to post one valuable post on a Common Table Expressions topic each day for the entire month.

What is a Common Table Expression

A Common Table Expression (know as CTE) is a type of in memory result set, similar to a view or a derived table, however the CTE only exists for the duration of a single query. CTE’s have many uses from simplifying huge derived table queries, to doing recursive queries that would otherwise be very difficult to accomplish with SQL Server. Every version of SQL Server from SQL Server 2005 on includes the ability to use CTE’s, including SQL Azure. CTE’s are available in the SQL Server Express, SQL Server Standard Edition, SQL Server Enterprise, SQL Server Developer, SQL Azure, and any other version of Microsoft SQL Server from SQL Server 2005 and on.

If you like this or any of the postings on CTE’s, please take a look at my book on Common Table Expressions. The book is currently available at Amazon.com in paperback and Kindle versions.

So What is a Common Table Expression?

Here is a preview of what a CTE looks like, stick around for tomorrows blog which describes how it all comes together, and what you can do with it.


;WITH MyCommonTableExpression AS
(

SELECT *

FROM Departments

)

SELECT *

FROM MyCommonTableExpression

Stay tuned for tomorrows posting on

Sample Databases

For Common Table Expressions month at SteveStedman.com I will be providing several sample queries most of which can be run in the cte_demo database which is available for download.

My Background With Common Table Expressions

I first learned about CTE’s in 2011, at which point I was just confused at the strange syntax. I had found a query that someone else had provided on their blog which used a CTE for something, I don’t even remember what it was used for. When trying to figure out what that query was doing, I was forced to figure out this strange new WITH syntax that I had not seen before. From there I became obsessed with understanding everything about Common Table Expressions.

After that introduction to CTE’s I quickly discovered that I was using CTE’s more and more often, and that there were some really cool tricks like recursive queries that you could do with CTEs.

In late 2011 I pitched a session for SQL Saturday#108 in Redmond WA on February 25th 2012 at the Microsoft Campus, and the session was accepted. This was going to be my first time presenting at a SQL Saturday and I had better learn everything that there is to know about CTE’s so that I don’t look foolish. I spent the next several months preparing my SQL Saturday session, and researching as much as I possibly could. I presented at the SQL Saturday and it went quite well. After the presentation several people made a few suggestions, and I adapted my presentation for SQL Saturday #114 in Vancouver BC Canada on March 17th 2012.

Next I had the opportunity to present at Seattle Code Camp 2012 on June 16th. I cleaned up the presentation a bit more, learned a few more tricks with CTE’s and presented at Seattle Code Camp.

Later in 2012 I presented at SQL Saturday 172 in Portland Oregon, and SQL Sat 166 in Olympia Washington. At one of those SQL Saturdays I met Rick Morelan from Joes2Pros and we discussed writing a book. I pitched the idea of a book on Common Table Expressions and Rick asked the question of “A whole book on Common Table Expressions, really, what would you cover?” So I threw out ideas on recursion, de-duplication of data, tree-path’s, Fibonacci sequence, and data paging. I still don’t think that he was entirely convinced that it would be worth a whole book, so I put together an outline to show what I had in mind. A couple weeks later I was working with Rick and the team at Joes2Pros to write the book on Common Table Expressions. But there aren’t any other books focused only on CTE’s.

The next few months I spent writing the book on CTE’s learning every detail that I possibly could so that I didn’t get anything wrong, and updating and refining the book. I can safely say that I have spent over 1000 hours over the last 2 years researching and understanding Common Table Expressions.

Several months later May 18, 2013 I was presenting at SQL Saturday in Redmond WA again, and I had the opportunity to give the CTE presentation again. But it wasn’t the same presentation for 15 months ago, it had evolved, improved, and covered far more information that I could ever fit into the hour session. The book was announced for the first time at that SQL Saturday session, and it was now available on Amazon.com.

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com