Notes from SQL Server Classes
Have you ever needed to generate a list of dates for the last 30 days. There are lots of different ways to do this with TSQL, but one way is with a recursive CTE. You can then left join against it with another query to generate a list of all dates in a result set, along with the dates for …
Recursive CTE to Calculate Dates for the Last 30 Days Read more »
Just this week the team at Stedman Solutions finished the SQL Server JOIN Types paid training course. I am very excited to complete this and to make it available to the SQL Community. Whether you have been writing SQL Queries for 10 years, or you are on connecting your first 2 tables in a query, there is something that you …
After the last post on Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST(), I realized that although I showed how to use it, I didn’t really explain what it means, or when to use it. That is where this example comes in. First lets take an example that generates simulated dice rolls. What are the odds …
Cumulative Distribution Function (CDF) – Analyzing the Roll of Dice with TSQL Read more »
I came across this example when preparing my CTE presentation a while back. Which produces the following output. Download my book for FREE when you sign up for my mailing list. Receive your free copy today!
The question came up as to how do I parse a query string using TSQL. So here you go, using a common table expression, and basing this on a similar function to what I put together yesterday for split. which produces this output. A quick and easy way to parse a query string in TSQL using a CTE. Receive a …
Using a CTE in a Function to Split Up a Query String Read more »
After my CTE presentation a while back I was asked many questions, and received several great suggestions from people. One question was how does the performance compare between a recursive CTE to generate a hierarchical tree path listing and a query using self JOINs and UNION ALL to generate similar results. To test this I created a simple table …
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 go in development eventually there is a need to do something more. Doing some complex string building to create files …
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. When presenting unleashing Common Table Expressions at SQL Saturday a while back, I was asked a couple of great questions …
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 is produced showing that the Departments table is hit twice with a table scan each time with the same cost. …
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 …