“How to set the ID to auto-increment in SQL Server?”

1 year ago
Benjamin Taylor
1 minute

In SQL Server, there are two methods to set an auto-incrementing ID field:

  1. AUTO_INCREMENT
CREATE TABLE TableName (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ...
);

In the example above, the ID field is defined as an integer type and uses the IDENTITY(1,1) property. In this case, the 1 indicates the starting value is 1, and the second 1 indicates the increment step size is also 1.

  1. Utilizing SEQUENCE Objects: In SQL Server 2012 and later versions, you can use SEQUENCE objects to generate auto-incremented IDs. Firstly, you need to create a SEQUENCE object, and then use the NEXT VALUE FOR expression on the ID field of the table. For example:
CREATE SEQUENCE SequenceName
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE
    NO CYCLE;

CREATE TABLE TableName (
    ID INT DEFAULT (NEXT VALUE FOR SequenceName) PRIMARY KEY,
    ...
);

In the above example, a SEQUENCE object named SequenceName is first created with a starting value of 1 and an increment step of 1. Then, when creating the table, the default value for the ID field is set to NEXT VALUE FOR SequenceName, allowing for automatic incrementing.

The auto-increment feature in SQL Server can be achieved using any method.

bannerAds