SQL IDENTITY: Auto-Increment Guide

In SQL, IDENTIY is a property used to automatically generate a unique incrementing numerical value for a specific column in a table when inserting new records.IDENTITY can be applied to integer columns to ensure each inserted record has a unique identifier.

When using the IDENTITY property, it is necessary to specify its starting value, increment value, and maximum value. For example, when creating a table, you can use the following syntax to define a column with the IDENTITY property:

CREATE TABLE TableName (
    IDColumn INT IDENTITY(1,1) PRIMARY KEY,
    OtherColumn VARCHAR(50)
);

The example above creates a table called TableName, with a column called IDColumn, which is an integer type auto-incrementing column and is specified as a primary key. IDENTITY(1,1) indicates that the column will start auto-incrementing from 1, increasing by 1 each time.

When inserting a new record, you don’t need to provide a value for the IDColumn, as the database will automatically generate a unique identifier for it. For example:

INSERT INTO TableName (OtherColumn) VALUES ('Value1');

When executing the above statement, the database will automatically generate a unique value for the IDColumn, ensuring that each inserted record has a distinct identifier.