For more information on Common Table Expessions and performance, take a look at my book at Amazon.com: Common Table Expressions Joes 2 Pros®: A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs There are many reasons that a Temp Table, Table Variable or Common Table Expression can be a good solution, but there is …
Temp Table vs Table Variable vs CTE and the use of TEMPDB. Read more »
I came across this example when preparing my CTE presentation for SQL Saturday in Portland OR in 2 weeks. Which produces the following output.
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 CTE, where 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 »
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.
One of the common questions that comes up in my CTE class/presentation asks can you use the CTE inside of a function or stored procedure. The answer is yes, but today I came across another question, how can I split a string (varchar) into rows. I did some searching but didn’t find quite what I was looking for. I did …
SQL Server 2012 introduces 8 new analytic functions. This post will cover 2 of them LEAD and LAG, which can be used to reference a value in the row before or the row after the current row. If you have read my OVER clause articles you will recognize the REVENUE table. Just a table listing department revenue year over year …
After my CTE presentation at SQL Saturday 108 in Redmond, I was asked many questions, and received several great suggestions from people. Based on that feedback, I am updating my presentation for SQL Saturday 114 in Vancouver to include some additional content. One question was how does the performance compare between a recursive CTE to generate a hierarchical tree path …
What is a Factorial: The product of an integer and all the integers below it; e.g., factorial four (4!) is equal to 24. The factorial of a positive integer n, written n!, is the product of all the positive integers from 1 up to and including n Example: 1! = 1 2! = 1 * 2 = 2 3! = 1 * …
Queries with Common table expressions (CTE) are made up of two parts, the CTE part, and the SQL that references the CTE. In preparation for SQL Saturday, the question came up of can you use an INSERT or UPDATE statement with a CTE. Referring to the documentation I confirmed that using an insert or update inside of the CTE is …
As part of my CTE research for my SQL Saturday presentation in Redmond in 2 weeks I decided to take on some classic computer science algorithms with CTE’s. Here is what I cam up with. Any math geek can tell you what the Fibonacci Sequence is, but do you know how to calculate it with a query? First, what is …