SQL Server Stuff
May 22, 2013 Leave a Comment
As a first time published author it is exciting to see that my Common Table Expressions book is now available on the Amazon.co.uk website. It showed up on Amazon.com about a week ago, and now its available in the UK. I just need to blog about my excitement as the book shows up in different places.
Links:
May 20, 2013 Leave a Comment
I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table Expressions, I discovered that I just can’t fit it into an hour with the detail that Common Table Expressions deserve. I would rather go into depth so that everyone can master CTEs rather than just breezing over the highlights and leaving everyone wondering.
For future SQL Saturdays and other presentations the sessions have been broken out as follows. Currently I have proposed the following 2 sessions for SQL Saturday Denver.
Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expression session you will learn everything needed to start using CTEs for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTEs. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTEs before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expression session.
You have been introduced to Common Table Expression, you understand the ;WITH syntax, but you want to know more. Learn how to recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE and DELETE data from CTEs. Some of the common use cases for CTEs will be covered including finding holes in patterns, finding and removing duplicate data, string parsing, and more. See how CTEs compare to SQL Server 2012 offset and fetch paging techniques. Get an in depth understanding of the performance behind a common table expression. Understand when the CTE is the right solution, and the wrong solution to use. Finally we will take a look at some classic recursive algorithms and how they can be implemented with CTEs. This session pairs well with the Introduction to Common Table Expression session.
May 19, 2013 Leave a Comment
My common Table Expressions book became available on Amazon.com late last week. After presenting on Common Table Expressions at SQL Saturday yesterday, the sales jumped a bit to the point that the CTE Book is now #2 in the Hot New releases for Computer Databases section at Amazon.com as shown in the image below.
This is so exciting after all the work that went into this book. Thanks everyone who purchased, I hope you enjoy reading the book as much as I enjoyed writing it.
May 18, 2013 Leave a Comment
It was a great day at SQL Saturday today. I was able to preview my Common Table Expressions Book at my Common Table Expressions presentation. The presentation was great, the room was packed, and there were lots of great questions.
Here is the lineup for the room that I presented in. Just before me was Rick Morelan presenting on SQL 2012 Error handling, which was a spectacular presentation.
One presentation that I wish I had attended was the Machine Learning for Mere Mortals from Dev Nambi. Although the presentation that I went to in the same time slot was great, I wish I had been able to go to both.
The slides are available for download from the presentation here: Steve Stedman CTE Presentation.
One thing that I had a hard time with during this presentation was being able to get all of the information covered in a hour. After writing my Common Table Expressions Book I had so many things I my mind that I wanted to cover, but the time just didn’t allow for it. I think that I will split the CTE presentation into two 1 hour sessions, first the introduction to CTEs for those who have not used CTEs in the past, and then the advanced CTEs for the really fun stuff.
May 17, 2013 1 Comment
I will be presenting at SQL Saturday #212 in Redmond tomorrow.
My presentation will be on Common Table Expressions, and it is the first CTE presentation since my Common Table Expressions book released. I have updated the presentation to include a number of new things relating to CTE’s that I figured out while writing the book.
This will be my second SQL Saturday in Redmond, the first one was a year ago. SQL Saturdays are a great learning experience, and I am looking forward to a great day of free learning.
Also, if you are at SQL Saturday, drop by the Joes2Pros table/booth and take a look at my Common Table Expressions book. Let me know what you think. See you in Redmond.
April 2, 2013 1 Comment
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.
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.