Are you making the most of the OUTPUT clause?
In my previous posts I have looked at using the OUTPUT clause to return GUID values generated by the database and how you can return these to an ADO.Net application. Thinking about what I had written, I concluded that if this was a book then I would not make these two blog posts the first chapters! But that may be where people are missing one of the main benefits of the OUTPUT clause, for debugging and demonstration purposes.
For instance, in the past I have quite often had to fathom out why a long script is not having the correct effect on some data. This can be a common scenario, when a process has been developed and tested on a specific small set of data, that does not have the full coverage, diversity or anomalies found in the live data.
In the past to work out what was wrong the easiest starting point was to add SELECT statements throughout the code and then try and spot what is not quite right, by taking a specific sub-set of data or a record and see what effect each statement has on it. Similarly the OUTPUT clause can be very useful when demonstrating as it gives a better indication exactly what is happening to the data.
e.g. One of my demonstrations of the new MERGE statement used the following table:
USE TEMPDB
GO
CREATE TABLE Products (
[ProductID] [int] NOT NULL IDENTITY CONSTRAINT PK_Products PRIMARY KEY CLUSTERED,
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Colour] [nvarchar](15) NULL,
[FrameSize] AS CAST(RIGHT([ProductNumber],2) AS smallint) ) ;
GO
Which has a surrogate key and computed column for demonstration purposes. I then inserted some data.
INSERT INTO Products ( [Name], [ProductNumber], [Colour] )
VALUES
( 'Mountain-500 Black, 24', 'BK-M18B-24', 'Black'),
( 'Mountain-500 Black, 28', 'BK-M18B-28', 'Black'),
( 'Mountain-500 Black, 32', 'BK-M18B-32', 'Black') ;
GO
To show what was stored I would immediately select the data back.
-- Make sure Grid Mode is on!
SELECT * FROM Products
GO
If I have included the OUTPUT clause it would have saved me some time, by only having the single statement:
INSERT INTO Products ( [Name], [ProductNumber], [Colour] )
OUTPUT inserted.*
VALUES
( 'Mountain-500 Silver, 24', 'BK-M18S-24', 'Silver'),
( 'Mountain-500 Silver, 28', 'BK-M18S-28', 'Silver'),
( 'Mountain-500 Silver, 32', 'BK-M18S-32', 'Silver') ;
GO
To demonstate the Merge statement I needed a load table populated with data:
-- Create a load table
CREATE TABLE Products_Load (
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Colour] [nvarchar](15) NULL )
GO
INSERT INTO Products_Load ( [Name], [ProductNumber], [Colour] )
SELECT [Name], [ProductNo], [Colour]
FROM ( VALUES
( 'Mountain-500S Black, 28', 'BK-M18B-28', 'Black'),
( 'Mountain-500S Black, 32', 'BK-M18B-32', 'Black'),
( 'Mountain-500S Black, 36', 'BK-M18B-36', 'Black'),
( 'Mountain-500S Black, 40', 'BK-M18B-40', 'Black'),
( 'Road-750 Black, 24', 'BK-R19B-24', 'Black'),
( 'Road-750 Black, 28', 'BK-R19B-28', 'Black'),
( 'Road-750 Black, 32', 'BK-R19B-32', 'Black')
) ld ( [Name], [ProductNo], [Colour] ) ;
GO
-- New/Updated Data
SELECT [Name], [ProductNumber], [Colour] FROM Products_Load ;
GO
And I would select the data from the Products table, perform the MERGE and the re-select it:
SELECT * FROM Products ;
-- Doing Updates/Inserts and Deletes on matches to [ProductNumber]
-- Change Name to "Mountain-500S" for "BK-M18B-28", "BK-M18B-32", "BK-M18B-36"
-- Add "BK-M18B-36", "BK-M18B-40", "BK-R19B-24", "BK-R19B-28", "BK-R19B-32"
-- Delete "BK-M18B-24", "BK-M18S-24", "BK-M18S-28", "BK-M18S-32"
MERGE Products AS p
USING
(SELECT [Name], [ProductNumber], [Colour] FROM Products_load ) as src
ON (p.[ProductNumber] = src.[ProductNumber] )
WHEN MATCHED AND ( p.[Name] <> src.[Name] OR p.[Colour] <> src.[Colour] )
THEN UPDATE SET p.[Name] = src.[Name], p.[Colour] = src.[Colour]
WHEN NOT MATCHED
THEN INSERT ( [Name], [ProductNumber], [Colour] )
VALUES ( src.[Name], src.[ProductNumber], src.[Colour] )
WHEN NOT MATCHED BY SOURCE
THEN DELETE ;
-- Updated/Inserted/Deleted data
SELECT * FROM Products
ORDER BY ProductID ;
GO
This would return the before and after results sets.
It is not immediately obvious what has changed.
I would then alter the data and run another merge statement, this time using and OUTPUT clause.
-- Change Some Data
UPDATE Products
SET Colour = 'Noir'
WHERE Colour = 'Black' ;
GO
DELETE FROM Products_load WHERE [ProductNumber] = 'BK-M18B-40' ;
GO
INSERT INTO Products_load ([Name], [ProductNumber], [Colour] )
VALUES ( 'City Bike-3000 Red, 24', 'CB-T30U-24', 'Red'),
( 'City Bike-4000 Red, 28', 'BK-T40U-28', 'Red'),
( 'City Bike-4000 Red, 32', 'BK-T40U-32', 'Red');
GO
SELECT * FROM Products_Load
ORDER BY [ProductNumber];
GO
-- Doing Updates/Inserts and Deletes on matches to productId with OUTPUT clauses
MERGE Products AS p
USING (SELECT [Name], [ProductNumber], [Colour] FROM Products_load ) as src
ON (p.[ProductNumber] = src.[ProductNumber])
WHEN MATCHED AND ( p.[Name] <> src.[Name] OR p.[Colour] <> src.[Colour] )
THEN UPDATE SET p.[Name] = src.[Name], p.[Colour] = src.[Colour]
WHEN NOT MATCHED
THEN INSERT ( [Name], [ProductNumber], [Colour] )
VALUES ( src.[Name], src.[ProductNumber], src.[Colour] )
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $ACTION, COALESCE(inserted.ProductId, deleted.ProductId ) AS ProductId,
inserted.[Name] AS [New Name],
inserted.[ProductNumber] AS [New ProductNumber],
inserted.[Colour] AS [New Colour],
inserted.[FrameSize] AS [New FrameSize],
deleted.[Name] AS [Old Name],
deleted.[ProductNumber] AS [Old ProductNumber],
deleted.[Colour] AS [Old Colour],
deleted.[FrameSize] AS [Old FrameSize]
;
GO
This produces something that is clear what changes have been made.
Using the $ACTION keyword it is alot more obvious and you can easily see the effect of the MERGE statement on each row in the Products table.
Doing this is particularly useful if you want to see the effects without changing the data, as the INSERT/UPDATE/DELETE or MERGE statement can be encapsulated in begin and rollback transaction statements.
BEGIN TRANSACTION
-- Merge Statement with OUTPUT clause
ROLLBACK TRANSACTION
The full example script can be found here.