syntaxhelp.com Your online cheat sheet!

Creating an IDENTITY column with SEED value of 100

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (
    EmpID INT IDENTITY (100, 1),
    EmpName VARCHAR(20) )
GO

INSERT INTO Employees (EmpName) SELECT 'Jacob'
INSERT INTO Employees (EmpName) SELECT 'Steve'

SELECT * FROM Employees

/*
EmpID       EmpName
----------- --------------------
100         Jacob
101         Steve
*/

Creating an IDENTITY column that has a negative SEED value

DECLARE @t TABLE (ID INT IDENTITY(-100, -10), Name VARCHAR(20))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
INSERT INTO @t (Name) SELECT 'Bob'
INSERT INTO @t (Name) SELECT 'Peter'

SELECT * FROM @t
/*
ID          Name
----------- --------------------
-100        Jacob
-110        Steve
-120        Bob
-130        Peter
*/

How to reset (change) the IDENTITY SEED value

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

DELETE FROM Employees
DBCC CHECKIDENT('Employees',RESEED, 0)
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
*/

How to find the SEED value of an IDENTITY column?

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

SELECT IDENT_SEED('Employees') AS IdentitySeed
/*
IdentitySeed
---------------------------------------
1
*/