Learn how to set up SQL Server Auto Increment in SSMS. Our SQL Server Support team is here to help you with your questions and concerns.

SQL Server Auto Increment SSMS | Guide

In SQL Server databases, the “auto-increment” column helps maintain unique identifiers for rows within a table. This unique identifier, is often used as a primary key. It helps keep the integrity and uniqueness of each record.

Today, we are going to take a look at creating and managing auto-increment columns using SQL Server Management Studio (SSMS).

1. Create a Table with Auto-Increment Column

   First, we have to open SSMS and connect to our SQL Server instance. In a new query window we have to run this script to create a table with an auto-increment column:

   


   Here,

   • ID acts as the auto-increment identifier.
   • The IDENTITY(1,1) property indicates that the column starts at 1 and increments by 1 for each new row.
   • OtherColumn1, OtherColumn2 can be customized as per our table requirements. We can also add more columns if needed.
2. Inserting Data into the Table

   Now, we have to insert data into our table. The ID column will automatically receive a unique value for each row:

   
INSERT INTO YourTableName (OtherColumn1, OtherColumn2) VALUES ('Value1', 'Value2');

3. Viewing Auto-Increment Values

   We can also view the auto-increment values assigned to the ID column after an insert operation, with the SCOPE_IDENTITY() function:

   INSERT INTO YourTableName (OtherColumn1, OtherColumn2) VALUES ('Value3', 'Value4');
SELECT SCOPE_IDENTITY() AS LastInsertedID;


   Here, the SCOPE_IDENTITY() function returns the last identity value generated.
4. Modifying an Existing Table

   If we already have a table and want to add an auto-increment column, we can use the ALTER TABLE statement:

   ALTER TABLE YourExistingTable ADD ID INT PRIMARY KEY IDENTITY(1,1);

   However, we have to make sure the table does not have any data before using ALTER TABLE statement.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

Today, our Support Engineers demonstrated how to set up SQL Server Auto increment in SSMS.