DBCC CHECKIDENT Atomically Change Identity Seed?

I have a set of staging tables that need to be used to update a hierarchy of tables with foreign keys between them, and identity columns for the primary keys. One way I'm thinking of doing this is to reset the identity seed on the target tables based on the number of rows I have in the staging tables, then to update the staging tables keys to match the vacated range of identity values. I'd insert them with SET IDENTITY_INSERT ON.

The question is: can this be done atomically? It seems that DBCC CHECKIDENT will return the current identity value, but can only change the seed to an absolute value. That would require that I get the current value, add "n" to it, then set the seed value. This would seem to be non-atomic, in that a new row could be inserted between the time I find the "current" value and the time I set the new value.

Does anyone know of a way to pre-allocate a block of identity values atomically? This has to be done in a live OLTB database.

Further research shows that a fundamental assumtion I was maing is incorrect. DBCC CHECKIDENT does not return the current identity seed. I was depending on this to allow me to increment the seed value, but it appears that cannot be done.

It looks like replication might help here, but since this project must be finished by September 1, we don't have time to learn anything else new. It looks like I'll be stuck using cursors and doing these inserts one at a time.

|||

You can use IDENT_CURRENT() function to return the last used value and IDENT_SEED() to find the created seed.

e.g.

Code Snippet

SELECT TABLE_SCHEMA, TABLE_NAME,
IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS [IDENT_SEED],
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS [IDENT_CURRENT]
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;

Then use dbcc checkident() to reseed as needed.

|||

Thanks. This should do it for me. I can check after the DBCC CHECKIDENT to see whether any rows were created within the block that I "reserved", and try again until I get all the values I need.

Dbcc Checkident

Dear All,

I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.

Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.

However I get the following:

User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

All users get the same message. This is an ASP.NET web app.

Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.

A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident