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.
