Training Employees

Training Employees

A great conversation, not original, but something found on LinkedIn

For employee training, see our Free SQL Query Training program.

 

TSQL OUTPUT Clause With UPDATE Statements

Earlier in the week I added a blog entry on using the OUTPUT Clause on INSERT Statements, today I will expand it by exploring the use of the OUTPUT clause with UPDATE statements.

Generally when you do an UPDATE statement you only get a row count returned, you don’t generally get a result set showing what was changed. With the OUTPUT clause we can have an UPDATE statement return the before and after of the rows being updated. Lets take a look at some sample code to do this.

First we create a sample database and table to work with.

USE Master;
GO

CREATE DATABASE [QueryTraining];
GO

USE [QueryTraining];
GO

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)
VALUES
('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),
('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);

First lets start with a basic update statement without using the OUTPUT clause. This update statement is just renaming the item with a SKU of MR009-213-ICE to have a description of ‘MRE – Ice Cream – Double Chocolate Chip’.

UPDATE dbo.Inventory
SET [Description] = 'MRE - Ice Cream - Double Chocolate Chip'
WHERE [ItemSKU] = 'MR009-213-ICE';

Which gives us the following output, which only shows us the number of rows affected by the UPDATE statement.
UpdateWithNoOutput
But what if you want to know the details associated with the before and after state of the rows being updated.

This time we are going to update the double chocolate chip to triple chocolate chip using the output statement.

UPDATE dbo.Inventory
SET [Description] = 'MRE - Ice Cream - Triple Chocolate Chip'
OUTPUT [deleted].*, [inserted].*
WHERE [ItemSKU] = 'MR009-213-ICE';

UpdateWithOutputBoth
The output above although you can’t see it all as it scrolls off the right, includes the exact rows updated, with the before and the after state of every column.

How about if we only want to see the before and after of the description column, we could try something like this, here we are updating the product to be Strawberry ice cream:


UPDATE dbo.Inventory
SET [Description] = 'MRE - Ice Cream - Strawberry'
OUTPUT [deleted].Description,
[inserted].Description
WHERE [ItemSKU] = 'MR009-213-ICE';

When run the query produces the following output:

UpdateOutputJustWhatChanged

We can see that the ice cream was previously Triple Chocolate Chip, and that this item has been replaced with Strawberry ice cream.

 Updating Multiple Rows

Lets now take a look at a bulk update, all products in the inventory table will have their retail price updated to be 10% more than they currently are. The SQL might look like this:


UPDATE dbo.Inventory
SET [UnitRetail] = [UnitRetail] * 1.10
OUTPUT [deleted].InventoryID,
[deleted].UnitRetail AS PriceBefore,
[inserted].UnitRetail AS PriceAfter;

UpdateOutputMultipleLines

From that result set you can see that for every inventory ID we get the Price Before, and the Price After the update statement was run. All this with a single TSQL statement.

 

I hope at this point you have a good understanding of using the OUTPUT clause with UPDATE statements.

See Also:

Whiteboard Interview Questions

Last week Aaron Buma and I were attended a session at Western Washington University where we were asked to present to the ACM club on how to work through the white board coding questions in the technical interview.

 

Check out my IndieGoGo Campaign for Database Health Monitor

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.

DiskSpace1

 

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

DiskSpace2

 

 

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.

FirstDatabase

 

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:

BackupSizes

 

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.

ProblemDatabase

 

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;

LogFileSizing

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.
ALTER DATABASE PerformanceTrouble SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (PerformanceTrouble_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE PerformanceTrouble SET RECOVERY FULL;
GO
-- 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:
CDriveAfterScript
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.

Setting Time Zones in Uzility

Uzility supports distributed teams in multiple time zones by allowing each user to set their own time zone for their login.

Here is a short YouTube video showing how to change the time zone on your account in Uzility.

 

Visit Uzility.com for more details.