Chris Johnson

This is a little quirk of SQL Server that I ran into a while ago.

We’ve probably all seen or used an INSERT... INTO script before, but just in case:

An INSERT... INTO script typically looks something like

SELECT
      *
INTO #Test1
FROM dbo.Test1;

And it will create a new table (#Test1) from the data selected in the SELECT statement, with the column definitions being pulled from the columns in the SELECT in the same way that the engine defines data types etc. for a view.

But what happens when one of the columns being selected is an identity column. To answer that, let’s run a quick test:

DROP TABLE IF EXISTS #Test1;
DROP TABLE IF EXISTS dbo.Test2;
DROP TABLE IF EXISTS dbo.Test3;

CREATE TABLE #Test1
    (
          Test1ID INT IDENTITY(1, 1) NOT NULL
        , SomeData VARCHAR(50) NOT NULL
    );

INSERT INTO #Test1
    (
          SomeData
    )
VALUES
      ('Some data')
    , ('Some more data')
    , ('Even more data')
    , ('And a bit more data');

SELECT
      *
INTO dbo.Test2
FROM #Test1;

SELECT
      Test1ID
INTO dbo.Test3
FROM #Test1;

What we’re doing here is creating a temp table (#Test1) with an identity column, then running a couple of SELECT... INTO statements that include that identity column to create new permanent tables in the dbo schema (Test2 and Test3). The reason I’m creating permanent tables is it make it easier for us to look at their definitions, which are:

CREATE TABLE [dbo].[Test2](
	[Test1ID] [int] IDENTITY(1,1) NOT NULL,
	[SomeData] [varchar](50) NOT NULL
);

CREATE TABLE [dbo].[Test3](
	[Test1ID] [int] IDENTITY(1,1) NOT NULL
);

So, we can see in this case the SELECT... INTO has created the Test1ID column as an IDENTITY column, the same as the source. The next question is what happens when the IDENTITY column exists at a different grain to the results?

DROP TABLE IF EXISTS #Test1;
DROP TABLE IF EXISTS #Test2;
DROP TABLE IF EXISTS dbo.Test3;

CREATE TABLE #Test1
    (
          Test1ID INT IDENTITY(1, 1) NOT NULL
        , SomeData VARCHAR(50) NOT NULL
    );

CREATE TABLE #Test2
    (
          Test2ID INT IDENTITY(1, 1) NOT NULL
        , Test1ID INT NOT NULL
        , SomeOtherData VARCHAR(50) NOT NULL
    );

SET IDENTITY_INSERT #Test1 ON;

INSERT INTO #Test1
    (
          Test1ID
        , SomeData
    )
VALUES
      (1, 'Some data')
    , (2, 'Some more data')
    , (3, 'Even more data')
    , (4, 'And a bit more data');

SET IDENTITY_INSERT #Test1 OFF;

INSERT INTO #Test2
    (
          Test1ID
        , SomeOtherData
    )
VALUES
      (1, 'DataData')
    , (1, 'DataDtaData')
    , (2, 'DataDataDataData')
    , (2, 'Too much Data')
    , (3, 'Not enough data')
    , (4, 'I also like data')
    , (4, 'Feed me data')
    , (4, 'I hunger for data');

SELECT
      T1.Test1ID
    , T1.SomeData
    , T2.Test2ID
    , T2.SomeOtherData
INTO dbo.Test3
FROM #Test1 AS T1
INNER JOIN #Test2 AS T2
    ON T1.Test1ID = T2.Test1ID;

So in this case we have 2 tables joined, both have IDENTITY columns that essentially make up the primary key, but the result is:

CREATE TABLE [dbo].[Test3](
	[Test1ID] [int] NOT NULL,
	[SomeData] [varchar](50) NOT NULL,
	[Test2ID] [int] NOT NULL,
	[SomeOtherData] [varchar](50) NOT NULL
);

So no IDENTITY column in the newly created table. In this case the engine can’t see which of these can be IDENTITY columns. But what if we feed it a bit more information.

DROP TABLE IF EXISTS dbo.Test2;
DROP TABLE IF EXISTS dbo.Test1;
DROP TABLE IF EXISTS dbo.Test3;
DROP TABLE IF EXISTS dbo.Test4;

CREATE TABLE dbo.Test1
    (
          Test1ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
        , SomeData VARCHAR(50) NOT NULL
    );

CREATE TABLE dbo.Test2
    (
          Test2ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
        , Test1ID INT NOT NULL
        , SomeOtherData VARCHAR(50) NOT NULL
        , CONSTRAINT FK_Test2_Test1ID
            FOREIGN KEY (Test1ID)
            REFERENCES dbo.Test1(Test1ID)
    );

SET IDENTITY_INSERT dbo.Test1 ON;

INSERT INTO dbo.Test1
    (
          Test1ID
        , SomeData
    )
VALUES
      (1, 'Some data')
    , (2, 'Some more data')
    , (3, 'Even more data')
    , (4, 'And a bit more data');

SET IDENTITY_INSERT dbo.Test1 OFF;

INSERT INTO dbo.Test2
    (
          Test1ID
        , SomeOtherData
    )
VALUES
      (1, 'DataData')
    , (1, 'DataDtaData')
    , (2, 'DataDataDataData')
    , (2, 'Too much Data')
    , (3, 'Not enough data')
    , (4, 'I also like data')
    , (4, 'Feed me data')
    , (4, 'I hunger for data')

SELECT
      T1.Test1ID
    , T1.SomeData
    , T2.Test1ID AS FKTest1ID
    , T2.Test2ID
    , T2.SomeOtherData
INTO dbo.Test3
FROM dbo.Test2 AS T2
LEFT JOIN dbo.Test1 AS T1
    ON T1.Test1ID = T2.Test1ID;

SELECT
      Test2ID
    , SomeOtherData
INTO dbo.Test4
FROM dbo.Test2;

So here I’ve made it explicit that Test2ID is the primary key of dbo.Test2, added in a foreign key to dbo.Test1 to also make it explicit that the join won’t be introducing any duplicate rows, and even turned the join into a LEFT JOIN to make explicit to the engine that we’re returning every row from dbo.Test2, but the answer is still:

CREATE TABLE [dbo].[Test3](
	[Test1ID] [int] NOT NULL,
	[SomeData] [varchar](50) NOT NULL,
	[Test2ID] [int] NOT NULL,
	[SomeOtherData] [varchar](50) NOT NULL
);

CREATE TABLE [dbo].[Test4](
	[Test2ID] [int] IDENTITY(1,1) NOT NULL,
	[SomeOtherData] [varchar](50) NOT NULL
);

So we can see from dbo.Test4 that we can get Test2ID to be recognised as an IDENTITY column but only if we don’t join to anything else.

This is mainly a bit of fun for me, going down a bit of a rabbit hole to see what’s at the end, but it does have some real world implications. If you’re using SELECT... INTO anywhere in your scripts then you do need to be aware of when it will and won’t create IDENTITY columns and account for that in future interactions with the created table. I became aware of this because I was editing an existing script and needed to add new data to a table created in this way, and I got errors because I was trying to insert into an IDENTITY column, so there are problems it can cause. You also need to be aware that if you’ve accounted for the IDENTITY column in the created table, but subsequently add a join into your SELECT, that column will no longer be created as an IDENTITY and you may need to do some editing downstream.

In general I don’t particularly like SELECT... INTO, I feel that I lose some control as a developer when I use it rather than explicitly creating the table, and I lose some of the self-documenting nature of the code, and this just gives me another reason to avoid it. But I also know that at a minimum I will end up debugging someone else’s code that uses it in the future, so it’s a good idea to know about these quirks.

Just a quick post today, to highlight one of the quirks of SSDT database projects.

Usually when you hit publish, or publish through your automated pipeline, or generate a script, or do whatever else you do to push the project out onto a server the process is pretty simple. There’s some sort of comparison engine that runs under the hood, and it does some technical wizardry to detect any differences between your project and the database you’re deploying to. It generates a script based on those changes, and runs that script against the database to bring it in line with the project. Simple enough, right?

Mostly, yes. But there are some cases where it isn’t that simple. I’ve blogged before about how you need to write any check constraints in a specific way, and today I’m going to look at another of these quirks.

The scenario

Recently in my job we were looking at tidying up the cases of some description fields in our dimensions. One of the procedures that generates the dimension data was generating a description field as ‘YES’ and ‘NO’, and we felt that ‘Yes’ and ‘No’ were better options. This was all being generated through a case statement (CASE WHEN Column = 1 THEN 'YES' WHEN Column = 0 THEN 'NO' END) so we thought it would be an easy fix to just change the YES to a Yes etc. We were wrong.

When we came to deploy, we found that the changes weren’t deploying to any of our environments. As they were part of a larger change set, we were really puzzled about why it was just these procedures that weren’t deploying, until we realised that it was because the only difference between the project version and the database version of these procedures was the case of the labels. I did some looking into it, and it turns out this is expected behaviour. I had a look around the publish options, and couldn’t find any ‘detect changes in case’ type option, and the internet suggested the only way to make the publish process work the way we wanted was to update the collation of the database to be case sensitive.

This was definitely a pain, and the answer in the end was to add a comment to the procedures to trick the comparison engine into thinking they’d changed, but the more I thought about it the more it made sense. By setting the database to be case insensitive, we had told SQL Server that we didn’t care about the case of anything in that database. Why, in that case, would the comparison engine waste time altering a proc when the only difference was something we had called out as unimportant?

More generally, I think this speaks to a principal of doing the right thing in the right place. If your database is case insensitive then don’t try to do case sensitive stuff in there. Instead, worry about the case in whatever layer you have that does care about the case of your labels. And if that layer is your database then make it explicit by making the database case sensitive.

This is my 9th TSQLTuesday, and this month Andy Leonard has given the prompt “How Do You Respond When Technology Changes Underneath You?”

I’ve largely worked at places where we’ve used pretty stable products. Despite my best efforts, most of the teams I’ve been part of have been reluctant to adopt much in the way of cutting edge technologies. There are things I could write about like the change in SSIS from 2008 to 2012, and the associated relearning I had to do to keep myself current, but I think I want to talk about something a little different:

SQL Server version upgrades

I’m not 100% sure this is what Andy had in mind with his prompt, but it feels on topic enough that I’m going to go with it anyway.

SQL Server version upgrades are something most developers and DBAs will have to confront at one point or another, and I’ve found myself dealing with a few significant ones in the last few years. All have been from 2008R2, including the one my company is currently in the middle of (I wasn’t kidding when I said they don’t tend to adopt the most cutting edge technologies), and they’ve all gone a similar way. I’m not going into all of the details (one blog post is way too short to write a complete how to on this), but there are a few things I want to share about my experiences.

Data Migration Assistant

The SQL Server Data Migration Assistant is an amazing tool. You download it, point it at a SQL instance, tell it what database(s) you want to upgrade, and tell it the target version. It then tells you a lot of what you need to know in terms of incompatible code, code that might behave differently, code that might perform differently, features marked for deprecation etc. Some of these things are easy fixes, for instance using the no longer valid syntax for calling RAISERROR. Others require a bit more work, like any old style outer joins using the *= syntax, in this case you will need to re-write the statement but it should be pretty obvious how you need to do that. Still others may need some more involved work, for instance one of our upgrades was using the old version of database mail. The fix for that meant setting up and configuring the target to use the up to date version, and changing every proc to call the new system procedures instead of the old.

As you might be gathering, there can be quite a bit of work involved even with the Data Migration Assistant’s help. But that’s only half the story.

Things the Assistant can’t help with

There are some design patterns you might adopt that make these upgrades harder, because they put code in places the migration assistant can’t see. The obvious example here is dynamic SQL, the migration assistant can’t see the code you will eventually generate because it’s not held directly in any object, and therefore can’t check what obsolete syntax might be lurking.

Similar principals apply to any code held in tables, or held in SSIS packages, or generated in scripts in SSIS packages, or run from the application side, and so on. Basically, any T-SQL code you put anywhere other than directly in a database object will cause you some issues here.

Obviously the first bit of advice I’m going to give you is “don’t do that”. If you keep your code inside your database, limit calls from external sources like SSIS or applications to just executing stored procedures, and resist the urge to write any dynamic SQL, you should be fine. But these things do exist for a reason, and can be useful, and as I’ve written about before, I am actually a fan of dynamic SQL when it’s used properly.

If you do choose to use some of these design patterns, you will do yourself a big favour by keeping the numbers to a minimum, and heavily documenting them somewhere. That way, when you come to upgrade you should have a ready list of trouble spots in your code base, and if you’ve kept them to a manageable number you should be able to check their upgrade compatibility fairly simply.

One way to perform those checks, that we are currently experimenting with, is to just generate stored procedures from those dynamic scripts. Run through the different possible paths (which you should have documented), generate a script for each of them, and wrap them in CREATE PROCEDURE statements. Once you’ve done that, the migration assistant should pick them up the same as anything else. Another option is just to test them thoroughly in your migration test environment. You should ideally have some re-runnable unit and end-to-end tests for this functionality, so do your test migration and run them.

As I said at the start, there’s a lot more to write about SQL Server version upgrades but this post is probably long enough already so I’m leaving it there. Thanks for the prompt, I hope I wasn’t too far off topic.

I came across an interesting quirk of database projects a few days ago. More specifically, a quirk when you come to publish a project that includes check constraints.

I had a few tables with check constraints added to different columns to specify that only particular values could be used in those columns. It looked something like the following:

CREATE TABLE [CheckConstraintExamples].Customer
(
    [CustomerID] INT NOT NULL, 
    [Forename] VARCHAR(50) NOT NULL, 
    [Surname] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(200) NOT NULL, 
    [CustomerType] VARCHAR(20) NOT NULL, 
    [CustomerLoyalty] VARCHAR(20) NOT NULL, 
    [CustomerValue] VARCHAR(20) NOT NULL, 
    CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerID]), 
    CONSTRAINT [CK_Customer_CustomerType] CHECK
        (CustomerType IN (
                               'Basic'
                             , 'Premium'
                             , 'Platinum')), 
    CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
        (CustomerLoyalty IN (
                                  'Low'
                                , 'Medium'
                                , 'High'
                                , 'Lapsed')), 
    CONSTRAINT [CK_Customer_CustomerValue] CHECK
        (CustomerValue IN (
                                'Low'
                              , 'Medium'
                              , 'High'
                              , 'Superspender'
                              , 'None'))
);

That all looks ok, and everything published fine, and kept on publishing fine until I happened to check the generated script for some of the publishes (I was in the early stages of development at this point so deployments were just being done through Visual Studio direct to my machine).

When I did I noticed that every deployment I was getting code like this:

GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerLoyalty]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerLoyalty];


GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerType]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerType];


GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerValue]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerValue];


GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerLoyalty]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerLoyalty]
CHECK (CustomerLoyalty IN (
                                'Low'
                              , 'Medium'
                              , 'High'
                              , 'Lapsed'));


GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerType]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerType]
CHECK (CustomerType IN (
                             'Basic'
                           , 'Premium'
                           , 'Platinum'));


GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerValue]...';


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerValue]
CHECK (CustomerValue IN (
                              'Low'
                            , 'Medium'
                            , 'High'
                            , 'Superspender'
                            , 'None'));


GO
PRINT
N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerLoyalty];

ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerType];

ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerValue];

That’s dropping all my constraints, recreating them using WITH NOCHECK, and then using WITH CHECK to check the existing data. Obviously all the data will pass those checks, but that’s going to add some time to my deployments, and as the size of the data and the number of checks both increase, these checks will take more and more time.

So, what’s going on? The answer was pretty simple really, when you create a table with check constraints like the ones I’ve used, SQL Server doesn’t use the IN when generating the table definition. Instead it breaks that out into a series of OR predicates, and when you deploy again the deployment process doesn’t recognise that the OR predicates are the same as the single IN predicate. Because it thinks the constraints are different, it drops the existing one and creates a new one based on the code in your project. Then the SQL Server engine transforms the IN predicate in the new constraint into a series of OR predicates and the cycle begins again.

The only solution is to re-write the CHECK CONSTRAINTs to use OR instead of IN, like this:

CREATE TABLE [CheckConstraintExamples].Customer
(
    [CustomerID] INT NOT NULL, 
    [Forename] VARCHAR(50) NOT NULL, 
    [Surname] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(200) NOT NULL, 
    [CustomerType] VARCHAR(20) NOT NULL, 
    [CustomerLoyalty] VARCHAR(20) NOT NULL, 
    [CustomerValue] VARCHAR(20) NOT NULL, 
    CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerID]), 
    CONSTRAINT [CK_Customer_CustomerType] CHECK
        (
               CustomerType ='Basic'
            OR CustomerType = 'Premium'
            OR CustomerType = 'Platinum'
        ), 
    CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
        (
               CustomerLoyalty = 'Low'
            OR CustomerLoyalty = 'Medium'
            OR CustomerLoyalty = 'High'
            OR CustomerLoyalty = 'Lapsed'
        ), 
    CONSTRAINT [CK_Customer_CustomerValue] CHECK
        (
               CustomerValue = 'Low'
            OR CustomerValue = 'Medium'
            OR CustomerValue = 'High'
            OR CustomerValue = 'Superspender'
            OR CustomerValue = 'None'
        )
)

Once I’d done that I thought everything would be fine so I generated another deployment script. Turns out there was one more little thing. When the SQL Server engine generated the object definition for those checks, it ordered the different predicates according to where they were in the original IN predicate, from last to first, which gives definitions like this:

ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
    ((
           [CustomerLoyalty]='Lapsed'
        OR [CustomerLoyalty]='High'
        OR [CustomerLoyalty]='Medium'
        OR [CustomerLoyalty]='Low'
    ))
GO

ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerType] CHECK
    ((
           [CustomerType]='Platinum'
        OR [CustomerType]='Premium'
        OR [CustomerType]='Basic'
    ))
GO

ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerValue] CHECK
    ((
           [CustomerValue]='None'
        OR [CustomerValue]='Superspender'
        OR [CustomerValue]='High'
        OR [CustomerValue]='Medium'
        OR [CustomerValue]='Low'
    ))
GO

Whereas if you check my definitions in the previous code block, I’d defined them the other way around. I suspected this meant the deployment engine still thought they were different check constraints, and to test it I deployed my revised project and generated another script. Sure enough, this final script wasn’t dropping and recreating those check constraints any more, and when I regenerated the table definition in management studio, it showed all of the predicates in the order I specified.

I said above that the only way to fix the issue I could see is to re-write the check constraints using a list of OR predicates. That’s not quite true. The other thing you can do is write your constraints like that to begin with, because you’re aware of the issue, and hopefully this blog means a few more of you will do that and not have to go through the re-write task I now have ahead of me.

So, this is pretty cool. Microsoft have brought GREATEST and LEAST functions to SQL Azure, and will be bringing them to on premise versions in a later release (presumably that means the next release).

These work like scalar functions versions of the MIN and MAX aggregations, you write something like GREATEST(1, 3, 4, 2, 6, 7, 5, 9, 13) and it returns the maximum of the values entered (13 in this case). The article gives a few use cases, and there is full documentation for both GREATEST and LEAST in Microsoft Docs.

This is something that’s been wanted for a long time, and in its absence we’ve often resorted to some complicated CASE statements that we’d probably all like to forget, so I’m really happy it’s there in Azure and coming soon to on prem. Now I just have to convince my company that we should probably migrate of SQL 2008 at some point…