CREATE OR ALTER

One of the fundamentals that we learnt when learning SQL, that we use CREATE to create a database object, and ALTER if we needed to change its structure. It was simple and straightforward.

But it was too much of a problem writing a deployment script, where you had to check if the object existed, and then create or alter it based on the result. Or you could drop and re-create the object but that would make you lose all the permissions that have been set on the object. In short it tended to get a little messy. Or of course you could try a clever idea like this one. Putting it in as dynamic SQL was a clever alternative too,  but then you had difficult-to-read and difficult-to-maintain scripts.

SQL Server 2016’s Service Pack 1 now gives us the CREATE [OR ALTER] statement, with which you could create a database object or modify in just one go. It’s a feature been asked for, for quite a long time, and it’s finally here.

You can use CREATE OR ALTER on the following types of objects: Stored procedures, Functions, Views and Triggers. It would be nice to have this on tables too, but then we have the whole issue of tables being populated, and the effect that it would have on dependent tables would be all too complicated.

Here is a piece of code comparing usage:

THEN:

IF (SELECT OBJECT_ID(‘TestProcedure’)) IS NULL
EXECUTE(‘CREATE PROCEDURE TestProcedure AS PRINT ”Test”’)
GO

ALTER PROCEDURE TestProcedure
AS
BEGIN

PRINT ‘Here”s the real code I wanted to write’
END

NOW:

CREATE OR ALTER PROCEDURE TestProcedure
AS
BEGIN
PRINT ‘Here”s the real code I wanted to write’
END

 

Comparing Results using T-SQL

Usually, in data migration or ETL projects there is a need for comparing source data with that of the destination once the data is loaded. The challenge is in comparing each field from the source with its counterpart on the destination, for each record – and you can have a whole lot of fields.

The query that you would write to do the comparison, would be a large tangle of comparison operators – even for tables of a few columns, increasing in complexity as the number of columns grow.

A simpler solution would be to first convert the source and the destination into tables of just three fields: The Key field, the Field field and the Value field. (In case you have a multi-column key, you would obviously have more than three fields – but the principle is the same)

Consider the following source:

Table 1: Source

Seq_code	Product_Name	Color	Unit_Price	Category_Name	Weight
C01	Crystal parrot	None	130.00	ORNAMENT	.75
C02	Silver pirate	Silver	35.00	ORNAMENT	1
C03	Pearl necklace	White	220.00	JEWELRY	.35
C05	Ceramic dog	Blue	15.00	ORNAMENT	.35

and then consider the following destination (assuming that data is already loaded through some sort of ETL process):

Table 2: Destination

Code	Name	Color	Price	Category	Weight
C01	Crystal parrot	None	130.00	Ornament	75
C02	Silver pirate	Silver	35.00	Ornament	1
C03	Pearl necklace	White	220.00	Jewelry	35
C05	Ceramic dog	Blue	15.00	Ornament	35

The Need

You need to compare these two, in order to pull out any discrepancies.

The Plan

You first mold each of these (i.e. the source and destination) to look like this:

Table 3: Pivoted Destination

Key	Field	Value
C01	Name	Crystal parrot
C01	Color	None
C01	Price	130.00
C01	Category	Ornament
C01	Weight	75
C02	Name	Silver pirate
…	…	…
C05	Weight	35

Here, you have the Key field (Code) of each record repeated for each field of that record along with the name of the field and the value of the field.

Before you do the molding you need to ensure that the field names in the original tables are the same. I.e. Seq_code in the source and Code in the destination should be changed to have the same name: Code, maybe.

Hence, the molded source should look like this:

Table 4: Pivoted Source

Key	Field	Value
C01	Name	Crystal parrot
C01	Color	None
C01	Price	130.00
C01	Category	ORNAMENT
C01	Weight	.75
C02	Name	Silver pirate
…	…	…
C05	Weight	.35

You now do the comparison using INNER JOIN on the Key + Field columns, and a WHERE to filter out the values that do not match. The molding can be done using UNPIVOT, so that there is no need to create any intermediate tables.

The Code

WITH
-- Pivot the source data
SourcePivot
AS
(
	SELECT	[Code],
			[Field],
			[Value]
	FROM	(
				SELECT	[Code] =		[Seq_Code],
						[Name] =		[Product_Name],
						[Color],
						[Price] =		CAST([Unit_Price] AS VARCHAR(20)),	-- Data type conversion since all
						[Category] =	[Category_Name],					-- fields have to be of the same
						[Weight] =		CAST([Weight] AS VARCHAR(20))		-- data type.
				FROM	Source
			) AS Source
			UNPIVOT
			(
				[Value] FOR [Field] IN ([Name], [Color], [Price], [Category], [Weight])
			) AS UnPvt
),

-- Pivot the destination data
DestinationPivot
AS
(
	SELECT	[Code],
			[Field],
			[Value]
	FROM	(
				SELECT	[Code],
						[Name],
						[Color],
						[Price] =	CAST([Price] AS VARCHAR(20)),
						[Category],
						[Weight] =	CAST(CAST([Weight] AS DECIMAL(8, 2)) AS VARCHAR(20))
				FROM	Destination
			) AS Destination
			UNPIVOT
			(
				[Value] FOR [Field] IN ([Name], [Color], [Price], [Category], [Weight])
			) AS UnPvt
)

-- Query for discrepancies
SELECT	SP.[Code],
		SP.[Field],
		SP.[Value] AS [SourceValue],
		DP.[Value] AS [DestinationValue]
FROM	SourcePivot SP
		INNER JOIN DestinationPivot DP
			ON SP.[Code] = DP.[Code]
			AND SP.[Field] = DP.[Field]
WHERE	SP.[Value] <> DP.[Value] COLLATE SQL_Latin1_General_CP1_CS_AS -- Use a case-sensitive collation on case insensitive
																	  -- tables/datbase if case difference is a concern.

The Result

Table 5: Comparison Result

Code	Field	SourceValue	DestinationValue
C01	Category	ORNAMENT	Ornament
C01	Weight	0.75	75
C02	Category	ORNAMENT	Ornament
C03	Category	JEWELRY	Jewelry
C03	Weight	0.35	35
C05	Category	ORNAMENT	Ornament
C05	Weight	0.35	35

<<Download sample code>>

The Length of an Expression

We all take things for granted. And one such thing are functions that we are familiar with. When we use a function for the first time, we use it in a certain way and it works. It works a second and third time, and it continues to work – we take it for granted. We expect it to work like it always has. Until one fine day – 10 years later, you find that it does not work the way you thought it would – but you still suspect that something else is wrong. You spend too much time trying to figure it out until you finally realize that it’s your dear old friend who’s gone wonky. It happens to all of us – or at least to those of us who don’t read documentation in its entirety.

I was just working on some T-SQL that required simple string manipulations. You know, the usual data from text file to staging table, pull out the field and throw in some functions to separate first and last names. I was using the LEN function to get the length of the string so that I can use it to pull out the last name. Something that goes like this:

 1: RIGHT([EmployeeName], LEN([EmployeeName]) - CHARINDEX(' ', [EmployeeName]))

A simple employee name field, that separates first and last name with a space. This would work alright, except when there are trailing spaces. This I did not know (I’m ashamed to say). That was because I have always used LEN believing that it will return the length of the entire string, which it does but ignoring trailing spaces.

So how would you work around this? I can think up of two ways:

1. Trim your string first:

    1: RIGHT(RTRIM([EmployeeName]), LEN(RTRIM([EmployeeName])) - CHARINDEX(' ', RTRIM([EmployeeName])))

   You would get longer codes, especially if it is more complex than my mere mortal example.

2. or use the DATALENGTH function:

    1: RIGHT([EmployeeName], DATALENGTH([EmployeeName]) - CHARINDEX(' ', [EmployeeName]))

   DATALENGTH counts the number of bytes that your string contains (including that of your trailing spaces). But, you need to be forewarned that if it’s an Unicode expression that you are dealing with it would show double the length, since Unicode uses up 2 bytes per character.

So there you go, a post that was not supposed to be technical, but philosophical – Don’t take things for granted.

Case-sensitive Querying in SQL Server

Can you filter/sort/manipulate SQL Server data in a case sensitive manner? The answer is of course, yes. But first, let me brief you as to why this is a question at all.

SQL Server, like most database systems have a collation setting, which by default is case insensitive. Most of us are quite used to SQL Server with this setting (for some of us subconsciously it has been set in our heads that SQL Server is case insensitive). True to that, most applications that we develop usually also do not demand case sensitivity. The dilemma occurs when suddenly there’s a requirement to perform a case sensitive filter on a table, or perhaps retrieve unique records taking case sensitivity into consideration.

Splitting Delimited Strings

Splitting delimited strings is a common requirement when writing T-SQL. There are so many instances when a comma separated list of product codes are required to be sent to the database for various types of processing. Once sent to the database the delimited string will have to be split and supplied as a parameter to a query or something like that.

With the introduction of SQL Server 2008, one would have expected the usage of splitting strings to have reduced, what with the introduction of table valued parameters. However, there are a lot of people who don’t want their client-apps changed. Or, they do not want to switch to table-valued parameters due to backward compatibility with older versions of SQL Server. So, here’s how you create your own function to split strings with a delimiter.

 1 CREATE FUNCTION [dbo].[SplitString]
 2 (
 3     @StringToSplit    VARCHAR(8000)= '',
 4     @Delimiter        CHAR(1)= ''
 5 )
 6  RETURNS @ReturnTable TABLE ([Value] VARCHAR(8000) NULL)
 7  AS
 8  BEGIN
 9 
10     -- Return empty table if no string to split is supplied
11      IF ISNULL(LTRIM(RTRIM(@StringToSplit)), '') = ''
12     RETURN
13 
14     -- Return table populated with complete string to split if no delimeter is supplied
15      IF ISNULL(LTRIM(RTRIM(@Delimiter)), '') = ''
16     BEGIN
17 
18         INSERT INTO @ReturnTable
19         SELECT @StringToSplit
20 
21         RETURN
22     END
23 
24     -- Iterate through string to split using delimiter, then return populated table
25      DECLARE @Snippet varchar(8000)
26 
27     WHILE CHARINDEX(@Delimiter, @StringToSplit) <> 0
28     BEGIN
29 
30         SET @Snippet = LEFT(@StringToSplit, CHARINDEX(@Delimiter, @StringToSplit))
31         SET @StringToSplit = RIGHT(@StringToSplit, LEN(@StringToSplit) - LEN(@Snippet))
32 
33         INSERT INTO @ReturnTable
34         SELECT REPLACE(@Snippet, @Delimiter, '')
35     END
36 
37     -- Insert the left over piece of the string to split into the table    
38      IF @StringToSplit <> ''
39         INSERT INTO @ReturnTable
40         SELECT REPLACE(@StringToSplit, @Delimiter, '')
41 
42     RETURN
43  END
44  GO

And this is how you could use the function in your queries:

• Like this:

1 SELECT *
2  FROM dbo.SplitString('P0032,P8763,P9093', ',')

• Or like this:

1 SELECT *
2 FROM dbo.Products
3 WHERE [ProductCode] IN (SELECT [Value]
4 FROM dbo.SplitString('P0032,P8763,P8872', ','))

• Or maybe like this:

1 SELECT P.*
2 FROM dbo.Products P
3 INNER JOIN dbo.SplitString('P0032,P8763,P9093,P_INVALID', ',') S
4 ON P.[ProductCode] = S.[Value]

These would return result sets such as these:

Result 1	Result 2	Result 3
Value P0032 P8763 P9093	ProductCode Name Price P0032 Milk 0.50 P8763 Butter 1.10 P8872 Ham 2.75	ProductCode Name Price P0032 Milk 0.50 P8763 Butter 1.10 P9093 Eggs 0.10