SQL Server IDENTITY columns

The SQL Server IDENTITY(seed, increment) property automatically generates a numeric value when a row is inserted. It is commonly used for surrogate keys and reference numbers.

An IDENTITY property generates values, but if the column must be unique, enforce that requirement with a PRIMARY KEY or UNIQUE constraint.

Create an IDENTITY column

CREATE TABLE dbo.auto (
  id INT IDENTITY(100,2) NOT NULL CONSTRAINT PK_auto PRIMARY KEY,
  name NVARCHAR(50) NULL,
  city NVARCHAR(50) NULL
);

The first generated value is 100 and the next is 102 because the seed is 100 and the increment is 2. Gaps can occur; applications should not assume identity values are gap-free.

Insert without supplying the identity value

INSERT INTO dbo.auto (name, city)
VALUES (N'John Reid', N'London');

SQL Server generates the id automatically. In SQL Server Management Studio, the same property appears under the column's Identity Specification.

To retrieve the value generated for the row inserted by the current request, continue to SCOPE_IDENTITY() and inserted identity values.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: