SQL Server IDENTITY columns

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Let SQL Server assign an ID

SQL example

 CopyCREATE TABLE dbo.auto (
  id INT IDENTITY(100,2) NOT NULL PRIMARY KEY,
  name NVARCHAR(50) NOT NULL,
  city NVARCHAR(50) NULL
);
INSERT INTO dbo.auto (name, city) VALUES (N'Ana', N'Pune');
SELECT id, name, city FROM dbo.auto;

The first inserted row has ID 100 and the next normally has 102. Identity values can have gaps and should not be treated as gap-free counters.

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

 CopyCREATE 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.

IDENTITY is not a row count

Deleted rows, transaction rollbacks, caching, and failed inserts can leave gaps. Do not use the last identity value to calculate the exact number of records.

Find the ID of your own insert

Use SCOPE_IDENTITY() or OUTPUT INSERTED.id with the insert batch, rather than IDENT_CURRENT() in a shared database.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

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: