What is a Common Table Expression – Video

 

Related Common Table Expression Links

You can also find more CTE examples on the TSQL Wiki.

If you enjoyed these Common Table Expression posting, and want to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

End of June Summary

June was an exciting month for me with Beta 6 of the Database Health Reports releasing at the beginning of the month, and this being CTE Month at SteveStedman.com to mark the release of my Common Table Expressions book.

CTE Month Summary

CTE Month postings started with the basics on June 1st, and grew throughout the month to include more advanced CTE content.

Since June is CTE month, here are some links to other CTE articles on my blog:

You can also find more CTE examples on the TSQL Wiki.

If you enjoyed these Common Table Expression posting, and want to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Other Posts for June

SQL Server Memory Hog Query

The article could be named, “How to use up all of your SQL Server available memory with a single CTE query.”  Another name for the article could just be “SQL Server Bug Report” depending on how you look at it.

When presenting unleashing Common Table Expressions at SQL Saturday a while back, I was asked a couple of great questions that I didn’t know the answer to. So I did the research and tracked it down:

1. How many levels of recursion can you have in a CTE?

2. How many levels of nesting can you have in a CTE?

So I started doing the research and doing some testing to figure it out.

How many levels of recursion can you have in a CTE?

This is the easier one to answer between the two questions. The answer is, you can have more levels of recursion that you would ever need for standard recursion. I have tested CTEs with up to 1 million levels of recursion, and the have performed pretty well.  If you are writing queries that need more than 1 million levels of recursion, perhaps you should take a look at a different approach.

How many levels of nesting can you have in a CTE?

This is where it gets really interesting. SQL Server does a really good job with overall performance on CTEs, but where it completely breaks down is on deep nested CTE queries. By deep nested I mean more than a thousand or two thousand. To answer the question of how many levels can you have, I would answer this as you can nest more than you would ever really need. If you really need more than a thousand levels of nesting in a CTE, you might want to rethink your approach.

On SQL Server 2005 the limit is 255. But in SQL Server 2008 and newer this limit was extended, and appears to not have a fixed limit, rather the limit is based on the amount of memory available for the query to use.

So to build the crazy CTE, I used excel to build out several thousands of rows of nested CTEs. One calling another, calling the next, and so on. What I found was that this was a very easy way to use up almost all the memory on your database. DO NOT TRY THIS ON A PRODUCTION DATABASE. The reason that DBAs and developers have test or development databases is to play around with things that may be dangerous on a production server. This is one that you could try on a test server.

The other interesting thing that the query does when it uses up all the memory, somehow it dumps some of the connections that are currently active on the SQL Server at that point.

Watch the video to find out how a nested CTE query could use up all of the memory on your SQL Server.

If anyone has a test server with 512GB, or 1TB of available, memory, I would love to see how this type of query performs. Give it a try and let me know.

You can download the script NestedCTE.zip here. The zip file contains 2 files, one called CTE.sql, and one called CTE2500.sql.  The 2500 version is the one that I used in the demo, and the CTE.sql has 32767 nestings.  I would love to see the bigger on run on a server with a TB or more of memory to see how it performs.

Here are a few other links to CTE related posts on my site.

SQL Saturday #172 in Portland Oregon

Its going to be a great day!   Ready for SQL Saturday 172 in Portland today, looking forward learning lots, and sharing my CTE Presentation with those who want to learn about Common Table Expressions.

Please come to my CTE if you are interested in any of the following:

1.What is a Common Table Expression
2.Simple CTE
3.CTE – Subquery Re-use
6.CTE Common Uses
7.CTE for Math Geeks

And many more CTE topics.     I hope to see you there.

Common Table Expression – links

Here are a few links to some of the other CTE examples used during my Common Table Expressions presentation.

Just a quick summary of the samples.

You can also find more CTE examples on the TSQL Wiki.

SQL Lunch UK

Today I started the day early with a great presentation for the SQL Lunch UK group on Common Table Expressions.

For those who attended the presentation here is the Zip file with the Powerpoint and the SQL code from the demo.  Feel free to download it and learn from it.

CTE_SQL_LUNCH_UK_May_2012.zip

 

Here are a few links to some of the other CTE examples used in the demo.

 

 

Good luck to everyone who is new to common table expressions.  Special thanks to Dave Ballantyne to inviting me to participate in this event.