A Comprehensive Guide to Using Identity Insert in SQL Server

As a full-stack developer, getting data models and database mechanics like primary keys, sequences and identifiers to function properly is crucial for building robust data-driven applications. SQL Server provides flexible tools like identity columns to generate these keys and values automatically. But sometimes, you need more control.

That‘s where the SQL Server IDENTITY_INSERT setting comes in handy…

What Exactly is an Identity Column?

An identity column is a numeric data type column that auto-generates sequential values as rows are inserted into the table, via a seed (starting point) and defined increment.

CREATE TABLE Users (
  ID int IDENTITY(1,1) PRIMARY KEY, 
  Username varchar(100)
);

On the first INSERT, ID will be 1, then 2 for the next row, 3 for the third row, continuing in increments of 1 based on the (1,1) after IDENTITY.

Identity Property Parameters

We can customize identity generation rules using additional properties:

• Seed – The first value used on initial insert (defaults to 1 if not specified)
• Increment – The value added to the last used identity for each insert (defaults to 1 if not specified)
• Not For Replication – Prevents the identity range from being managed by replication agents
• No Cache – Disables identity value caching for faster inserts in high-volume systems

CREATE TABLE LogEvents (
  EventID int IDENTITY(1000,5) NOT FOR REPLICATION NO CACHE,
  EventData varchar(500)  
);

Now ID will start at 1000 and increment by 5 for each insert, ignoring replication and with no caching.

When to Use Identity Columns

Identities are useful when you need:

• A unique key for table rows
• A way to identify rows and relate tables together
• Automatic incrementing values
• Simple index performance gains

You see them often on primary keys and foreign keys used to model relationships.

When to Override Identity Insert

Despite their automation, there are times when you need to manually insert identity values instead of having them generated, such as:

• Migrating data – Preserving a numeric ID column from one system to another
• Merging datasets – Importing identity values from one table into another
• Auditing requirements – Inserting IDs in a defined sequence for tracking
• Complex business rules – Emulating patterns matching other identifiers
• Testing interface integrity – Controlling primary keys to validate downstream impacts more accurately

This is where the IDENTITY_INSERT setting comes into play…

How to Enable Identity Insert

We can override the identity generation process by enabling IDENTITY_INSERT on the target table:

SET IDENTITY_INSERT dbo.Users ON;

Now identity values can be explicitly inserted into the Users table, instead of being auto-generated.

Inserting Override Values

With IDENTITY_INSERT enabled, we can insert any valid int value for the identity column just like other columns:

INSERT INTO dbo.Users (ID, Username)  
VALUES (10, ‘user10‘),  
       (20, ‘user20‘);

This inserts rows with the given ID values 10 and 20 rather than generating new sequential values.

Disabling Identity Insert

It‘s good practice to always disable IDENTITY_INSERT immediately after doing an insert that overrides the identity values:

SET IDENTITY_INSERT dbo.Users OFF;

This prevents coding mistakes that accidentally insert incorrect identifiers going forward.

When to Use Alternatives

While convenient, IDENTITY_INSERT is not the only way to override identity values…

DBCC RESEED

If you need to restart identity generation from a specific value after inserting rows, use DBCC RESEED instead:

 
DBCC CHECKIDENT (‘dbo.Users‘, RESEED, 10);

This resets the identity seed counter to 10, so next insert will use 11.

Sequences

For more complex sequences, SQL Server also supports CREATE SEQUENCE and NEXT VALUE FOR:

CREATE SEQUENCE CustomIDSequence  
    START WITH 5000  
    INCREMENT BY 5 ;  
INSERT INTO dbo.Users (ID, Username)
VALUES (NEXT VALUE FOR CustomIDSequence, ‘user5k‘);

This gives you more flexibility to increment identities however you want.

Identity Insert Gotchas

Working with IDENTITY_INSERT does come with a few key caveats…

• Can only be set on one table at a time. Switching tables requires disabling and re-enabling.
• Bypasses duplicate key checking. Be careful not to insert duplicates.
• Not thread safe for high concurrency inserts. Use caution in transactions.
• Constraints aren‘t checked which can insert invalid data if not careful.

It‘s also easy to neglect disabling IDENTITY_INSERT later, leading to unexpected identity behavior down the road…

Recovering from Identity Gaps

Identity values can sometimes get out of sequence from application errors, DBCC RESEED issues, and other edge cases leading to gaps like missing numbers.

For example, starting with identity values 1, 2, 3…

A bad delete or DBCC check might next try to insert 4, Skipping 3!

We need to realign to make sure identities sequence properly again.

Finding Gaps

To find gaps or holes you can run this handy query to show you identities that are out of sequence:

SELECT ID, Username 
FROM dbo.Users
WHERE ID - (ROW_NUMBER() OVER (ORDER BY ID)) > 0

Any results indicate an identity jump or dropped value.

Reseeding Correctly

Once found, we can reseed the last well known continuous value to resume proper sequencing:

  
DBCC CHECKIDENT (‘dbo.Users‘, RESEED, 2);

Combined with IDENTITY_INSERT to handle existing data, identities can be restored to smooth continuity.

Platform Comparison

SQL Server identities have similar capabilities to other database platforms, with some subtle differences…

Oracle

Oracle‘s equivalent to IDENTITY_INSERT is the sequence bypass keyword:

ALTER SEQUENCE CustomSEQ BYPASS CACHE;
INSERT INTO dbo.Users (ID, Username)
VALUES (CustomSEQ.NEXTVAL, ‘user9999‘); 
ALTER SEQUENCE CustomSEQ NO BYPASS CACHE;

PostgreSQL

PostgreSQL doesn‘t support identity insert directly but allows overriding serials by simply specifying a value:

INSERT INTO dbo.Users (ID, Username)  
VALUES (55, ‘user55‘);

So portability varies across database platforms.

Best Practices

When using IDENTITY_INSERT, it‘s wise follow these guidelines:

• Only enable on one table per session.
• Disable immediately after override insert.
• Use set based approaches vs cursors or loops.
• Wrap in transactions to handle rollbacks.
• Leverage try/catch blocks to handle errors.

Adopting these practices will help avoid common identity management pitfalls.

Conclusion

SQL Server identity columns are incredibly useful for generating keys and driving application identifiers and sequence values automatically in background database plumbing.

But the flexibility to directly control sequences is also crucial for migrations, testing integrity, implementing business rules, audit tracking and recovery procedures.

The IDENTITY_INSERT setting, combined with DBCC RESEED, sequences, and other techniques give developers precision control when needed.

Like many powerful database tools, it should be used judiciously in controlled scenarios. But mastering behavior quirks and best practices pays dividends across many SQL programming challenges.

I hope this guide gives you a firm grasp of working with identity insert to expand your ID automation expertise!