SQL UNPIVOT on Multiple Columns

Some time ago I posted about using the UNPIVOT function in a stored procedure here.  Recently I came across an interesting problem that required further use of the UNPIVOT command.

Suppose you have a number of columns in a wide table that you would like to unpivot into a single column and multiple rows. An example might be when there are many measure columns and you would like to narrow the table by combining all the measure columns into a single column. This would be a fairly standard use of the UNPIVOT command that was covered in the earlier post.

Now suppose that there are two sets of measures that you would like kept as separate columns,  Instead of a single MeasureName and MeasureValue column, you would like 2 of each.  Or perhaps a simpler example might not be measure, but dimensional data.  Let's look at CRM system data:

CREATE TABLE Company

(Id INT,

CompanyName VARCHAR(500),

Contact1 VARCHAR(500),

Contact2 VARCHAR(500),

Email1 VARCHAR(500),

Email2 VARCHAR(500)

)

GO

INSERT INTO Company SELECT

1, 'FastCarsCo', 'Mr Purple', 'Mr Orange', 'purple@fcc.com', 'orange@fcc.com'

UNION ALL SELECT

2, 'TastyCakeShop', 'Mr Brown', 'Mr White', 'brown@tcs.com', 'orange@tcs.com'

UNION ALL SELECT

3, 'KidsToys', 'Mr Pink', 'Mr Black', 'pink@kt.com', 'black@kt.com'

UNION ALL SELECT

4, 'FruitStall', 'Mr Red', 'Mr Blue', 'red@fs.com', 'blue@fs.com'

GO

SELECT Id, CompanyName, contact1, contact2, email1, email2

FROM Company

GO

In this example we have a company table with two contacts for each company, and an email address for each contact.  Instead of two columns for contact names and two for email addresses, we would like to reduce this to one of each: CompanyName, ContactName, EmailAddress.  First let's try and reduce the contact1 and contact2 columns to one:

SELECT ID, CompanyName, ContactName,Contact

FROM

(

SELECT ID, CompanyName, Contact1, Contact2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2)

) pvt

We have now replaced the contact1 and contact2 fields with a single ContactName field.  I have added the new Contact column to indicate the source of the data.  This can be removed from the select list if it is not required.

Next we need to perform a similar unpivot on the email columns.  This is actually easier that you might think.  Rather than nesting queries, or complex CTEs, we need only append our first query with another unpivot statement:

SELECT Id,

CompanyName,

ContactName,

EmailAddress,

Contact

FROM

(

SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2 )

) pvt1

UNPIVOT

(

EmailAddress For Email IN (Email1, Email2 )

) pvt2

WHERE RIGHT(Contact,1) = RIGHT(Email,1)

The WHERE clause in the above query is very important.  Without it, we haven't told SQL Server how the second set of unpivoted data matches the first.  Without the WHERE , it will do a cross join, resulting in Mr Purple occurring twice; once with the correct email address, and once with the address of Mr Orange, his colleague.

In the WHERE clause we state that the new columns we have created, Contact (selected) and Email (not selected) should be joined on the last character. Remember that these two columns are populated with the names of the original fields, ie Contact1, Contact2, and Email1, Email2.  The two columns therefore align as Contact1 → Email1 (1=1) and Contact2 → Email2 (2=2).  Hence it is important that the names of the original columns in our source Company table contain text such that they can be joined together in the WHERE clause at the end of the query.

More details on the PIVOT / UNPIVOT command can be found here:

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

SQL Server Error Handling

Good error handling can be critical to writing robust code and ensuring predictable behavior should things go wrong. We always want to be certain that our database will be left in a clean and desirable state, which can be challenging if, say, a stored procedure errors part way through a series of inter-dependent steps.

As a basic starting point for basic handling of problem scenarios it's good practice to contain code within transactions:

BEGIN TRAN

--DO SOMETIHNG

COMMIT

If multiple steps are contained within the transaction, and any one should fail, then all the steps will be rolled back, leaving the database in the same state it was before the transaction began.

With SQL 2005, TRY..CATCH blocks were introduced. Using this feature we can greatly enhance the error handling. We can now “try” to do our data manipulation and should any step fail, we can “catch” the error and perform further "clean up" steps as a result.

A simple example might be to open a transaction in the TRY block, roll it back in the CATCH, else commit it:

BEGIN TRY

BEGIN TRAN

--DO SOMETHING

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

END CATCH

IF @@TRANCOUNT > 0

COMMIT

For more details on useful global variables such as @@TRANCOUNT see my post on SQL Server Global Variables.

The above code allows us to cleanly handle error scenarios and greatly improve the robustness of our code and therefore our database. However, the above example does not actually let us know any details about the error that has occurred. To do that, we need to capture the error information:

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

SELECT

ERROR_MESSAGE() AS ErrorMessage,

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_LINE() AS ErrorLine

END CATCH

In the above script I have forced a “divide by zero” error in the TRY block. When this error is thrown, the code will jump into the CATCH block and execute the select statement:

ErrorMessage ErrorNumber ErrorSeverity ErrorState ErrorLine

------------------------------------- ----------- ------------- ----------- -----------

Divide by zero error encountered. 8134 16 1 3

Note that the actual error has not been thrown, and the code has completed successfully without any problems. We are simply displaying the results of the various error functions.

We can make this even more powerful if we use the RAISERROR function. This will allow us to actually throw an error message back to the calling application. The advantage this gives us is that we can capture the error from the TRY, perform remedial action in the CATCH, and then let the calling application know that there was an error we had to clean up:

DECLARE @ErrorMsg VARCHAR(500)

BEGIN TRY

BEGIN TRAN

SELECT 10/0

END TRY

BEGIN CATCH

SELECT @ErrorMsg = 'Error: ' + ERROR_MESSAGE()

+CHAR(13) +'Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(100))

+CHAR(13) +'State: ' + CAST(ERROR_STATE() AS VARCHAR(100))

+CHAR(13) +'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(100))

+CHAR(13) +'Line: ' + CAST(ERROR_LINE() AS VARCHAR(100))

IF @@TRANCOUNT > 0

ROLLBACK

RAISERROR(@ErrorMsg, 16,10)

END CATCH

IF @@TRANCOUNT > 0

COMMIT

-----------

(0 row(s) affected)

Msg 50000, Level 16, State 10, Line 21

Error: Divide by zero error encountered.

Severity: 16

State: 1

Number: 8134

Line: 5

The result is that the error is captured and handled - the transaction is rolled back. Using the RAISERROR function, however, we have build our own custom error message to return to the calling application. In the example above, this message lets us know even the exact line that caused the error. This message can be enriched to include as much information as you wish, which can greatly ease debugging of the problem.

(It's worth noting that the severity of the error I have set to 16. Different severity numbers can have different effects in different places, and typically 11-18 are the range for user defined errors. See the MSDN link at the end of this article for more details.)

Finally, RAISERROR can be used to throw msg_id's, and this is the more standard SQL behaviour. The list of standard error messages that can be thrown are contained in the sys.messages

table. You can insert your own (with an id >50000) and then use RAISERROR to throw these when required.

Error handling can be used to powerful effect using the techniques described. If you have any techniques you use or ideas on how to improve on the methods described here, please feel free to share them.

MSDN - RAISERROR:

http://msdn.microsoft.com/en-us/library/ms178592.aspx

CheckSum, HashBytes and Slowly Changing Dimensions

A recent requirement for a DW was to implement a Type 2 Slowly Changing Dimension across all attributes in the dimension.  Unfortunately, the dimension had many, many attributes (30+), which meant that a comparison of all attribute values would be needed for each new record that arrived.

To recap:

Type 2 Slowly Changing Dimension (SCD2)
SCDs are methodologies for tracking historical changes to a data row in a dimension table over time. While SCD1 is just an overwrite when there is a change (no history), SCD2 involves entering a new record into the table with the new changed data.  A new surrogate key is generated for the new record, while maintaining the natural key to allow the new record to be linked to the old one.  Typically, there would also be either an IsCurrent flag, to identify which is the current version of the data, and/or Start and End Date columns to indicate the order and time span of the changes:

Surrogate_Key	Natural_Key	CustName	Country	Start_Date	End_Date
100	375	Guy	Scotland	01-Jan-2002	31-Jul-2005
101	375	Guy	England	01-Aug-2005	31-Dec-9999

In the above example the Country has changed and so a new record with the new country and new surrogate key has been inserted.  As each new row arrives, it's country is checked against the existing records country to see if it has changed or not.

This is straightforward if there is only the one attribute being tracked.  A simple string comparison will quickly identify the change.  However, if there are many attributes being compared then the query doing this comparison can become quite long and perform poorly,  particularly if you have large numbers of records arriving.

In order to solve this problem it was decided to hashcode the relevant (tracked) columns together and save the output to a new column.  If we hashcode the incoming records on the same columns as well, then all we have to do is compare the two hash columns to see if there the record has changed. 

There are a number of ways to do this:

CHECKSUM()

From BOL:

 CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator...... If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

In fact a quick test using CHECKSUM() to hashcode 100,000 GUIDs resulted in up to 3 repeated hashcodes ( "collisions").  The CHECKSUM is very fast, but this rate of collision is not tolerable in this scenario.

HASHBYTES()

HASHBYTES() performs a similar function but the rate of collision is much much lower. It's not zero, but to date I have never been able to generate a collision.
To use this function, an algorithm needs to be specified. Thomas Kesjer has done a detailed comparison of the various algorithms' performance, and produced this graph:

The performance of the HASHBYTES algorithms are largely similar (poorer than CHECKSUM), with the exception of MD2, which is much slower.  Typically I use MD5.  The other point to note is that HASHBYTES requires a single string parameter to be passed, not multiple columns eg:

SELECT HASHBYTES('MD5', Country) FROM DummyTable

In order to hashcode multiple columns together we can either concatenate them together as strings, or an even neater way would be to convert it to an XML string using FOR XML:

SELECT HASHBYTES('MD5', (SELECT CustName, Country FOR XML RAW))

Using this technique we can hashcode all the existing records in the table, and as each new record arrives we can look at its hashcode and compare it (joining on the Natural Key).  If the code is different then the record has changed, and is inserted. If not, it is ignored.

This should be much simpler to maintain and perform much better than doing individual comparisons of the all the changing fields.

SQL to Drop All Non-System Objects From Master DB

We've all been there - well I have anyway. You've just created your script to generate all the required objects for your database.  You run the script in the new environment.  But wait, you forgot to set the database!  All the objects have been created in the master db instead!

The number of times this has happened to me and to others I have worked with is infuriating.  I've sat there watching people spend ages deleting one by one all the db objects using Management Studio, stumbling when there are dependencies, and generally wasting a lot if time.

To solve this problem I have put together a short script to generate all the required drop statements. Fortunately, the sys.objects table contains a handy "is_ms_shipped" column that makes life much simpler:

SELECT

'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+Name+']''))'+ CHAR(10)+ 'DROP '

+ CASE WHEN  type = 'U' THEN 'TABLE '

       WHEN  type = 'P' THEN 'PROCEDURE '

       WHEN  type ='FN' THEN 'FUNCTION '

       WHEN  type = 'V' THEN 'VIEW '

  END

+ Name + CHAR(10) + 'GO' + CHAR(10)

FROM  Master.sys.objects

WHERE is_ms_shipped <> 1

AND TYPE IN ('U','P','FN','V')

ORDER BY

CASE  WHEN type = 'P'  THEN 1

      WHEN type = 'V'  THEN 2

      WHEN type = 'FN' THEN 3

      WHEN type = 'U'  THEN 4

END

This script will generate all the DROP statements for the user-defined objects in the master db.  These need to be copied into a new window (easiest if you have been outputted the results to text rather than to grid in SSMS) and executed.

If there are any foreign key constraints, however, the above script will generate an error.  To handle this either execute all the drop statements over and over until the errors cease (each subsequent execution of the drop statements will delete additional tables as each foreign key table is dropped), or, prior to executing the above script, execute the following:

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name

+ CHAR(10) + 'GO' +CHAR(10)

FROM sys.foreign_keys fk

WHERE is_ms_shipped = 0

This will generate the necessary statements that need to be run in order to drop all the constraints. Copy the output to a new query window and execute.

SQL Server Parameter Sniffing - Slow Running Queries

Parameter sniffing is a problem that can occasionally creep into code, just when everything seems fine. A stored procedure that yesterday ran nice and fast, today is taking much longer. The next day it is fast again. Nothing has changed on the server / db. It can be difficult to track down why.

The answer could be Parameter Sniffing.

When a stored procedure is compiled, it compiles using ( or "sniffing") the parameter values set ​​at the time of the invocation. It uses those parameter values ​​to determine the optimal execution plan for the proc. Simply put, if you later call the proc with a different parameter value, it will still use the execution plan determined by the first set of values. It will do this regardless of whether a better execution plan would have been more appropriate for the new value.

For example, if I have a table containing all the values ​​from 1 to 100, and then 100 rows with a value 999:

/ * Create table * /

CREATE TABLE DummyData ( col1 INT ​​)

DECLARE @Counter INT

/ * Insert data 1-100 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( @Counter )

SET @Counter = @Counter + 1

END

/ * Insert 100 data rows with value 999 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( 999 )

SET @Counter = @Counter + 1

END

Next create a procedure to select values from this table:

CREATE PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

END

Now generate the Execution Plan for this procedure, passing a parameter of 999, and look at the "estimated number of rows":

EXEC SelectDummyData @DummyValue = 999

The row count is 100, which is what we would expect. But re-run the execute statement, this time with @DummyValue = 5, and the Estimated Number of Rows is still 100, when we would expect it to be 1.

The reason it is still expecting 100 rows is because the execution plan was determined when the proc was first compiled, when we used a value of 999.  It is still using the same execution plan, and therefore the same number of rows is expected, regardless of the input value supplied.

The impact of this oddity in this example is negligible, but for a complex query it can result in significantly slower performance.

If your query is complex it might be easier to generate the execution plan in XML ( SET SHOWPLAN_XML ON ) and then search the XML for something like "ParameterCompiledValue =".

To avoid parameter sniffing issues from occurring there are a number of options that can be considered:

1. Recompile the stored proc every time it is executed:

EXEC SelectDummyData @DummyValue =  1
WITH RECOMPILE 

This will ensure that the proc uses the supplied  parameters to build the best execution plan every time, but the overhead is the constant recompilation. This may be acceptable if there is sufficient gain in query performance.

Generating the execution plan on the above script, using various parameters, shows the correct number of rows being estimated each time.

If the parameter sniffing  relates to a set of queries in particular, then  recompilation can be specified individually for queries:

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( RECOMPILE )

2. Optimize the query/ies for a specific parameter value:

ALTER PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( optimize FOR ( @DummyValue = 1 ))

END

With this option we can force the query plan to be based on the same known value every time it is compiled (eg During maintenance tasks). This option may work if we know the optimal value for our system. The downside, however, is that it may still perform poorly for other values, but that may well be tolerable.

3. Avoid sniffing altogether, by using a local variable inside the proc:

ALTER PROC SelectDummyData ( @DummyValue INT )
AS BEGIN DECLARE @LocalDummyValue INT SET @LocalDummyValue = @DummyValue
SELECT * FROM DummyData WHERE col1 = @LocalDummyValue
END 

Here there is no possibility of parameter sniffing, and the execution plan will be based on the statistics. Note, however, that the plan will be built using statistic densities instead of statistic histograms, which are less accurate. Therefore it is not guaranteed to be the best plan for all possible values.

Conclusion
Parameter sniffing has the potential to cause queries to run with widely varying performance. In order to mitigate this problem consider modifying the code based on the options presented above. Hopefully this will help keep the queries and stored procedures performing well wherever they are used.

More useful info on this topic is available here:

Degremont Michel

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Turgay Sahtiyan
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx