Nested CTEs

June 11, 2013 by Leave a Comment

Day 11 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at how to use multiple nested CTEs in a query.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Russian Nesting Dolls

If you have ever seen the Matryoshka dolls known as the Russian nesting dolls or babushka dolls they are very interesting. They start with a large wooden doll that when opened contains a slightly smaller doll, then inside of that one an even smaller doll. These dolls often have five to eight dolls nested inside of each other.

The interesting thing about these dolls is that they have one specific way that they can all fit together; the smallest doll must be the first one put inside of the next smallest. If you were to lose one of the medium dolls, they could still be placed together; there would just be some extra space inside.

Nested CTEs often remind me of the Russian nesting dolls. Think of the first CTE in a multiple CTE query as the smallest doll. That first CTE can’t reference any other CTEs, much like the smallest doll can’t fit any other dolls inside of it.

Think of the second CTE in a multiple CTE query as the second smallest doll in the Russian dolls. It can only have one doll fit inside of it. CTEs are similar in that the only CTE that can be referenced by the second CTE in a multiple CTE query is the first CTE.

The third CTE is like the third smallest Russian doll. The third smallest doll can only have the smallest or second smallest doll placed inside of it. With the third CTE it can only access the first or the second CTE declared, and not any of the CTEs declared later.

The same concept applies for all of the CTEs in a nested CTE query. Any CTE query can access the CTEs declared prior to that one, but not the ones after.

Nested CTEs

Take an example of a multiple CTE query with 4 CTEs as follows with the … being replaced with a T-SQL query.


;WITH CTE1 AS
  ( .... ),

CTE2 AS
  ( .... ),

CTE3 AS
  ( .... ),

CTE4 AS
  ( .... )

SELECT *
    FROM CTE4;

 

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Filed under CTE, CTE Book Tagged with , , , ,

Database Health Reports – Seven Hundred Downloads So Far

June 10, 2013 by Leave a Comment

Since the initial release of the Database Health Reports project last September, there have been over 700 downloads of the application at this point Beta6 is going strong.

First I would like to say Thank You to everyone using it so far.

Beta 6 introduced a few new features, but mostly focused on overall stability and bug fixing.

  • New Inefficient Indexes report added. Useful to find indexes that aren’t being used.
  • Scrollbar added to the historic waits report to allow for easier browsing of wait stats.
  • Overall improved CTRL-C and CTRL-A clipboard support.
  • Adding a setting dialog to consolidate some of the basic settings.
  • Added a threshold setting (to the new setting dialog) to set the threshold to search on the big clustered indexes report.
  • Added sorting to all the columns in all the grids.
  • Added the application name into the connect string.
  • Improved the call to check for updates when the application starts.
  • Adding another warning to the database overview panel for huge log files.
  • Added additional wait type documentation.
  • Improved error handling.
  • Added the update every X seconds functionality to the database connections report, this includes a setting in the setting dialog to change the every X seconds interval. If you don’t want it to update, just set it to a very large number.
  • New update interval settings for the database overview and server overview panels.
  • Adding Historic Plan Cache Hit Ratio Chart to the Historic Reporting section.

Still my favorite part of the overall product is the historic wait time monitoring. Once historic monitoring is turned, it records data about what may having been causing problems at some point in time. This way when someone make a comment like “The database appeared slow yesterday at 4:25am”, you have the ability to go find out why it may have been slow.

HistoricWaits

On June 6th I received a nice comment on the Raving Fans page. This page was set up to share what people have to say about this application. Since the application is free of charge the only thing I ask is for someone to share what they find with the application:

RavingFan1

Database Health Reports is a free application that I provide to the SQL Server community to help analyze and fix performance problems on your MS Sql Server.

Filed under Database Health Tagged with , , ,

Multiple CTEs in a Query

June 10, 2013 by Leave a Comment

Day 10 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at how to use multiple CTEs in a query.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

 

One of the questions that comes up when discussing CTE Scope is can I do multiple CTEs. The answer is yes you can do multiple CTEs but the scope is is still constrained to a single SQL Statement.

Multiple CTEs in a Single Statement

Lets take a look when multiple CTEs in a single statement might be useful. Take the example of trying to generate random names in order to fill up tables in a test database to simulate a full database during the development phase of a new project. This example is explained in much further detail in chapter 6 in my Book on Common Table Expressions. Lets take a look here at this example from my SQL Saturday presentation. The following is a CTE that returns a list of first names.


;WITH Fnames (Name) AS
(

SELECT 'John'
 UNION
 SELECT 'Mary'
 UNION
 SELECT 'Bill'

)
SELECT F.Name FirstName
 FROM Fnames F;

When run we see the following:

MultipleCTEs1

 

Now if we want to expand on that we can add a second CTE for last names and CROSS JOIN the results of the two CTEs. A refresher on CROSS JOIN, the CROSS JOIN takes every row from one side and matches it up with every rows for the other side, with no ON clause. This effectively creates a Cartesian product of the two tables, or joins up every possible combination.


;WITH Fnames (Name) AS
(
SELECT 'John'
 UNION
 SELECT 'Mary'
 UNION
 SELECT 'Bill'
 ),

Lnames (Name) AS
(
SELECT 'Smith'
 UNION
 SELECT 'Gibb'
 UNION
 SELECT 'Jones'
 )

SELECT F.Name AS FirstName, L.Name AS LastName
 FROM Fnames AS F
 CROSS JOIN Lnames AS L;

MultipleCTEs2

Here you will notice that the initial CTE is separated from the new CTE with a comma, and that the second CTE doesn’t start with the WITH keyword. Then in the final SELECT statement it just references both of the CTE statements declared above. When run we see the following results.

MultipleCTEs3

 

Here you can see that every first name is joined up with every last name, with two input tables of 3 rows each the result set has 3×3 or 9 rows of output.

You could also add a third CTE with middle names, and possibly a 4th with login names, and very quickly end up with a very large result set multiplying each time there is another cross join added.

 

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Filed under CTE, CTE Book Tagged with , , , , , ,

Expanding on Recursive Dates CTE

June 9, 2013 by Leave a Comment

Day 9 of Common Table Expression Month (June) at SteveStedman.com, today I will be building on the intro to recursive CTEs from two days ago and on showing how a recursive CTE can be used to calculate information about dates of the year from yesterday. This would be useful if you were trying to build a calendar.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Recursive Review

Yesterday’s topic was the introduction to recursive CTE’s.

RecursiveRevew

In the introduction to recursive CTE’s we covered the declaration of the CTE, the Anchor Query which starts the recursive process, the Recursive Query which continues the recursion, and the Query that calls the CTE.

Dates CTE Review

In the following picture, the CTE is named Dates, the anchor query start out by just selecting January 1st of 2013. Next the recursive part selects CalendarDate from the Dates CTE and it adds a single day to it. This all continues recursively as long as the date is less than January 1st 2014.

RecursiveDates1

Adding In More Details

For today’s example the CTE will be exactly the same as yesterday, but the query calling it is different. The part highlighted below shows the difference from yesterday.

MoreRecursiveDates1

When run we get the following output:

MoreRecursiveDates4

Next I borrow part of the date formatting from @DevonDBA from his post at http://devondba.blogspot.co.uk/2013/06/create-table-of-dates-showing-holidays.html, and slightly modify it as shown below. Thanks Martyn.

MoreRecursiveDates2

Now we can see if the day that we are looking at is a weekday or a weekend as shown here:

MoreRecursiveDates3

I hope you are enjoying CTE Month at SteveStedman.com.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Filed under CTE, CTE Book Tagged with , , , , , , ,

SQL Server 2014 Announced

June 8, 2013 by Leave a Comment

Microsoft has announced the next version of SQL Server will be SQL Server 2014, and that a pre-release version will be available later this year.

Some of the features announced SQL Server 2014 are:

  • new disaster recovery and backup solutions with Windows Azure
  • enhanced self service BI tools
  • high availability improvements, to the Always On capabilities
  • in memory OLTP for 10x to 50x performance improvements
  • Improvements to the resource governor
  • New cloud migration wizard

This should be interesting. If it goes the way of SQL Server 2012 it should be released in the spring, but that is just a guess.

Filed under SQL 2014 Tagged with , , ,

Recursive CTE for Dates In A Year

June 8, 2013 by Leave a Comment

Day 8 of Common Table Expression Month (June) at SteveStedman.com, today I will be building on the intro to recursive CTEs from yesterday and showing how a recursive CTE can be used to calculate information about dates of the year. This would be useful if you were trying to build a calendar.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Recursive Review

Yesterday’s topic was the introduction to recursive CTE’s.

RecursiveRevew

In the introduction to recursive CTE’s we covered the declaration of the CTE, the Anchor Query which starts the recursive process, the Recursive Query which continues the recursion, and the Query that calls the CTE.

Recursive CTE for dates in a Year

In the following picture, the CTE is named Dates, the anchor query start out by just selecting January 1st of 2013. Next the recursive part selects CalendarDate from the Dates CTE and it adds a single day to it. This all continues recursively as long as the date is less than January 1st 2014.

RecursiveDates1

There is an additional setting. The OPTION (MAXRECURSION 366) has been added to go past the default 100 levels of recursion.

When we run the query we get the following results:

RecursiveDates2

Which continues all the way to December 31st 2013.

</span>
 ;WITH Dates as
 (
 SELECT cast('2013-01-01' as date) as CalendarDate

UNION ALL

SELECT dateadd(day , 1, CalendarDate) AS CalendarDate
 FROM Dates
 WHERE dateadd (day, 1, CalendarDate) < '2014-01-01'
 )

SELECT *
 FROM Dates
 OPTION (MAXRECURSION 366);
<pre>

 

How Would This Be Useful:

Chapter 10 of the CTE book has a section on finding holes in patterns. Basically you want to query for things that you have it is generally straightforward, but if you want to query for things that you don’t have it is not as easy. Lets say you are working on a scheduling application that needs to look at a list of dates and find the dates that a venue may be available. You can probably easily query the dates the venue is in use, and with the Dates CTE you could then do a left join with exclusions where you left join the Dates CTE to the dates that a venue is in use, then only select the results where the venue date is null. This would return the dates that the venue is available.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Filed under CTE, CTE Book Tagged with , , , , , , , ,

← Older posts

Newer posts →