T-SQL Ninja #25

IDENTITY

What is IDENTITY?

IDENTITY is a property for columns in a table. It can be used to generate keys, i.e. unique, consecutive values in a column. The SQL Server guarantees that several parallel running transactions receive different values for the column.

How can you work with IDENTITY?

The IDENTITY property is passed two values, the SEED, which is the starting value that the first row in the table contains and the INCREMENT, which is the value by how much the current value should be increased after each row. It is usual to set both values to one. So let’s create a table with an IDENTITY column:

DROP TABLE IF EXISTS #IdTest 

CREATE TABLE #IdTest
(
     Id int IDENTITY(1,1)
    ,Txt varchar(250)
)

Now we add a row to this table and query it:

INSERT INTO #IdTest (Txt) 
VALUES ('Test')

SELECT 
     Id
    ,Txt 
FROM #IdTest

You will see a line with the text “Test” and the ID value of one. If you now add two more columns, they get the Id values two and three:

INSERT INTO #IdTest (Txt) 
VALUES ('Test2'), ('Test3')

SELECT 
     Id
    ,Txt 
FROM #IdTest

As experienced ninjas, you may have noticed that we omitted the Id column in the INSERT statements because it is defined as IDENTITY and is therefore filled by the SQL server itself. So now let’s try to include this column:

INSERT INTO #IdTest (Id, Txt) 
VALUES (4, 'Test4')

Then we get an error message saying that it is not possible to insert into an IDENTITY column if IDENTITY_INSERT is disabled. So let’s change that:

SET IDENTITY_INSERT #IdTest ON

INSERT INTO #IdTest (Id, Txt) 
VALUES (4, 'Test4')

SET IDENTITY_INSERT #IdTest OFF

SELECT 
     Id
    ,Txt 
FROM #IdTest

Now our table contains one more line and if we insert one more line, it even gets the id 5 correctly:

INSERT INTO #IdTest (Txt) 
VALUES ('Test2'), ('Test3')

SELECT 
     Id
    ,Txt 
FROM #IdTest

So now let’s insert an Id that does not match the current row and afterwards another row if the IDENTITY function is switched on:

SET IDENTITY_INSERT #IdTest ON
INSERT INTO #IdTest (Id, Txt) 
VALUES (60, 'Test6')
SET IDENTITY_INSERT #IdTest OFF

INSERT INTO #IdTest (Txt) 
VALUES ('Test5')

SELECT 
     Id
    ,Txt 
FROM #IdTest

Now the two inserted lines have received the id’s 60 (where we had specified them explicitly) and 61, where we had assigned them automatically. So the IDENTITY column “noticed” that its current maximum value is currently 60 and automatically assigned 61 as its next value.

What the next value of the identity column would be, we can check with a DBCC command:

DBCC CHECKIDENT ('#IdTest')

The DBCC command can also be used to reset the current SEED of the IDENTITY:

DBCC CHECKIDENT ('#IdTest', RESEED, 1)

After this, the first CHECKIDENT query returns the information that the current IDENTITY value is one, while the current column value is 100. However, the next INSERT query automatically checks the maximum value of the column again and the next row is assigned the ID 101:

INSERT INTO #IdTest (Txt) 
VALUES ('Test8')

SELECT 
     Id
    ,Txt 
FROM #IdTest

Now we would like to learn something about the transactional security of IDENTITY columns. For this we open a transaction and insert a row into the table, which we query directly:

BEGIN TRANSACTION
INSERT INTO #IdTest (Txt)
VALUES ('Transaction 1')

SELECT 
     Id
    ,Txt 
FROM #IdTest

We see that our new line has got the next Id (101) as expected.

If we now roll back the transaction, the row is of course no longer in the table:

ROLLBACK TRANSACTION
SELECT 
     Id
    ,Txt 
FROM #IdTest

Prüfen wir nun den Wert der Identity-Spalte, dann sehen wir, dass der IDENTITY-Wert nun auf 101 stehengeblieben ist:

DBCC CHECKIDENT ('#IdTest') 

If we now check the value of the Identity column, we see that the IDENTITY value has now stopped at 101:

INSERT INTO #IdTest (Txt)
VALUES ('Transaction 1')

SELECT 
     Id
    ,Txt 
FROM #IdTest

You have seen that columns with the IDENTITY attribute automatically take the smallest possible next free value in the column. This can result in gaps. And be warned: despite the identity column by IDENTITY_INSERTs it can happen that duplicates are contained in the column. If you want to avoid this, you have to add the UNIQUE attribute to the column.

References

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *