What you need to know about SQL identity columns

As every other developer working with Microsoft SQL Server on-premise, you probably already came in contact with SQL IDENTITY columns. They are commonly used and are pretty straightforward… or at least they seem that way.

As an architect I regularly use them in my designs, but there are a few quirks I learned along the way that some of you might not have encountered yet.

Disclaimer: I’m not a DBA nor is this article intended for DBA’s, this is mainly directed towards developers.

So, now that we got our DBA disclaimer up….. ūüėČ
Here are some of the things you might now know yet…

You can only have 1 identity column in a table

When trying to create more than 1 identity column in a table, SQL Server will fail you:

CREATE TABLE [dbo].[Foo](
    [FirstId] [int] IDENTITY(1,1) NOT NULL,
    [SecondId] [int] IDENTITY(1,1) NOT NULL
)
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.

There can only be one identity property per table and as such, there can only be one IDENTITY column.

Finding the value that was inserted

Older applications might use @@IDENTITY to return the last identity value inserted. However, if you use @@IDENTITY and insert into a table that runs a trigger and generates another identity value, you will get back the last value generated in any table.

That’s why you use SCOPE_IDENTITY() to return the inserted value. Every procedure, trigger, function and batch has it’s very own scope.
SCOPE_IDENTITY() shows the most recently inserted IDENTITY in the current scope (which ignores any triggers that might have fired).

Gaps in identity columns are normal #1 (Transactions)

When you use an INSERT statement against a table that has an IDENTITY column, the IDENTITY value increases whether the INSERT succeeds or fails.

If a transaction is rolled back, the new IDENTITY column value isn’t rolled back with it, so you might end up with gaps in your sequencing even when you’re not deleting any rows. This can be a problem when you’re creating sequences that can’t contain gaps, such as for invoice numbers.

That means: identity columns are a terrible way to determine sequencing!

FYI: The same applies for a SQL Server SEQUENCE (if you do not use the NOCACHE option)

Gaps in identity columns are normal #2 (AlwaysOn)

In a SQL Server AlwaysOn setup, you always have a primary and zero or more secondary replicas. You might use an IDENTITY column in a database which is synchronized in an Availability Group.

Due to the way IDENTITY columns are optimized for performance in SQL Server, SQL always reserves a range in memory to speed up the creation.
When dealing with a fail-over between replicas, you might encounter gaps in the identity. Often an automatic fail-over will trigger a gap as opposed to a manual fail-over where it might happen less frequent.

More information on how to “fix” this:

FYI: The same applies for a SQL Server SEQUENCE (if you do not use the NOCACHE option)

Gaps in identity columns are normal #3 (Crashes or restarts)

If, for some reason, your SQL Server crashes, it is perfectly possible to have a gap in your values of your IDENTITY field.

The same reason applies as with #2:
Due to the way IDENTITY columns are optimized for performance in SQL Server, SQL always reserves a range in memory to speed up the creation. A crashed SQL Server service, might leave a gap as well.

Additionally, this can also occur just by restarting your SQL Server service or restarting your Windows Server, although it is much less likely.

More information on how to “fix” this:

Update: here’s an excellent example on how to simulate this behavior.

FYI: The same applies for a SQL Server SEQUENCE (if you do not use the NOCACHE option)

Truncating a table also resets its identity property

The TRUNCATE TABLE statement resets the IDENTITY property in the table to the seed. To keep the IDENTITY counter, use DELETE instead of TRUNCATE TABLE, but keep in mind that DELETE may be much slower because it’s fully logged in case your recovery model is set to Full.

 

If – after all of this – you’re left wondering with:
“This is fine and all, but I used an IDENTITY to create a sequential number in SQL because I didn’t know any better. I can’t do much to change it now, how do I proceed?”
I got just the thing for you!
Take a look at this awesome article: http://www.itprotoday.com/software-development/should-i-use-identity-or-not

It provided you with two awesome alternatives.

 

Hope this suits your needs. Let me know in the comments if I missed something.

Cheers,
Pieter

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s