How to Get Auto Increment ID in SQL Server

Recently, I was stuck in a situation where I had a table with a primary key value but no auto-increment. However, if the table holds thousands of records, adding the next ID value for the INSERT operation is quite difficult.

How to Get Auto Increment ID in SQL Server

To resolve this situation in SQL Server, we can create a trigger that fetches the maximum ID value and increments the ID value for the next insert operation. Let’s get started to see how to get auto increment ID in SQL Server.

SQL Server Trigger to Increment ID

In this section, we will discuss how to create a SQL Server trigger to increment the ID value automatically. For this, we will use the tblUSAStates table, which does not have the auto-increment ID column.

Here is the Transact-SQL code to create the tblUSAStates table and insert some records in it.

create table tblUSAStates(
	id INT,
	state_name VARCHAR(50),
	state_code VARCHAR(50)
);
insert into tblUSAStates (id, state_name, state_code) values (1, 'South Carolina', 'SC');
insert into tblUSAStates (id, state_name, state_code) values (2, 'Pennsylvania', 'PA');
insert into tblUSAStates (id, state_name, state_code) values (3, 'Georgia', 'GA');

In the above SQL code, we have created the table named tblUSAStates. And also inserted 3 records in the same table.

Now, if the same table holds thousands of records, determining the last ID value is difficult. So, here, we will create an SQL Server Trigger that will first fetch the maximum ID value. After this, increment the maximum ID value by 1 and then perform the INSERT operation.

Here is the SQL code for the trigger in SQL Server.

CREATE OR ALTER TRIGGER trgInsertIncrementID
ON tblUSAStates
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @id INT,
			@name VARCHAR(50),
			@code VARCHAR(50)

	SET @id = (SELECT MAX(id) FROM tblUSAStates) + 1
	SELECT @name = state_name, @code = state_code FROM inserted 

	INSERT INTO tblUSAStates(id, state_name, state_code)
	VALUES (@id, @name, @code)
	PRINT 'Record Insert with incremented ID'
END

• In this example, we have defined an instead of trigger named trgInsertIncrementID. This trigger will first fetch the maximum ID value and then increase the ID value by 1.
• In the end, it will use the state name, state code, and new ID value to insert the record in the tblUSAStates table.

After creating the trigger, if we try to perform the INSERT operation without specifying the ID column value, the trigger will execute and automatically increment the ID value.

Here is the INSERT operation that we will perform on the table in SQL Server.

INSERT INTO tblUSAStates (state_name, state_code)
VALUES ('Florida', 'FL')

After performing the above INSERT operation, we will the new record will ID value automatically set to 4.

By doing this, we can increment the id in SQL Server by trigger.

SQL Server Trigger to Increment ID after Insert

In this SQL Server section, we will understand how to create a SQL Server Trigger to increment ID after performing the UPDATE operation.

We will create an AFTER Trigger in SQL Server to increment the ID value. Again, for this implementation, we are going to use the tblUSAStates table, but this time, we will create an AFTER Trigger.

Here is the SQL query for the AFTER Trigger in SQL Server.

CREATE OR ALTER TRIGGER trgAfterInsertIncrementID
ON tblUSAStates
AFTER INSERT
AS
BEGIN
	DECLARE @id INT
	SET @id = (SELECT MAX(id) FROM tblUSAStates) + 1

	UPDATE tblUSAStates
	SET id = @id
	WHERE id IS NULL
END

In the above example, we have created a trigger named trgAfterInsertIncrementID. This trigger will fire after the insert operation is executed. Moreover, it will fetch the maximum ID value from the table and increment it by 1.

In the last, it will use the UPDATE statement to set the id to a maximum ID value + 1.

Now, if we perform the INSERT operation on the table in SQL Server, it will insert the record with the ID value NULL. Then, a trigger will be fired, and it will update the value of the ID using the UPDATE statement.

INSERT INTO tblUSAStates(state_name, state_code)
VALUES ('California', 'CA')

The output will be look like below.

By performing the above operation, it will use the trigger to increment the value of the ID.

SQL Server Trigger to Increment ID Reset

In this section, we will discuss how to create an SQL Server Trigger to reset the auto-increment ID value. For this implementation, we will use the same tblUSAStates table.

However, the tblUSAStates table does not contain an auto-increment column, so first, we will understand how to ALTER the table and add one.

Here are the SQL queries to add an auto-increment ID column to an existing table in SQL Server.

--Adding new auto-increment column
ALTER TABLE tblUSAStates
ADD newID INT IDENTITY(10, 1)
GO

--Droping the existing ID column
ALTER TABLE tblUSAStates DROP COLUMN id
GO

--Renaming the new column to previous one
EXEC sp_rename 'tblUSAStates.newID', 'id', 'Column'

• In the above query, we first added a new column to the table named newID, this time an auto-increment column.
• After this, we dropped the previous ID column from the table using the ALTER table statement.
• In the last, we use the sp_rename stored procedure to rename the newID column to just the id column.

Now that we have added the auto-increment column to the tblUSAStates table, let us move to the next phase. Next, we will understand how to create the AFTER DELETE Trigger to reset the value of the auto-increment IDENTITY column.

CREATE OR ALTER   TRIGGER [dbo].[trgIncrementIDReset]
ON [dbo].[tblUSAStates]
AFTER DELETE
AS
BEGIN
	DBCC CHECKIDENT ('tblUSAStates', RESEED, 1)
END
GO

• In the above example, we have created a trigger that will be fired after performing the DELETE operation in SQL Server. So, the trigger will reset the auto-increment value to 1.
• After performing a delete operation, the next time we make an insert in the table, the id value will start from 1 instead of 10.

Here is the sample SQL for the DELETE operations performed on the table in SQL Server.

DELETE FROM tblUSAStates
where id  = 12

Now, if we check the table using the SELECT statement, we can observe that this Id 12 has been deleted from the table.

SQL Server Trigger to Increment Identity

In SQL Server, when we define an auto-increment table column using the IDENTITY, we don’t need to increment the value of ID manually. Instead, it will automatically increment the value of the ID column whenever we perform the insert operation on a table.

In such cases, we don’t usually need a trigger to increment the identity column value in SQL Server. For more details related to the IDENTITY column in SQL Server, please refer to the following SQL Server tutorial: Identity Column in SQL Server.

Conclusion

In this SQL Server tutorial, we have discussed how to create an SQL Server Trigger to increment the ID value. We have also discussed multiple examples.

Also, take a look at some more SQL Server tutorials.

• How to execute Trigger in SQL Server
• SQL Server Trigger On View
• How to Debug an SQL Server Trigger