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 …
One of the common questions that I get asked after my Unleashing CTE Presentation is “Can we use CTEs in SSRS reports?” The answer is YES, Common Table Expressions work great in SSRS reports, one of my favorite ways of …
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 …
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 …
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 …
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 …
The following example shows how to create a Tree Path with a Common Table Expression (CTE). First off what is a tree path? For this example I will be referring to the product categories at a camping and fitness store, …
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 …
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 CTE’s. To use multiple CTE’s …
As part of my planning for the SQL Saturday Presentation in Vancouver I am creating an hour long presentation on Common Table Expressions. The easiest way to do a recursive query in SQL server is to use a recursive CTE …