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_INSERT
s 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.