5

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).

  • Alternatives: 1) use BIGINT instead of INT. 2) Replace IDENTITY with SEQUENCE (and CYCLE property). – ypercubeᵀᴹ Apr 5 '17 at 10:44
  • 1) BIGINT is a large key for my scenario. Worst case is I reach overflow in a couple years, so I think I gain more by having a small key and perform some maintenance from time to time. 2) SEQUENCE - this is a good idea, but it is harder to manage within the ORM (would require some refactoring) and this works only if I know that small values do not live for a long time. – Alexei Apr 5 '17 at 10:52
  • Yes, I assumed you were deleting old values. If there are only gaps (and islands of remaining values, CYCLE won't work). There's also the option of removing the identity from these tables, since you don't have FKs towards them. But no idea how that may affect your applications and EF. – ypercubeᵀᴹ Apr 5 '17 at 10:57
  • 1
    Reading the comments it may not be suitable, but would this be a good use case for a GUID key over an int/identity one? – Gareth Lyons Apr 5 '17 at 13:31
  • 1
    @GarethLyons - Unfortunately, this involves quite some refactoring in the application layer, since it relies on all tables/models having an integer identifier. For a brand new application, it can be applied though. – Alexei Apr 5 '17 at 13:33
2

You could try simply dropping and re-adding the Identity column without copying the entire table. This has the same effect as what you are attempting with the code in the question. Neither approach really handles keeping existing rows as per the "some old values may stay forever" statement from the question, but for whatever situations work with the code in the question, the following should also work.

You should also consider starting the identity range at the min-value. Starting at 1 gives you just over 2.1 billion values, so starting at the minimum value (or close to it) gives you 4.2 billion values. Hence, less likely you will be needing such a reseed operation in the future.

SETUP

-- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

Drop and re-Create Identity PK

BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;
1

While (as seen in the comments), there are other potential options, it appears this one:

  • works;
  • doesn't require a significant outage;
  • doesn't require changes to your app;
  • could even be automated, if necessary.

I think it's probably your best option. It's the one I'd use.

0

You ask "Are there any methods to avoid performing a full data copy to obtain smaller values for identity columns?" To find if there are gaps in the sequence will require a read of every row (or index on that column). To fill a gap will require an update on every row past the gap. (Since you can't update an IDENTITY this becomes a delete followed by an insert, with some reseed jiggery-pokery in between. This feels like it may be slower than a "bulk table copy" approach, though the justification for that eludes me and is likely dependent on read-ahead and disk access algorithms.) Assuming it is the older data that is being deleted, it will have lower identity values so the bulk of the table will be updated each time. A full copy with sequential reads and writes sounds like it will be more efficient.

One approach is to make the table partitioned. Reseed the identity in a working table. Switch out the old data and switch in the working table. Down time will be measured in (milli)seconds.

Disclaimer: I haven't tested so am not sure how it will react. There will have to be consideration for writes that happen while the working table is being prepared. Perhaps two active partitions which undergo maintenance on alternate schedules?

  • All operations are performed from the application layer, so I can use a flag to mark process start and use it to suspend all activity affecting these tables. I will give it a try and come back here. – Alexei Apr 5 '17 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.