Search results matching tags 't-sql' and 'SQL Server'

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

The perils of double-dash comments [T-SQL]

jamiet — Thu, 06 Dec 2012 09:21:39 GMT

I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re developing SSIS packages so head over to SSIS OLE DB Source, Parameters And Comments: A Dangerous Mix! and be educated. Note that the problem is solved in SSIS2012 and Valentino explains exactly why.

If reading Valentino’s post has switched your brain into “learn mode” perhaps also check out my post SSIS: SELECT *... or select from a dropdown in an OLE DB Source component? which highlights another issue to be aware of when using the OLE DB Source.

As I was reading Valentino’s post I was reminded of a slidedeck by Chris Adkin entitled T-SQL Coding Guidelines where he recommends never using double-dash comments:

That’s good advice!

@Jamiet

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

Bitmask data insertions in SSDT Post-Deployment scripts

jamiet — Mon, 11 Jun 2012 10:11:23 GMT

On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing.

Our requirement is a little different though, our data is split up into various buckets that we need to selectively deploy on a case-by-case basis. I was going to use a SQLCMD variable for each bucket (defaulted to some value other than “Yes”) to define whether it should be deployed or not so we could use something like this in our Post-Deployment script:

IF ($(DeployBucket1Flag) = 'Yes')
BEGIN
   :r .\Bucket1.data.sql
END
IF ($(DeployBucket2Flag) = 'Yes')
BEGIN
   :r .\Bucket2.data.sql
END
IF ($(DeployBucket3Flag) = 'Yes')
BEGIN
   :r .\Bucket3.data.sql
END

That works fine and is, I’m sure, a very common technique for doing this. It is however slightly ugly because we have to litter our deployment with various SQLCMD variables. My colleague James Rowland-Jones (whom I’m sure many of you know) suggested another technique – bitmasks. I won’t go into detail about how this works (James has already done that at Using a Bitmask - a practical example) but I’ll summarise by saying that you can deploy different combinations of the buckets simply by supplying a different numerical value for a single SQLCMD variable. Each bit of that value’s binary representation signifies whether a particular bucket should be deployed or not. This is better demonstrated using the following simple script (which can be easily leveraged inside your Post-Deployment scripts):

/* $(DeployData) is a SQLCMD variable that would, if you were using this in SSDT, be declared in the SQLCMD variables section of your 
project file. It should contain a numerical value, defaulted to 0.
 
In this example I have declared it using a :setvar statement. Test the affect of different values
by changing the :setvar statement accordingly.
 
Examples:
:setvar DeployData 1   will deploy bucket 1
:setvar DeployData 2   will deploy bucket 2 
:setvar DeployData 3   will deploy buckets 1 & 2
:setvar DeployData 6   will deploy buckets 2 & 3
:setvar DeployData 31  will deploy buckets 1, 2, 3, 4 & 5
*/
:setvar DeployData 0
DECLARE  @bitmask VARBINARY(MAX) = CONVERT(VARBINARY,$(DeployData));
IF (@bitmask & 1 = 1)
BEGIN     PRINT 'Bucket 1 insertions';
END
IF (@bitmask & 2 = 2)
BEGIN     PRINT 'Bucket 2 insertions';
END
IF (@bitmask & 4 = 4)
BEGIN     PRINT 'Bucket 3 insertions';
END
IF (@bitmask & 8 = 8)
BEGIN     PRINT 'Bucket 4 insertions';
END
IF (@bitmask & 16 = 16)
BEGIN     PRINT 'Bucket 5 insertions';
END

An example of running this using DeployData=6

The binary representation of 6 is 110. The second and third significant bits of that binary number are set to 1 and hence buckets 2 and 3 are “activated”.

Hope that makes sense and is useful to some of you!

@Jamiet

P.S. I used the awesome HTML Copy feature of Visual Studio’s Productivity Power Tools in order to format the T-SQL code above for this blog post.

Use VALUES clause to get the maximum value from some columns [SQL Server, T-SQL]

jamiet — Fri, 20 Jan 2012 13:22:00 GMT

My ex-colleague Paul Mcmillan pointed me at a thread on Stack Overflow that demonstrated a neat T-SQL trick to get the maximum value from a collection of columns in a row. Paul had never seen it before and neither had I so I figure one or two of you out there might learn something from it too.

In short you can use the VALUES clause to effectively union the values into a dataset and get the MAX from that dataset. Better demonstrated with code:

DECLARE @t TABLE(a INT,b INT,c INT); INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5); SELECT * , ( SELECT MAX(val) FROM (VALUES (a) , (b) , (c) ) AS value(val) ) AS MaxVal FROM @t;

I'm sure many of you knew this already but if you didn't, well, you too have learnt something today. See more uses for the VALUES clause at Interesting enhancements to the VALUES Clause in SQL Server 2008

@jamiet

P.S. Oh, this only works in SQL Server 2008 and beyond.

Delete all data from a database

jamiet — Mon, 19 Sep 2011 09:39:00 GMT

Sometimes I use this blog to share scripts that I know I'm going to need at some point in the future, this is one of those times.

The script below will remove all data from a database while respecting/preserving all foreign key constraints - very useful in a development or test environment if you want to get back to a "vanilla" snapshot of your database (i.e. one with no data in it). Usual caveats apply, this is not supported, use at your own risk blah blah blah.

In case you're interested, this is based on the script I provided a couple of years ago at Deriving a list of tables in dependency order which I also leveraged in sp_CascadingDataViewer!

/*Thus script clears all data from a database without requiring you to disable foreign keys
Basically, it orders all the tables according to FKs so that it deletes from them in the correct order.*/
SET NOCOUNT ON;
DECLARE    @schemaName SYSNAME;
DECLARE    @tableName  SYSNAME;
DECLARE    @level      INT;
DECLARE t_cur CURSOR FOR
WITH fk_tables AS (
    SELECT    s1.name AS from_schema    
    ,        o1.Name AS from_table    
    ,        s2.name AS to_schema    
    ,        o2.Name AS to_table    
    FROM    sys.foreign_keys fk    
    INNER    JOIN sys.objects o1                                 ON        fk.parent_object_id = o1.OBJECT_ID    
    INNER    JOIN sys.schemas s1                                 ON        o1.schema_id = s1.schema_id    
    INNER    JOIN sys.objects o2                                 ON        fk.referenced_object_id = o2.OBJECT_ID    
    INNER    JOIN sys.schemas s2                                 ON        o2.schema_id = s2.schema_id    
    /*For the purposes of finding dependency hierarchy we're not worried about self-referencing tables*/
    WHERE    NOT    (    s1.name = s2.name                 
            AND        o1.name = o2.name)
)
,ordered_tables AS
(        SELECT    s.name AS schemaName
        ,        t.name AS tableName
        ,        0 AS LEVEL    
        FROM    (    SELECT    *                
                    FROM    sys.tables                 
                    WHERE    name <> 'sysdiagrams') t    
        INNER    JOIN sys.schemas s                                  ON        t.schema_id = s.schema_id    
        LEFT    OUTER JOIN fk_tables fk                               ON        s.name = fk.from_schema    
                                                                       AND   t.name = fk.from_table    
        WHERE    fk.from_schema IS NULL
        UNION    ALL
        SELECT    fk.from_schema
        ,        fk.from_table
        ,        ot.LEVEL + 1    
        FROM    fk_tables fk    
        INNER    JOIN ordered_tables ot                              ON        fk.to_schema = ot.schemaName    
                                                                       AND   fk.to_table = ot.tableName
)
SELECT    DISTINCT    ot.schemaName,ot.tableName,ot.LEVEL
FROM    ordered_tables ot
INNER    JOIN (
        SELECT    schemaName,tableName,MAX(LEVEL) maxLevel        
        FROM    ordered_tables        
        GROUP    BY schemaName,tableName
        ) mx
ON        ot.schemaName = mx.schemaName
AND        ot.tableName = mx.tableName
AND        mx.maxLevel = ot.LEVEL
ORDER BY LEVEL DESC; 
OPEN t_cur;
FETCH NEXT FROM t_cur INTO @schemaName,@tableName,@level;
WHILE @@FETCH_STATUS = 0
BEGIN
       DECLARE @ParmDefinition NVARCHAR(500)   ;
       DECLARE @vSQL           NVARCHAR(MAX)   ;
       DECLARE @vRowCount      INT             ;
       SET     @vSQL = 'DELETE FROM [' + @schemaName + '].[' + @tableName + '];';
       SET     @vSQL = @vSQL + 'SET @vRowCount_OUT = @@ROWCOUNT;'
       SET     @ParmDefinition = '@vRowCount_OUT INT OUTPUT';
       EXEC    sp_executesql @vSQL,@ParmDefinition,@vRowCount_OUT=@vRowCount OUTPUT;
       PRINT   CONVERT(NVARCHAR(100),@vRowCount) + ' rows deleted from [' +  @schemaName + '].[' + @tableName + ']';
       FETCH NEXT FROM t_cur INTO @schemaName,@tableName,@level;
END

CLOSE t_cur;
DEALLOCATE t_cur;

[T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

jamiet — Wed, 10 Aug 2011 15:04:00 GMT

I recently returned to a client at which I last worked back in 2006 and, as you might imagine, that gives me ample material for a blog post dedicated to Crap Code; conveniently the subject of the latest T-SQL Tuesday.

I first worked for this client back in 2004 and that is significant because back then we were writing code on SQL Server 2000. Ah, thems were the days!!! I smiled wryly to myself when I encountered the following code in a stored procedure that I once wrote:

--Simplified for convenience/demo
CREATE PROCEDURE [dbo].[usp_PersonInsert]
(
   @pXMLDataInsert     TEXT
)
AS
BEGIN
   DECLARE @vDoc            INT
   EXECUTE sp_xml_preparedocument @vDoc OUTPUT, @pXMLDataInsert
   SELECT
       ID, 
       Name
   INTO    #Insert
   FROM    OpenXML(@vDoc, 'NewDataSet/Table', 2 ) 
   WITH    (
       ID  INT     'ID',
       Name    VARCHAR(70) 'Name'
       )
   EXECUTE sp_xml_removedocument @vDoc
   INSERT INTO dbo.Person(ID,Name)SELECT ID,Name FROM #Insert
END

If you have never had the pleasure(!!!) of dealing with XML data prior to SQL Server 2005 then you're probably looking at this with a slightly confused look on your face wondering quite why we had to make this so complicated. TEXT datatype? OPENXML? System stored procs? What's in this @vDoc variable? On the other hand if you have had to deal with XML pre-2005 then right now you're probably nodding sagely to yourself and remembering the not-so-good-old-days!

No-one would ever write code like this today but back in 2004, before wonderous things like the XML datatype and table-valued-parameters, this was how one passed datasets into a stored procedure (well, its how we did it anyway). If any of my colleagues were to write this today they would probably be despatched home with a copy of Inside SQL Server 2008 : T-SQL Programming tucked under their arm and ordered not to come back until they could recite it ad infinitum but back in 2004, well, we thought we were pretty cool!

Times change, people change and coding practices change too. Code isn't born crap, it just tends to crapness the longer that it lives! Bear that in mind when coding today because in five years time you'll probably be coming back and having a little chuckle at it!

@Jamiet

On the typeof() campaign trail

jamiet — Wed, 10 Aug 2011 08:25:00 GMT

Given a table:

CREATE TABLE [person] ( [id] INT IDENTITY(1,1) , [name] VARCHAR(100) , [age] INT );

How nice would it be to be able to do something like this inside your stored procedures?:

DECLARE @person TABLE (
  [name] typeof([person].[name]) ,
  [age] typeof([person].[age])
);

Or, perhaps, declare something to be of type:

CREATE TYPE [dbo].[person] AS TABLE (
   [name]  typeof([person].[name]) ,
   [age]   typeof([person].[age])
);

Maybe you could even wrap it all up as:

CREATE PROC InsertPeople
   @person [dbo].[person] READONLY
AS
BEGIN
   INSERT  [person]([name],[age])
   SELECT  [name],[age]
   FROM    @person;
END

You know what to do.

That is all!

SQL_VARIANT_PROPERTY function

jamiet — Wed, 03 Aug 2011 19:50:00 GMT

I learnt something about T-SQL today that I never knew and I figure I’m not the only one so it might be worth sharing what I learnt; namely, I discovered the SQL_VARIANT_PROPERTY function.

It all started when going over a piece of code that I recently inherited, here is an abbreviated version of that code in order to demonstrate the problem:

DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8);
SET @dest = @src;
SELECT @dest;

If you execute that then the result is a truncated value:

This is not good. We have lost data and yet there is no indication that a problem has occurred – the truncation is silent. Happily if you are using database projects in Visual Studio and have turned on code analysis then you will get a warning about this that goes something along the lines of:

"SR0014 : Microsoft.Rules.Data : Data loss might occur when casting from VarChar(10) to VarChar(8)."

It may be the case of course that you are happy with the truncation in which case you are well advised to turn this into an explicit rather than implicit cast which you can do using the LEFT function:

DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8);
SET @dest = LEFT(@src,8);

This will get rid of the code analysis warning in Visual Studio and also indicate to anyone reading the code that the truncation is intentional. I still did not like this code though – I did not like the fact that we had to enter the length of 8 in two different places, it is not DRY. I would rather be able to say “truncate the value to the length of the @dest field” so that if anyone ever shortened the length of @dest we wouldn’t return to an implicit cast. That desire prompted me to raise a request on Connect: New function that returns the maximum possible length of a variable or column.

SQL Server Program Manager Umachander Jayachandran replied informing me that such a function already existed in the shape of

SQL_VARIANT_PROPERTY(expression,property)

which can return one of a series of properties about a scalar value. I adapted my code like so:

DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8) = ''; --note that we need to initialise @dest in order to use SQL_VARIANT_PROPERTY on it
SET @dest = LEFT(@src,CAST(SQL_VARIANT_PROPERTY(@dest,'maxlength') AS int));
SELECT @dest;

We are now truncating the value without having to specify the length which twice I happen to think is pretty cool (but maybe that’s just me).

In case you’re interested the complete list of properties that can be queried for using SQL_VARIANT_PROPERTY is:

BaseType
Precision
Scale
TotalBytes
Collation
MaxLength

In a way SQL_VARIANT_PROPERTY() is the closest we have in our archaic T-SQL world to .Net’s System.Type. System.Type is, as some of you may know, the basis for reflection and type inference in .Net development; let’s keep hoping that more type inference alike functionality comes to T-SQL in the not too distant future.

@jamiet

Debunking Kimball Effective Dates part 2 – Windowing Functions

jamiet — Wed, 13 Jul 2011 17:31:00 GMT

Reigniting the [SCDEndDate] debate

In November 2009 I wrote a blog post entitled Debunking Kimball Effective Dates where I stated that I was against the Kimball-recommended practice of maintaining a StartDate & EndDate value for every record in a type 2 slowly changing dimension (SCD) table (i.e. the values highlighted in blue in the following image):

The post was deliberately inflammatory so as to provoke a debate and it certainly got the desired response as it received 47 comments with a roughly 50-50 split between folks agreeing/disagreeing with me (which was especially interesting given I discussed the same technique back in 2005 but without the inflammatory tone and then received only 6 comments). I really don’t mind if people agree or not, it certainly made for a good debate in those 47 comments.

The crux of the post was thus: the column [SCDEndDate] is unnecessary because it can be derived from [SCDStartDate] of the preceding record. Eliminating that column is therefore beneficial because it can ease the pain of managing those values in an ETL process. Here’s some code that creates the table (without [SCDEndDate]), populates it, then derives [SCDEndDate] in a query:

           DROP TABLE [Customer];  --drop if exists      
           CREATE TABLE [Customer] (      
              [Id]                INT             PRIMARY KEY   IDENTITY(1,1)      
           ,  [NId]               NVARCHAR(50)      
           ,  [Name]              NVARCHAR(50)      
           ,  [Home]              NVARCHAR(50)      
           ,  [MaritalStatus]     CHAR(1)      
           ,  [Descendants]       INT      
           ,  [SCDStartDate]      DATE   --Note only [SCDStartDate], no [SCDEndDate]      
           );      
           CREATE UNIQUE INDEX Cust_idx ON [Customer]([NId],[SCDStartDate]) INCLUDE ([Id],[Name],[Home],[MaritalStatus],[Descendants]);      
           INSERT [Customer] ([NId],[Name],[Home],[MaritalStatus],[Descendants],[SCDStartDate])      
           VALUES ('Cust001','Henry','London','S',0,CONVERT(DATETIME,'20050324'))      
           ,      ('Cust001','Henry','London','M',0,CONVERT(DATETIME,'20070726'))      
           ,      ('Cust002','Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))      
           ,      ('Cust001','Henry','London','M',1,CONVERT(DATETIME,'20091127'));      
           SELECT c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate]      
           ,      COALESCE(MIN(c2.[ScdStartDate]),CONVERT(DATE,'99991231')) AS [SCDEndDate]      
           FROM   [Customer] c      
           LEFT   OUTER JOIN [Customer] c2       ON     c.[Nid] = c2.[Nid]      
           AND        c.[SCDStartDate] < c2.[SCDStartDate]      
           GROUP  BY c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate];

Go and run it now if you’re not au fait with what we’re talking about here! Here is the resultset:

Notice how the [SCDEndDate] for all records pertaining to “Henry” equal the [SCDStartDate] of the following record.

A new way in Denali

Amongst those 47 comments was the following:

Wow, that enormous group by does not look very appealing. Not only is there another layer of abstraction, but when columns get added I have to update a view, and the group by is gonna make it crawl.
-JaggedEdge

That’s a fair point. Not only is there a GROUP BY but if we look at the execution plan we can see that there are two index scans also:

We’re only accessing one table but still get two scans? Yeah, that doesn’t feel good does it? Happily a new feature is coming to SQL Server Denali that makes this easier and is included in yesterday’s CTP3 release; that feature is windowing functions. Below is the same query rewritten to use the new LEAD windowing function:

           SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
                   ISNULL( LEAD([SCDStartDate]) OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC),      
                           CAST('20501231' AS DATETIME)      
                       ) AS [SCDEndDate]      
           FROM    [Customer] c

and here is the resultset and execution plan:

We have the same results (just in a different order) with a different execution plan. There are more physical operations going on here but at least we have eliminated one of those scans. Having said that we have introduced a key lookup operator instead which is easily eliminated by including some columns in our index:

           CREATE UNIQUE INDEX Cust_idx ON [Customer]([NId],[SCDStartDate]) INCLUDE ([Id],[Name],[Home],[MaritalStatus],[Descendants]);

Run it again and our new execution plan is:

The key lookup operator has been eliminated and we have a nice method of deriving [SCDEndDate] without resorting to JOINs and multiple index scans.

Perf testing

Ok, we have eliminated that nasty looking additional Index Scan and Nested Loop Join but the real question is whether this has proved beneficial or not - for that we need a suitably large dataset with which to test. Here’s some code that inserts 140000 rows into our [Customer] table , each time modifying the [NId] column slightly. Its a crude dataset but for our purposes, it will suffice:

       DECLARE @i INT = 0      
       WHILE @i < 35000      
       BEGIN      
                   INSERT [Customer] ([NId],[Name],[Home],[MaritalStatus],[Descendants],[SCDStartDate])      
                   VALUES ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','S',0,CONVERT(DATETIME,'20050324'))      
                   ,      ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','M',0,CONVERT(DATETIME,'20070726'))      
                   ,      ('Cust002' + CAST(@i AS VARCHAR(6)),'Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))      
                   ,      ('Cust001' + CAST(@i AS VARCHAR(6)),'Henry','London','M',1,CONVERT(DATETIME,'20091127'));      
                   SET @i += 1;      
       END

I ran our two queries with STATISTICS IO ON & STATISTICS TIME ON, here are the results:

Method	Scan Count	Logical Reads	CPU time	Elapsed time
JOIN with GROUP BY	18	2902	1637	8232
LEAD	1	2129	718	1853

There are a few stats there but really the most important one is Elapsed time; as you can see the new LEAD method was orders of magnitude faster, it took less than a quarter as long as the old method that I outlined back in the previous blog post.

Conclusion

I still maintain that even pre-Denali you are better off not storing an [SCDEndDate] but now in Denali we have even less reason to do so because the new windowing functions are such a massive boost to performance in these sorts of scenarios. Get to know the new windowing functions well, something tells me you’ll be glad that you did!

@jamiet

Update

After reading Jeremiah Peschka’s post Leaving the Windows Open earlier today I realised that I could make the query containing the LEAD() function a little bit neater. Previously I was using ISNULL() to compensate for there being no match from LEAD like so:

       SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
               ISNULL( LEAD([SCDStartDate]) OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC),      
                       CAST('20501231' AS DATETIME)      
                   ) AS [SCDEndDate]      
       FROM    [Customer] c

Here’s the execution plan again for easy comparison (its different to the one above because this was for a much larger dataset):

Jeremiah’s post taught me that you don’t need ISNULL because LEAD has a similar ability built into it hence I can write the query a little more succinctly like so:

       SELECT  c.[Id],c.[NId],c.[Name],c.[Home],c.[MaritalStatus],c.[Descendants],c.[SCDStartDate],      
               LEAD([SCDStartDate], 1, CAST('20501231' AS DATETIME))      
                           OVER(PARTITION BY [NId] ORDER BY [SCDStartDate] ASC) AS [SCDEndDate]      
       FROM    [Customer] c

and the query plan:

OK, the query plan isn’t any different but I think we’ve got a much neater SQL query. And that makes me happy