How To Create A Table With Identity Column In SQL Server

The identity column is crucial for maintaining data integrity and ensuring that each row of your table contains an autogenerated and unique value. It is also crucial in case you are setting primary keys. Note that you can set only one identity column in a table. Once you set the identity property, it is impossible to remove it, and the only way is to drop the table or recreate it.

Syntax

CREATE TABLE Table_Name (
    [Column_name] [int] IDENTITY(seed, increment) NOT NULL,
    [Column_name] [data_type] NOT NULL,
    Column_name [data_type]
);

Example -1

Let’s discuss an example. Here, I am creating a table named Vehicle, where the VehicleID is the IDENTITY column. Below is the SQL script to create that table.

CREATE TABLE Vehicle(
	[VehicleID] [int] IDENTITY(1,1) NOT NULL,
	[VehicleName] [nvarchar](50) NOT NULL,
	[VehicleBrand] [nvarchar](50) NOT NULL
);

Let’s execute the above script in SQL Server Management Studio. The script executed successfully, as shown in the screenshot below.

Check out: How to add identity column to existing table in SQL Server

I got the expected output, and the Vehicle table was created successfully, as shown in the screenshot below.

Now for the confirmation: To check the identity column in the SQL server, right-click on the column and select Properties.

You can see below that the identity value is True, which means it is an identity column.

Note: IDENTITY(1,1) means the starting value  IDENTITY is 1, which will increment by 1 for each new record.

Check out: How to reset identity column value in SQL Server

Example -2

Now, let us create a table with a primary key and identity column in SQL server. Here, the table name is Orders, and OrderID is the identity column and also the primary key of the table.

CREATE TABLE Orders (
    [OrderID] [int] IDENTITY(1, 1) PRIMARY KEY,
    [OrderName] [nvarchar](50) NOT NULL,
    [OrderType] [nvarchar](50)
);

The query was executed successfully, as shown in the screenshot below.

Now, I got the expected output, and the Orders table was created successfully with OrderID as the identity column and the primary key, as shown in the screenshot below.

Conclusion

You can use the IDENTITY property and the CREATE TABLE statement to create a table with an identity column in SQL that ensures Data integrity. Check out the above examples for more information.

You may also like following the articles below.

• How To Generate Script Of Table With Data In SQL Server
• How to insert values into table with identity column in SQL Server
• How to turn off identity column in SQL Server