Search results matching tag 'Composable DML'

Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Thu, 07 Feb 2013 13:59:57 GMT

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:

INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;

This statement updates some rows in [tgt] and stores the old values in [old_values]. I think that’s rather useful, especially in a data warehousing scenario where one may wish to MERGE to a type 1 dimension table. Unfortunately this scenario gives rise to another limitation of Composable DML – the value returned by @@ROWCOUNT is the number of rows that were affected in [old_values], not in [tgt]. The following code (which you can simply copy/paste and execute and which is also available on pastebin) demonstrates this problem:

/******************************************************************************************************************************
A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that 
I don't think there is a way to capture the number of rows affected by the MERGE
 
Jamie Thomson, 2013-02-07
******************************************************************************************************************************/
 
/*Setup table first and insert some data into [src]*/
USE tempdb
IF OBJECT_ID('src') IS NOT NULL		DROP TABLE src;
CREATE TABLE src (
	id		INT
,	name    NVARCHAR(MAX)
);
IF OBJECT_ID('tgt') IS NOT NULL		DROP TABLE tgt;
CREATE TABLE tgt (
	id		INT
,	name    NVARCHAR(MAX)
);
/*[updates] will be used as the target of the Composable DML insertion*/
IF OBJECT_ID('old_values') IS NOT NULL		DROP TABLE old_values;
CREATE TABLE old_values (
	id		INT
,	name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;
SELECT	[@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
GO 2

Here is the output:

Notice that the Composable DML containing the MERGE statement is executed twice. The first execution inserts two rows into [tgt] yet @@ROWCOUNT returns zero because zero rows were inserted into [old_values] by the outer query. The second execution results in two rows in [tgt] being updated hence two rows are inserted into [old_values] and hence why @@ROWCOUNT returns two. It appears there is no way to discover the number of inserts or updates that were committed by the MERGE; if you’re a fan of logging rowcounts during ETL operations (which I think you should be) then this is a big problem. The only way I can think of getting around this problem is to break the statement into two like so (for brevity I haven’t included the full code listing so it is also available on pastebin):

/*Setup part is the same as before, we do need an extra table for capturing the output of our MERGE tho*/
IF OBJECT_ID('mrgout') IS NOT NULL		DROP TABLE mrgout;
CREATE TABLE mrgout (
	action_			NVARCHAR(MAX)
,	inserted_id		INT
,	inserted_name    NVARCHAR(MAX)
,	deleted_id		INT
,	deleted_name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
TRUNCATE TABLE mrgout;
INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
SELECT	mrgout.action_
,		mrgout.inserted_id
,		mrgout.inserted_name
,		mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
;
INSERT	dbo.old_values(id,name)
SELECT	deleted_id,deleted_name FROM mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
SELECT	[INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)   GO 2

Executing that returns:

This is much better. We now know the tally of insertions and updates committed by the MERGE, unfortunately we have had to do it in two separate statements which in a way defeats the point of using MERGE in the first place (and don’t forget some of the other current problems with MERGE). If you can think of a better way of doing it then I’m all ears – please reply in the comments below.

I’m not saying don’t use MERGE and I’m not saying don’t use Composable DML; just be aware of their limitations. Personally I think there should be built-in functions, similar to @@ROWCOUNT, that return the number of rows INSERTed/DELETEd/UPDATEd by a MERGE; Aaron Bertrand agreed and raised a Connect submission to that affect: Katmai : Merge does not distinguish rowcounts in triggers which has, unfortunately, “been closed as won’t fix”.

@Jamiet

Composable DML cannot insert into a table with a FOREIGN KEY

jamiet — Fri, 10 Aug 2012 09:48:00 GMT

I have written on here about the benefits of Composable DML (i.e. the ability to insert the data outputted from an OUTPUT clause into a table) before however I discovered a limitation today that I think is worth sharing. Basically, the table that you’re inserting into cannot have a FOREIGN KEY constraint nor can it have a FOREIGN KEY constraint pointing to it. Here’s some code that demonstrates the first of these two restrictions:

/*This code demonstrates the fact that you can't use composable DML to insert into a table that has a FK*/
USE tempdb
CREATE TABLE [TableWithPK] ([col1] INT PRIMARY KEY);
CREATE TABLE [TableWithFK] ([col1] INT REFERENCES [TableWithPK]([col1]));
 
/*Following statement fails with error:
Msg 356, Level 16, State 1, Line 17
The target table 'TableWithFK' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the 
FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.*/
INSERT	[TableWithFK]([col1])
SELECT	[col1]
FROM	(
		INSERT	[TableWithPK]([col1])
		OUTPUT	[INSERTED].[col1]
		VALUES(1)
		)insert_out
;

Definitely one to be aware of. Judging by this Connect submission: Insert over DML is not allowed on a table with a foreign key constraint this isn’t going to change any time soon either.

@Jamiet

P.S. I have put the above code up on PasteBin too: http://pastebin.com/jk3q9P2e . I’ve started to use PasteBin more and more recently, take a look at http://pastebin.com/u/jamiet to see what else I have shared.

Using Composable DML to maintain entity history [T-SQL]

jamiet — Fri, 13 Jul 2012 13:09:00 GMT

I recently inherited a data warehousing SQL Server solution from my good buddy James Rowland-Jones (you may know him from his more auspicious role on the SQLPass board) and it contained a very cool technique of using composable DML (a feature that arrived in SQL Server 2008) to maintain history of updates to a particular table. I knew about composable DML (and have blogged about it previously) however had never considered how useful it could be in a data warehousing scenario, hopefully James won’t mind if I share what I’ve learned in this blog post.

In our case we have a Customer dimension that needs to be maintained as a type 2 however for speed of querying purposes we also need to have a table that provides the most recent state view of all our customers. For demo purposes, here are our two tables:

CREATE TABLE [#Customer] (
	[CustomerSurrogateKey]	INT		IDENTITY(1,1)	PRIMARY KEY
,	[CustomerNaturalKey]	NVARCHAR(100)
,	[NumberOfChildren]	TINYINT
,	[HomeTown]		NVARCHAR(MAX)
,	[EffectiveDateTime]	DATETIME2(7)
,	CONSTRAINT [Customer_UK] UNIQUE ([CustomerNaturalKey],[EffectiveDateTime])
);
CREATE TABLE [#CustomerCurrent] (
	[CustomerCurrentSurrogateKey]	INT		IDENTITY(1,1)	PRIMARY KEY
,	[CustomerNaturalKey]		NVARCHAR(100)			UNIQUE
,	[NumberOfChildren]		TINYINT
,	[HomeTown]			NVARCHAR(100)
);

[#Customer] is our type 2 dimension table and [#CustomerCurrent] will store the most recent state of each customer.

Composable DML allows us to take data from the OUTPUT clause of an INSERT or MERGE statement and insert that data elsewhere; in our case we are going to first MERGE into [#CustomerCurrent] and then insert the new or updated records into [#Customer]. Like so:

DECLARE	@newData TABLE (
	[CustomerNaturalKey]	NVARCHAR(100)
,	[NumberOfChildren]	TINYINT
,	[HomeTown]		NVARCHAR(100)
);
/*Manufacture some incoming new data*/
INSERT	@newData([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
VALUES  (N'Jamie',1,N'London')
,	(N'Sarah',0,N'Birmingham')
,	(N'Ginny',0,N'Glasgow');
/*MERGE into #CustomerCurrent and insert new/updated records into #Customer using Composable DML*/
INSERT	#Customer([CustomerNaturalKey],[NumberOfChildren],[HomeTown],[EffectiveDateTime])
SELECT	ISNULL([mergeOutput].[InsertedCustomerNaturalKey],[mergeOutput].[DeletedCustomerNaturalKey])
,	ISNULL([mergeOutput].[InsertedNumberOfChildren],[mergeOutput].[DeletedNumberOfChildren])
,	ISNULL([mergeOutput].[InsertedHomeTown],[mergeOutput].[DeletedHomeTown])
,	SYSDATETIME()
FROM	(
		MERGE	#CustomerCurrent tgt
		USING	@newData src
			ON	tgt.[CustomerNaturalKey]	=	src.[CustomerNaturalKey]
		WHEN NOT MATCHED THEN
			INSERT([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
			VALUES(src.[CustomerNaturalKey],src.[NumberOfChildren],src.[HomeTown])
		WHEN MATCHED AND
				(	tgt.[NumberOfChildren]	<>	src.[NumberOfChildren]
				OR	tgt.[HomeTown]		<>	src.[HomeTown]
				)
				THEN
			UPDATE	
			SET		[NumberOfChildren]	=	src.[NumberOfChildren]
			,		[HomeTown]	=	src.[HomeTown]
		OUTPUT	$ACTION					AS [Action]
		,		DELETED.[CustomerNaturalKey]	AS [DeletedCustomerNaturalKey]
		,		DELETED.[NumberOfChildren]	AS [DeletedNumberOfChildren]
		,		DELETED.[HomeTown]		AS [DeletedHomeTown]
		,		INSERTED.[CustomerNaturalKey]	AS [InsertedCustomerNaturalKey]
		,		INSERTED.[NumberOfChildren]	AS [InsertedNumberOfChildren]
		,		INSERTED.[HomeTown]		AS [InsertedHomeTown]
		)[mergeOutput]
WHERE	[mergeOutput].[Action] IN ('UPDATE','INSERT')
;

Take a minute or two to look at that code and work out what is going on:

We take the new data in @newData and compare it to the target, [#CustomerCurrent], to see if there are any new or updated records. If there are, do the necessary INSERT or UPDATE within the MERGE.
Output the newly inserted/updated data using the OUTPUT clause
Insert the new/updated records into our type 2 dimension table, [#Customer].

After we run this we end up with this data in the two tables:

Obviously there is no difference at this stage. The real benefit comes when we update and insert new records; let’s model that scenario by changing what’s in @newData and running the same code:

INSERT	@newData([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
VALUES  (N'Ginny',0,N'Edinburgh')	--Ginny has moved from Glasgow to Edinburgh
,	(N'Mike',2,N'Leeds')		--Mike is a new customer
;
--Run the MERGE with Composable DML again....

Which leaves us with:

Note how we have two records for Ginny in [#Customer] however only one record, showing the current state ([HomeTown]=’Edinburgh’), in [#CustomerCurrent]. The real beauty of this technique is that both operations (i.e. the MERGE into [#CustomerCurrent] and the INSERT into [#Customer]) are done under the same transaction so if either fail, both fail.

Pretty cool, no? Once all the data is inserted then we can take a look at the history of each of our customers

/*This code only works on SQL Server 2012 and beyond due to the use of the LEAD function, however you can achieve similar 
in earlier versions using a technique outlined at http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx
*/
SELECT	c.[CustomerSurrogateKey]
,	c.[CustomerNaturalKey]
,	c.[NumberOfChildren]
,	c.[HomeTown]
,	[StartEffectiveDateTime]=c.[EffectiveDateTime]
,       ISNULL( LEAD(c.[EffectiveDateTime]) OVER(PARTITION BY c.[CustomerNaturalKey] ORDER BY c.[EffectiveDateTime] ASC),                  CAST('20501231' AS DATETIME)              ) AS [EndEffectiveDateTime]
FROM    [#Customer] c

That’s it! Hope this proves useful to some of you. If you want to read more about Composable DML then take a read of Exploring Composable DML. Thank you to James for showing me a great use of this little-known feature in SQL Server.

@Jamiet

Rather than running each of the above snippets individually here is the entire demo script. Simply copy-and-paste everything below into a SSMS query window and hit execute:

CREATE TABLE [#Customer] (
	[CustomerSurrogateKey]	INT		IDENTITY(1,1)	PRIMARY KEY
,	[CustomerNaturalKey]	NVARCHAR(100)
,	[NumberOfChildren]	TINYINT
,	[HomeTown]		NVARCHAR(MAX)
,	[EffectiveDateTime]	DATETIME2(7)
,	CONSTRAINT [Customer_UK] UNIQUE ([CustomerNaturalKey],[EffectiveDateTime])
);
CREATE TABLE [#CustomerCurrent] (
	[CustomerCurrentSurrogateKey]	INT		IDENTITY(1,1)	PRIMARY KEY
,	[CustomerNaturalKey]		NVARCHAR(100)			UNIQUE
,	[NumberOfChildren]		TINYINT
,	[HomeTown]			NVARCHAR(100)
);
DECLARE	@newData TABLE (
	[CustomerNaturalKey]	NVARCHAR(100)
,	[NumberOfChildren]	TINYINT
,	[HomeTown]		NVARCHAR(100)
);
/*Manufacture some incoming new data*/
INSERT	@newData([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
VALUES  (N'Jamie',1,N'London')
,	(N'Sarah',0,N'Birmingham')
,	(N'Ginny',0,N'Glasgow')		
;
/*MERGE into #CustomerCurrent and insert new/updated records into #Customer using Composable DML*/
INSERT	#Customer([CustomerNaturalKey],[NumberOfChildren],[HomeTown],[EffectiveDateTime])
SELECT	ISNULL([mergeOutput].[InsertedCustomerNaturalKey],[mergeOutput].[DeletedCustomerNaturalKey])
,	ISNULL([mergeOutput].[InsertedNumberOfChildren],[mergeOutput].[DeletedNumberOfChildren])
,	ISNULL([mergeOutput].[InsertedHomeTown],[mergeOutput].[DeletedHomeTown])
,	SYSDATETIME()
FROM	(
		MERGE	#CustomerCurrent tgt
		USING	@newData src
			ON	tgt.[CustomerNaturalKey]	=	src.[CustomerNaturalKey]
		WHEN NOT MATCHED THEN
			INSERT([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
			VALUES(src.[CustomerNaturalKey],src.[NumberOfChildren],src.[HomeTown])
		WHEN MATCHED AND
				(	tgt.[NumberOfChildren]	<>	src.[NumberOfChildren]
				OR	tgt.[HomeTown]		<>	src.[HomeTown]
				)
				THEN
			UPDATE	
			SET		[NumberOfChildren]	=	src.[NumberOfChildren]
			,		[HomeTown]	=	src.[HomeTown]
		OUTPUT	$ACTION					AS [Action]
		,		DELETED.[CustomerNaturalKey]	AS [DeletedCustomerNaturalKey]
		,		DELETED.[NumberOfChildren]	AS [DeletedNumberOfChildren]
		,		DELETED.[HomeTown]		AS [DeletedHomeTown]
		,		INSERTED.[CustomerNaturalKey]	AS [InsertedCustomerNaturalKey]
		,		INSERTED.[NumberOfChildren]	AS [InsertedNumberOfChildren]
		,		INSERTED.[HomeTown]		AS [InsertedHomeTown]
		)[mergeOutput]
WHERE	[mergeOutput].[Action] IN ('UPDATE','INSERT')
;
WAITFOR DELAY '00:00:01';
/*Manufacture some incoming new data*/
INSERT	@newData([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
VALUES  (N'Ginny',0,N'Edinburgh')	--Ginny has moved from Glasgow to Edinburgh
,	(N'Mike',2,N'Leeds')		--Mike is a new customer
;
 
/*Execute the same MERGE */
INSERT	#Customer([CustomerNaturalKey],[NumberOfChildren],[HomeTown],[EffectiveDateTime])
SELECT	ISNULL([mergeOutput].[InsertedCustomerNaturalKey],[mergeOutput].[DeletedCustomerNaturalKey])
,	ISNULL([mergeOutput].[InsertedNumberOfChildren],[mergeOutput].[DeletedNumberOfChildren])
,	ISNULL([mergeOutput].[InsertedHomeTown],[mergeOutput].[DeletedHomeTown])
,	SYSDATETIME()
FROM	(
		MERGE	#CustomerCurrent tgt
		USING	@newData src
			ON	tgt.[CustomerNaturalKey]	=	src.[CustomerNaturalKey]
		WHEN NOT MATCHED THEN
			INSERT([CustomerNaturalKey],[NumberOfChildren],[HomeTown])
			VALUES(src.[CustomerNaturalKey],src.[NumberOfChildren],src.[HomeTown])
		WHEN MATCHED AND
				(	tgt.[NumberOfChildren]	<>	src.[NumberOfChildren]
				OR	tgt.[HomeTown]		<>	src.[HomeTown]
				)
				THEN
			UPDATE	
			SET		[NumberOfChildren]	=	src.[NumberOfChildren]
			,		[HomeTown]	=	src.[HomeTown]
		OUTPUT	$ACTION					AS [Action]
		,		DELETED.[CustomerNaturalKey]	AS [DeletedCustomerNaturalKey]
		,		DELETED.[NumberOfChildren]	AS [DeletedNumberOfChildren]
		,		DELETED.[HomeTown]		AS [DeletedHomeTown]
		,		INSERTED.[CustomerNaturalKey]	AS [InsertedCustomerNaturalKey]
		,		INSERTED.[NumberOfChildren]	AS [InsertedNumberOfChildren]
		,		INSERTED.[HomeTown]		AS [InsertedHomeTown]
		)[mergeOutput]
WHERE	[mergeOutput].[Action] IN ('UPDATE','INSERT')
;
 
/*This code only works on SQL Server 2012 and beyond due to the use of the LEAD function, however you can achieve similar 
using in earlier versions using a technique outlined at http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx*/
SELECT	c.[CustomerSurrogateKey]
,		c.[CustomerNaturalKey]
,		c.[NumberOfChildren]
,		c.[HomeTown]
,		[StartEffectiveDateTime]=c.[EffectiveDateTime]
,       ISNULL( LEAD(c.[EffectiveDateTime]) OVER(PARTITION BY c.[CustomerNaturalKey] ORDER BY c.[EffectiveDateTime] ASC),                  CAST('20501231' AS DATETIME)              ) AS [EndEffectiveDateTime]
FROM    [#Customer] c

Exploring Composable DML

jamiet — Sun, 30 Aug 2009 22:33:23 GMT

SQL Server 2008 includes a new feature called “Composable DML” which I hadn’t heard about until I read Adam’s post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE. Composable DML is a new feature in T-SQL that allows you to use the data provided by the OUTPUT clause of a DML (i.e. INSERT, UPDATE, DELETE) statement as a derived table and thus insert it elsewhere. Here I’ll attempt to demo this in as simple a way as possible:


create table #t1
(
    name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
    name varchar(max)
,    rowcnt int
);


insert    @rowcounts
select    x.name, x.rowcnt
from    (
        insert #t1
        output inserted.name, 1 as rowcnt
        select 'Henry' as name
        ) x;

Take a moment to browse that code because its very easy to understand. We simply take the data from the OUTPUT clause of our insertion and then insert it into a table variable. Very handy indeed.

Unfortunately that’s just about all you can do with it. For example, do you want to examine the data in SSMS without having to insert it somewhere first? No can do! Observe:


create table #t2
(
    name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
    name varchar(max)
,    rowcnt int
);


select    x.name, x.rowcnt
from    (
        insert #t2
        output inserted.name, 1 as rowcnt
        select 'Henry' as name
        ) x;

Note on this occasion we’re not inserting the data anywhere and hence we get an error:

So, the only thing we can do with that data is insert it somewhere – we can’t even do a simple SELECT. Seems like a pretty strange restriction to me!!

Lastly, let’s try aggregating it (note the GROUP BY clause):

create table #t3
(
    name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
    name varchar(max)
,    rowcnt int
);

insert    @rowcounts
select    x.name, sum(x.rowcnt)
from    (
        insert #t3
        output inserted.name, 1 as rowcnt
        select 'Henry' as name
        ) x
group    by x.name;

Nope, can’t do that either!!

So, ostensibly Composable DML seems like a very nifty new trick for your bag but in its current form it is fairly limited. Let’s hope it improves in the next version!

@Jamiet