Years ago while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times. But as things …
Using a CTE to Split a String Into Rows with Line Numbers Read more »
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. …
The following question came up when working on my CTE presentation for SQL Saturday. Does a query that JOINs a CTE to itself execute the CTE query once or twice? For instance: ;WITH deptCTE(id, department, parent) AS (SELECT id,department,parent FROM Departments)SELECT q1.department,q2.department FROM deptCTE q1 INNER JOIN deptCTE q2 ON q1.id = q2.parent WHERE q1.parent IS NULL; The following execution plan …
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 …
This week I completed another successful corruption repair. Here is what the client had to say when the database was repaired: Stedman Solutions were no less than amazing! We were in a major bind; we had corruption in a …
The question came up as a blog comment of “To delete 100,000 row chunks from a 9,542,067 row table, how about” Thats a great question. I have had to do something very similar lately. The problem that you run into …
Q & A – Deleting lots of rows from a huge table. Read more »
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 …
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) …
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 …
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 …