I have some leaf tables (no FK towards them) with a few hundreds of thousands records that are used to synchronize some external data using Entity Framework ORM. This involves some DELETEs followed by BULK INSERTs.
For most of the tables, some old values may stay forever, so I cannot use SEQUENCEs with CYCLE, as suggested in one of the comments.
One effect is that identity values will keep increasing over time and I want to be able to reduce their values.
This question and its answers explain that identity values cannot be updated, even if
identity_insert is on for the table.
One quick way to do is transferring all data to a buffer table and perform the switch via rename. Something like the following:
-- ActiveDirectoryCache_bak is the table I want to reduce identity values for -- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready begin tran select min(UserId), count(1) from ActiveDirectoryCache_bak DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED); -- Min UserId = 100, Count = 176041 -- Checking identity information: current identity value '204558', current column value '204558'. select * into ActiveDirectoryCache_bak_buffer from ActiveDirectoryCache_bak where 1 = 0 DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1) insert into ActiveDirectoryCache_bak_buffer select LoginUsername, GivenName, MiddleName, Surname, EmailAddress from ActiveDirectoryCache_bak drop table ActiveDirectoryCache_bak alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId) EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak'; select min(UserId), count(1) from ActiveDirectoryCache_bak DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED); -- Min UserId = 1, Count = 176041 -- Checking identity information: current identity value '176041', current column value '176041'. -- this should be replaced with commit when not in test mode rollback
I can afford to make this kind of operations during the night and they seem to take only a few seconds for my data volume (4 seconds for this example).
Question: Are there any methods to avoid performing a full data copy to obtain smaller values for identity columns? Or this is one of the best approaches in my context (reasonable data volume and being able to lock some tables for several seconds).