Common Table Expressions are a great way to simplify complex derived table queries. If it makes sense to break out one derived table or subquery into a CTE, it can make sense to do multiple. To use multiple CTE’s in a single query you just need to: Finish the first CTE Add a comma, Declare the name Optional columns for …
The following is chapter 7 from my Common Table Expressions book in its entirety. I can remember the first time that I worked on data paging code. I had to page through web site search results 20 at a time on a given web page. My task was to understand how it had been written and to do some bug fixing. After reviewing …
The following is chapter 6 from my Common Table Expressions book in its entirety. READER NOTE: Please run the CTEBookSetup.sql script in order to follow along with the examples in this chapter. The script mentioned in this chapter is available at http://SteveStedman.com. Thinking of the proverb “two heads are better than one” makes me think, it really depends on the people. Two people working together are …
As part of the CTE book there is a sample database that all the queries use. You can download the create script for that sample database here and run it in your local SQL Server, or on a test SQL Server: Due to the security settings of the web site, and many download checks, I was unable to anme the …
The following is chapter 4 from my Common Table Expressions book in its entirety. READER NOTE: Please run the CTEBookSetup.sql script in order to follow along with the examples in this chapter. The script mentioned in this chapter is available at http://SteveStedman.com. Recursive CTEs Have you ever solved a maze puzzle on paper, or had the opportunity to visit a …
The following is Chapter 3 from my Common Table Expressions book it its entirety. CTEs Instead of Derived Tables One time I was handed an eight page print out with an 8pt font showing a single SQL statement. It was handed to me with a question about why it was not returning the right data. After looking the query over …
Back in 2012 when I was writing my Common Table Expressions book, I came up with the following CTE in a function to split a string into rows. Original Post: http://stevestedman.com/2012/04/using-a-cte-to-split-a-string-into-rows/ At the time, I thought it was pretty handy function, I ended up including it as an example in my CTE book, and I have used it dozens of …
Last week I had the great opportunity to speak at PASS Summit 2015. My presentation was on Common Table Expressions. Here is the sample code from the presentation. AdvancedCTEs.zip Related Links: Common Table Expression (CTE) Month at SteveStedman.com (introduction) Sample Database for Common Table Expressions Writing Your First Common Table Expression with SQL Server Video: Writing Your First CTE …
Epic Life Goal Completed: Speaking at PASS Summit – Advanced CTEs Read more »
During my 24 Hours of Pass presentation on Advanced CTE’s today I was asked the question about deleting from a CTE when it uses an EXISTS statement that queries another table. I figured I would create quick blog post to show the example. First some background when using the delete statement with a CTE you can’t delete if the query inside …
Today I am presenting on Advanced Common Table Expressions as part of the 24 Hours of PASS conference. It is being webcast so check it out if you can. The outline for the presentation will be the following: Recursive Queries Hierarchical Recursive Data Manipulating Data Common Use Cases CTE Performance Considerations Classic Recursive Algorithms The sample code and slides are …