Chris Johnson

One of my goals for 2023 was to speak at a SQL user group again. I set this because in 2021 I’d felt like I was starting to build a speaking profile. I’d spoken at New Stars of Data, and that had gone really well, and I’d applied to GroupBy and DataMinutes and been accepted for both. For reasons I’ve already gone into, I didn’t keep going with it, but it felt like I had some momentum at the time and giving a talk at a user group seemed like a good first step to getting that back without setting myself too big a challenge.

Anyway, last Thursday I spoke at Data Bristol. I gave a modified version of the Visual Studio database projects talk from New Stars of Data, gearing it more towards people who didn’t necessarily have any prior experience with the tool. I felt like that was important for an audience like a user group, where people don’t have any choice of sessions, it’s fine to pitch an expert level talk at a large conference where attendees have other sessions they can go to instead but at a user group everyone has to listen to your talk so it doesn’t feel that I can assume existing knowledge.

I felt like the talk went pretty well. I wasn’t as prepared as I was for the New Stars of Data talk, and that showed in a couple of places when the transition between slides wasn’t that smooth, or I lost the thread of what I was saying a bit. I also felt that the lack of prep affected my confidence a bit, and because of that I didn’t project well enough in places. With that said, I got some great feedback and a few questions, so overall I feel pretty good.

I also feel like that was a good warmup for maybe doing some other talks, so I’m going to keep my eyes open for other opportunities and start applying. I do feel like database projects is a good topic for a talk, and using them to split your database over multiple projects and re-using code across databases is a really powerful tool that nobody else is really talking about.

Epic life quest

Speaking at a user group wasn’t a distinct goal of my epic life quest, but there is a goal to speak 5 times at user groups, and this takes that up to 3.

However, I also got some non-SQL news this week. One of the goals I had was to become a faction ref at my larp system. This didn’t quite happen, but instead I applied this year to be a member of the game team, and I heard on Friday that they’d accepted my application. Larp is a ridiculous hobby that I love doing (there’s a very cool video that my main system has put out as an advertisement) and being on game team means I will be helping to run things (as a volunteer) and bring some of the joy I’ve got from it to other people.

I’m therefore counting this as a point in my epic life quest, and that gives me all 5 points, which means I’ve completed level 8. Hopefully I can push on a bit this year and make some progress towards level 9 as well!

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.