Sql-server – Why is IDENTITY_INSERT ON only allowed on one table at a time

I'll answer here as well. It has to do with the internals of how IDENTITY and SEQUENCE work.

With IDENTITY, SQL Server pre-caches values into memory so that they are readily available. See Martin Smith's answer for the details. As values are used, a background process generates more values. As you can imagine this pool can run out pretty quickly, leaving the application at the mercy of the background process that is generating the values.

With SEQUENCE, SQL Server allows you to define how large the cache should be. While SQL Server doesn't actually keep the values in the cache, it only keeps the current value and the top end value, this will greatly reduce the amount of IO that is needed to create values.

Don't set the cache too high, as this will reduce the number of numbers which can be used: if SQL Server were to crash, any values specified in the current cache range which weren't used would be lost.

As for row insertion, just specify a default value for the column, like so:

DEFAULT (NEXT VALUE FOR Audit.EventCounter),

Doing dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity.

        CREATE TABLE TestIdent
        (
            ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
            , SomeText nvarchar(255)
        );

        dbcc checkident('dbo.TestIdent',reseed,0)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

This causes the next identity entered to be 10:

        TRUNCATE TABLE dbo.TestIdent;

        DBCC CHECKIDENT('dbo.TestIdent',reseed,10)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;