syntaxhelp.com Your online cheat sheet!

Creating an IDENTITY column that increments by 5

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

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

SELECT * FROM Employees

/*
EmpID       EmpName
----------- --------------------
1           Jacob
6           Steve
11          Bob
*/

Creating an IDENTITY column that counts backwards

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

SELECT * FROM @t
/*
ID          Name
----------- --------------------
100         Jacob
99          Steve
98          Bob
*/

Creating an IDENTITY column that has a negative INCREMENT value

DECLARE @t TABLE (ID INT IDENTITY(2, -1), 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
----------- --------------------
2           Jacob
1           Steve
0           Bob
-1          Peter
*/

How to find the INCREMENT 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_INCR('Employees') AS IdentityIncrement
/*
IdentityIncrement
---------------------------------------
1
*/