syntaxhelp.com Your online cheat sheet!

Creating an IDENTITY Column

USE Tempdb
GO

CREATE TABLE Employees (
    EmpID INT IDENTITY,
    EmpName VARCHAR(20) )
GO

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

SELECT * FROM Employees

/*
EmpID       EmpName
----------- --------------------
1           Jacob
2           Steve
*/

An example that uses @@IDENTITY system variable

DECLARE @NewVal INT
INSERT INTO Sales (col1, col2) SELECT 'val1', 'val2'
SELECT @NewVal = @@IDENTITY
-- do something with @NewVal

An example that uses SCOPE_IDENTITY() function

DECLARE @NewVal INT
INSERT INTO yourtable (col1, col2) SELECT 'val1', 'val2'
SELECT @NewVal = SCOPE_IDENTITY()
-- do something with @NewVal

Inserting an explicit value into an IDENTITY column?

CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 100, 'Jacob'
SET IDENTITY_INSERT Employees OFF

SELECT * FROM Employees 
/*
EmpID       Name
----------- --------------------
100         Jacob
*/

How to find the current IDENTITY value of a table

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

SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1

INSERT INTO Employees(Name) SELECT 'Jacob'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1

INSERT INTO Employees(Name) SELECT 'Steve'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 2

Identifying all the stored procedures that uses @@identity

SELECT DISTINCT 
	o.name 
FROM sysobjects o 
INNER JOIN syscomments c ON (c.id = o.id) 
WHERE xtype = 'P' 
	and category = 0 
	and c.text LIKE '%@@identity%'
ORDER BY o.name