Multiple CTE’s in a single Query

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 and optional columns for the next CTE, open the CTE query with a comma, write the query, and access it from a CTE query later in the same query or from the final query outside the CTEs.

-- First CTE
;WITH fnames (name) AS
(SELECT 'John' UNION SELECT 'Mary' UNION SELECT 'Bill'),

-- Second CTE
minitials (initial) AS
(SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'),

-- Third CTE
lnames (name) AS
(SELECT 'Anderson' UNION SELECT 'Hanson' UNION SELECT 'Jones')

-- Using all three
SELECT f.name, m.initial, l.name
FROM fnames f
CROSS JOIN lnames AS l
CROSS JOIN minitials m;

Another way that this could be done using SQL Server 2008 or newer is with the row constructor instead of the UNIONs inside of each CTE.

SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x);

This type of row constructor syntax is often used in INSERT statements, but it can also be used in a CTE as shown here:

;WITH Fnames (Name) AS
(SELECT x FROM (VALUES ('John'), ('Mary'), ('Bill')) f(x) ),
Minitials (initial) AS
(SELECT x FROM (VALUES ('A'), ('B'), ('C')) f(x) ),
Lnames (Name) AS
(SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x) )
SELECT F.Name AS FirstName, m.initial, l.Name AS LastName
  FROM Fnames F
 CROSS JOIN Minitials as m
 CROSS JOIN Lnames as L;

Which produces the following output.

multipleCTEs

If you found this interesting and would like to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com.


Paperback

Kindle

Index Statistics Report added to the FREE SQL Server Health Reports

In preparation for an upcoming SQL Saturday presentation I have created another report to add to the FREE SQL Server Health reports project.

The new Index Statistics Report is the 8th report in the series, intended to give DBA’s and database developers access to the information they need to analyze the health of their database.

 

Here is a screen shot of the Statistics report.

Statistics that are out of date may impact the actual usage of those indexes.  With out of date or no statistics, SQL server can’t accurately make the decisions needed for a query to perform well.  You may get an index scan when an index seek may be the better solution.

Again this report is absolutely free something what I want to share with the SQL Server community.  If you have any questions, just send me an email or contact me on twitter @SqlEmt.

It is sad when Santa Claus wants to break into your bank account.

Normally by blog/website is focused only on SQL Server topics, but this just jumped out at me and I have to share it.

 

The holiday shopping season is here again, and so are the scammers.  Here is a copy of an email message that I received today, wanting a few simple things like name, age, eye color, home city, childhood best friend, and more.  Basically everything they need to get into your bank account or other online accounts.  All the “secret questions” that you answer in order to get a password reset on your bank account, or online trading account.

The promise is a letter from Santa, the reality is identity theft.

I order to keep the holiday season safe from scammers do what you can to educate others on these types of scams.

 

 

Backup Set Report added to FREE Server Health Reports

In preparation for an upcoming SQL Saturday presentation I have created another report to add to the FREE SQL Server Health reports project.

This report has been replaced with the Backup Report in the Database Health Project.

The Backup Set Report has been added to the SQL Health Reports.  The Backup Set Report  is the 7th report to be added to the Database Health Reports package.

The Database Health reports package is a FREE collection of several reports that I have created to monitor and maintain the health of your SQL server.

With this report you can quickly check out the sizes and growth of the databases with the 10 largest backups.

Download it and enjoy.  If you have any feedback, please send it my way.

Database Size report added to SQL Health Reports

In preparation for a SQL Saturday presentation I have created another report to add to the FREE SQL Server Health reports project.

 

The Database Size Report has been added to the SQL Health Reports.  The Database Size Report  is the 6th report to be added to the Database Health Reports package.

The Database Health reports package is a FREE collection of several reports that I have created to monitor and maintain the health of your SQL server.

 

With this report you can quickly assess the size of your databases and the logs to determine which may be growing faster than you expect.

SQL Server Join Types – inner join, left outer join, right outer join, full outer join, cross join and self join

I submitted another abstract for SQL Saturday titled TSQL Basics – SQL Server Join Types. Focusing on inner join, left outer join, right outer join, full outer join, cross join and self join.
This is for the SQL Saturday in Vancouver BC sessions.
That makes three sessions for Vancouver now. I still need to figure out the selection process to find out if my ideas will be accepted.

SQL Server Join Types

Presentation Outline

  • What is a JOIN
  • Inner join
  • Left outer join
  • Right outer join
  • Full outer join
  • Cross join
  • Self join
  • Use Samples
For more info see my JOIN virtual class (Class Outline)