Using a Common Table Expression Instead of a Derived Table

Here is a short video that shows how to convert those ugly derived table (SubQuery) queries into a common table expression to help clean up your TSQL code. This applies to Common Table Expressions on Microsoft SQL Server, and the recording comes from my Free SQL Query Training course.



Other CTE Related Posts:

What is a Common Table Expression

I just a added a video to YouTube titled “What is a Common Table Expression”.  This video covers the extreme basics of what a CTE is on Microsoft SQL Server, and how to write that CTE in TSQL. Here is the video:

It’s a very short video with the basics of what a CTE is.  Take a look.


Other CTE Related Posts:

TSQL OUTPUT Clause on Insert Statements

One of the often overlooked features of SQL Server is the OUTPUT clause, which applies to INSERT, UPDATE, DELETE, and MERGE statements. In this post we are going to go over the basics of using the OUTPUT clause on an INSERT statement.

What the output clause does is return a result set on queries that don’t usually return a result set like INSERT, UPDATE, DELETE, and MERGE. You can have the INSERT statement return the full row that was inserted, including any columns that were filled in automatically, for instance with default values, or with IDENTITY values.

The output clause was added to Microsoft SQL Server in version 2005, and is available in all editions including Express, Standard, Enterprise, Developer and Azure.

Lets take a look at how it works.  First we will set up a sample database to work with. Caution: this script created a database called QueryTraining on your SQL Server. If you don’t wish do create the database, or don’t have permissions to do so, just create the table, and work in your existing database.

USE Master;

CREATE DATABASE [QueryTraining];

USE [QueryTraining];

CREATE TABLE [dbo].[Inventory](
[InventoryID] [INTEGER] IDENTITY(-2147483647,1) PRIMARY KEY,
[Category] [VARCHAR](200),
[ItemSKU] [VARCHAR](200),
[Description] [VARCHAR](4000),
[QuantityOnHand] [INT],
[UnitCost] DECIMAL(10,2),
[UnitRetail] DECIMAL(10,2),
[Archived] BIT

INSERT INTO dbo.Inventory (Category, ItemSKU, Description, QuantityOnHand,
UnitCost, UnitRetail, Archived)
('SLEEPINGBAG', 'SB001-LG-RED', 'Sleeping Bag - Large - RED', 5, 5.75, 19.99, 0),
('SLEEPINGBAG', 'SB001-LG-GRN', 'Sleeping Bag - Large - GREEN', 13, 5.75, 14.99, 1),
('SLEEPINGBAG', 'SB001-LG-BLK', 'Sleeping Bag - Large - BLACK', 1, 5.75, 19.99, 0),
('TENT', 'TT001-10-CMO', 'Tent - 10 Man - CAMO', 5, 10.00, 99.95, 0),
('AXE', 'AX013-HT-BLK', 'Axe - Hatchet - Black', 35, 4.99, 24.95, 0),
('FOOD', 'MR005-1S-SPG', 'MRE - 1 Serve - Spaghetti', 5, 0.50, 7.99, 0),
('FOOD', 'MR006-1A-PIZ', 'Pizza', 1, NULL, 9.95, 0);

If you are wondering why the IDENTITY is seeded with the large negative value, see my article on this topic.


Now that the database is set up, we will start with a basic insert statement, without the output clause.

INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
						   QuantityOnHand, UnitCost, UnitRetail, Archived)
('FOOD', 'MR009-210-ICE', 'MRE - Ice Cream - Chocolate', 1, NULL, 11.95, 0);

Which produces the following basic output:



Now we will modify the query and simply add the following before the VALUES keyword:


So the query ends up looking like this:

INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
 QuantityOnHand, UnitCost, UnitRetail, Archived)
('FOOD', 'MR009-209-ICE', 'MRE - Ice Cream - Rocky Road', 1, NULL, 11.95, 0);

Now we get the following output:


When scrolled to the right, you can see the entire row that was inserted was returned, plus the InventoryID which came from the IDENTITY value is returned also. This result set can be processed in code like any result set returned from a typical SELECT statement.

What if I just want the IDENTITY value that was inserted.

You just change the OUTPUT clause instead of INSERTED.* you ask for INSERTED.InventoryID (or whatever your IDENTITY column is called).

INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
 QuantityOnHand, UnitCost, UnitRetail, Archived)
('FOOD', 'MR009-211-ICE', 'MRE - Ice Cream - Vanilla', 1, NULL, 11.95, 0);

When run you can see just a single column with the InventoryID returned in the result set.



Technically at this point there are many other ways to get the inserted ID, including @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT, and various other methods that people have creatively come up with over time.

Multiple Values

Now, how about returning multiple values, that’s something that you can’t get with IDENTITY, SCOPE_IDENTITY, or IDENT_CURRENT.

Let’s take a look.

INSERT INTO dbo.Inventory (Category, ItemSKU, Description,
 QuantityOnHand, UnitCost, UnitRetail, Archived)
('FOOD', 'MR009-212-ICE', 'MRE - Ice Cream - Cookies and Cream', 1, NULL, 11.95, 0),
('FOOD', 'MR009-213-ICE', 'MRE - Ice Cream - Chocolate Chip', 1, NULL, 11.95, 0);

Nothing changes on the OUTPUT line, the only change is the addition of a second row when inserting the values. By the way this format for insert requires SQL Server 2008 or newer, it doesn’t work on SQL Server 2005.

Here is what we get back:



There you have it. The basic overview of the OUTPUT clause on INSERT statements.


See Also

My SQL Server is Out Of Disk Space

As the DBA, you get a call from the IT Team who monitors disk space and they tell you the following:

“One of our server that has SQL Server running on it is running low on disk space. It has gone from around 60% disk utilization to 95% disk utilization just overnight. Whats the problem?”

In this case it happens that this is a server that you have never seen before. Someone installed a SQL Server to test out something product along the way, they never told the DBA about it, and suddenly this sever has become an important part of the company internal tools.

Sound familiar?  I know I have been hit with this before.


Right off, you have never seen this server before, you have no established baseline to compare it to, and you need to find out why it is running out of disk space rapidly. For me, some of the first things that would cross my mind would be:

  • Has anyone installed a new database or product new on this server recently?
  • Are any of the databases growing rapidly?
  • Are backup files being saved locally?
  • Are backups being run at all?
  • How big are each databases data files and log files?
  • Is there something outside of SQL Server taking up space?

Before I start troubleshooting I am going to just remote desktop to the server and double check the disk space myself to be sure that someone hasn’t misinterpreted the disk utilization.



At first glance the free disk space is 4.39GB out of 63GB.




As I am thinking about where to look first, the disk space drops to 2.28GB free.  Now I am very concerned.  I know that the C: Drive is filling up, this sever only has a C: drive, and if the Windows operating system runs out of disk space on the boot drive C:, then the server will crash, and often times will no longer boot.  Now things are serious, and must be dealt with quickly.

Now to start hunting, I could dig out several dozen of my favorite trouble shooting queries, or I could just run Database Health Monitor to start troubleshooting the issue. Database Health Monitor is my quick choice to track down problems like this.

I start up Database Health Monitor and connect to the SQL Server that is running out of space, to start looking around.

Initial View


Not immediately jumps out.  I see 12 databases on this SQL Server, and I see that the PerformanceTrouble database is using more CPU than any other database on the system.  Now to start looking at individual databases. The first database listed is called BadDB.  For this demo I am using a SQL Server that I use to test the Database Health Monitor Application, and I tend to use names that are descriptive of what the database is intended to do.



From this database I can see a few red flags. Well, we learn that the Database and Log files all exists on the C: Drive, and that this could cause the database to run out of disk space if the database grows, but this database isn’t very big, its only a few Megabytes, so I am not worried here.

Next I take a look at the Real Time Reports for Backup Sizes:



For this database I can see that it has grow over the last year from around 2 megabytes to around 10 megabytes. All really small.  I also notice from the File names that the backups are being saved on the C: drive too.

As I click through the rest of the databases I see very similar results, its not great, but none of them are very big until I get to the database called PerformanceTrouble. Database Health Monitor shows this overview.



Now this page points out a few things.  In bright red, there is a notice that shows the amount of log space allocated and being used by this database is around 24GB, for a database that is only 341MB in size.  This is an indication that backups are not being run, or that backups are not being run often enough, for instance log backups.  On this same page, I can see that the last full backup was run today at 5:00am.  I then click through the rest of the databases and nothing jumps out anywhere near this problem.

At this point I have spent about 3 minutes running Database Health Monitor, and I have found one problem that is using up about 33% of our entire hard drive.


How do we fix this issue?

First if I just doubleclick the Large Log File warning at the bottom of the application, my browser will open with one solution. There is a solution presented that looks a lot like this:

First we run this query to get the names of the log files.

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;


From that query we see that the name of the log file is PerformanceTrouble_log, the script below would get updated that your actual database name and log file name. The following script will shrink the log file for this database to around 1mb.

WARNING: Running this script will disconnect all other users from the database. Only run this on a production database if you have no other option.

-- Truncate the log by changing the database recovery model to SIMPLE.
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (PerformanceTrouble_log, 1);
-- Reset the database recovery model.
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

Once the script is run we can see that the log file has been shrunk from the 24GB to be around 1MB.

When we check the drive space on the server it now looks like this:
Now we are no longer at the the critical point where we think the server is going to run out of space, but there are two things that should be done next.

1. Run a full backup of this database.  If your SQL Server were to crash before the full backup is run you would have no way to recover back to the current point in time.

2. Monitor and confirm if the database log is continuing to grow, or if this was maybe a one time event that caused it to grow this large. If you determine that it is still growing then research and find out what is causing the transaction log to fill up.  You can use many of the features of Database Health Monitor to do this research.


The overall troubleshooting process took about 3 minutes, and the time to fix this issue only took around 2 minutes.

SQL Forensics

If you owned a convenience store, or other business that was likely to get robbed you would likely have surveillance cameras with the intention of having evidence to provide the police with the information that they need in order to track down and prosecute the person who may rob the store.


Most of our databases have far more value to our company than the entire contents of a small convenience store, and whether you are willing to admit it or not they are probably more likely to get hacked than a convenience store is to get robbed. But we don’t have the surveillance system in place to monitor what happened when and why.

Once you have been hacked it is very challenging to find out what someone may have done to your databases, especially when you don’t find out for several months.

As a DBA, imagine your manager coming to you saying that 3 months ago between the 10th of the month and the 26th of the month the IT team has determined that a hacker obtained unauthorized access to the network. They have a log of all files that were transferred out of the network.  What we need from you is to know that parts of the database did the change or modify. Did they install a job anywhere that checks for confidential information and emails it to them on a regular basis, did they alter a stored procedure to move money into their account? Did they add another user to the database that will allow them unlimited access whenever they want? How would you answer these questions.

It would be nice if we could just ask SQL Server for a forensic analysis of what occurred between the dates in question and get a quick summary of what was changed, who was in the system and what might have been done. Unfortunately SQL Server doesn’t have a good way to do this, you could get a product that would scan the transaction logs to show what may have changed, if you still have the transaction logs from 3 months ago. You could get some of what you are looking for from Extended Events, maybe some from SQL Source control if you are using a source control product.

All those after the fact solutions are like working with a sketch artist for a convenience store robbery because you didn’t have a functioning camera.

If your company is large enough to have a data center, or to use a data center co-location facility there are probably plenty of cameras. But none of the cameras will catch the hacker with remote access to your systems.


Many of us when asked what changed on our SQL Server 3 months ago over a 2 week period would probably do some research and eventually come up with the “I don’t know” answer. Management may see it like this… “You don’t know if anyone did anything to compromise the integrity of your database, What good are you?”

How do we solve this. Plan ahead, it can actually be easier that installing video cameras. Anyone who has ever installed surveillance cameras knows how painful it can be to get them in the right place, the right angle, the right view.

Github Open Source Project

Introducing SQL Forensics, an open source project that I have just started hosted at Github.

The reason that I have created this as an open source project is to have the highest level of transparency in the project. All the source code is there so look it over, don’t trust me, confirm for yourself everything that it is doing to monitor your system, and if you don’t like something it is doing, then just change it.

What does it do today… Not much since I just started working on it yesterday. But here is where I plan to go with it.

  • Tracking of who changed what and when
    • Stored Procedures
    • Functions
    • Tables
    • Triggers
    • Foreign Keys
    • Schemas
    • Users
    • Logins
    • Permissions
    • Add/Remove a Database
    • Database configurations
    • Instance / Server configurations
  • Alerting
  • Investigation Tools

So far if you install the SQL Forensics database it tracks any changes made to the global configuration settings for the current server, sp_configure. You can see the baseline, and any changes by querying the [Log] table.

SELECT li.[name], cast(l.[whenRecorded] as datetime), l.[value]
  FROM [ForensicLogging].[Log] l
  INNER JOIN [ForensicLogging].[LogItems]  li on li.[id] = l.[itemId]
  ORDER BY [whenRecorded] DESC;

sp_configure_advanced_optionsThis shows that sp_configure ‘show advanced options’ was changed 3 times, but reconfigure was never run.  The value column shows the configuration value, as well as the value currently in use.



What would you need to know?

What do you need to know for a proper SQL Forensic investigation to know if someone is in the system?  Drop me an email or post a response to this message with your needs.


Database Health Monitor – Beta 8 – Soft launch

Today I launched Database Health Reports Beta 8 as a soft launch. What I mean by the soft launch is that other than updating the DatabaseHealth website and this blog posting, I haven’t done much to promote it.  Why you might ask?  Due to it being a holiday week between Christmas and New Years, there are many people out of the office. I figured I would wait until after January 1st to make a big splash.

Beta 8 is out. The biggest change is the charting. About 75% of the charts in the system have been completely rewritten. I was using a charting module that didn’t allow for the flexiblity that was needed to make the charts look the way I wanted. In September right after the Beta 7 release I started rewriting the charts from scratch.  Between now and the next Beta I will work to get more of the charts converted over to the new look.

Here is an example of some of the newer charts. The red and green bars on the plan cache are used to indicate change.  Red indicates that the value was worse, and green indicates that it improved or stayed the same. The CPU by database chart was rewritten to make better use of the available space.

New Charts


Also shown in the above screen shot is the new Server Configuration panel with details on the specific version of SQL Server, when it was installed, the number of processors and more.

Beta 8 Release Notes

Beta 8 Released 12/29/2013.

The following changes have been made since Beta 7. The big feature in the Beta 8 is the rewrite of many of the charts.

New Features

  • Renamed to Database Health Monitor, attempting to avoid confusion with SSRS Reports.
  • Additional checks for obsolete or unusual settings (SHRINK_DATABASE, TORN_PAGE_DETECTION).
  • Blocking reporting with hierarchical drilldown on the blocking queries.
  • Server details panel showing logical and physical cpu counts, SQL Server Start time, SQL Server install data, Server Name, SQL Server version info, and information on real or virtual server.

Bug Fixes

  • Multithreading the re-connect of databases, vastly improving the startup time if one or more databases is not available.
  • A large amount of the project has been refactored to help mature the product and allow for additional feature growth, and reduce bugs.
  • Improved background threading.
  • Updates and bug fixes on SQL Technical Debt.