Identity value management

Identity column is a column that uniquely identifies every row. It differs from a primary key in that its values are managed by the server and ( unless you specify it explicitly ) can't be modified. In many cases it is used as a primary key.

Identity value management:

Create Identity:

CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL)

Insert Identity values:
You do not have to specify identity value when inserting new record: INSERT INTO Person(Name) values('Oleh'). It will be filled for you using current identity and increment values.
In case you specify one: INSERT INTO Person(PersonID,Name) values(2,'Oleh') you will get an error: Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Person' when IDENTITY_INSERT is set to OFF.
Instead you need to use next snippet:
SET IDENTITY_INSERT Person ON
INSERT INTO Person(PersonID, Name) VALUES(1,'Oleh')
SET IDENTITY_INSERT Person OFF

When you insert identity value explicitly and it's a primary key column then you will get an error:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_Person'. Cannot insert duplicate key in object 'dbo.Person'.
The statement has been terminated.

Get current identity value:

• DBCC CHECKIDENT(TableName, NORESEED) - returns current identity value
• SCOPE_IDENTITY() - returns the last identity value inserted within the same scope(stored procedure, trigger, function or batch)
• @@IDENTITY - returns the last identity value inserted across all scopes
• IDENT_CURRENT(TableName) - returns the last identity value for the specified table regardless of the scope.

Update identity value:

• DBCC CHECKIDENT(TableName, RESEED, NewSeed) - resets the identity value with provided seed
• DBCC CHECKIDENT(TableName) - resets the identity value only if it is required

Get Seed/Increment:

• IDENT_INCR(TableName) - returns original increment value that was specified when identity was created.
• IDENT_SEED(TableName) - returns original seed value that was specified when identity was created.

SELECT TABLE_SCHEMA, TABLE_NAME
, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED
, IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL

Finding gaps in identity column:

• Here you can find the original post about how you can find gaps in identity column.

Regards,
Oleh