SQL Server – Ensure Primary Key Uniqueness Across Two Tables

sql server

I have a system with an Events table that holds a list of events to process. Once they are processed, their status field is marked such, and they remain there for archive/history purposes. The PK of the Events table is identity.

The Events table is frequently queried for event type, due time, etc. Most of the time only unprocessed events are of interest. But due to the number of records constantly growing, these queries start taking up more time than they should.

So, I'd like to implement a process that regularly brings the old processed events to another table, so that the primary events table remains lean. Ideally, I'd like that other table be named like EventsArchive and keep exactly the same field setup, with one difference that the PK field is not identity. However, now there is a risk that upon inserting new record in Events table, the PK value is something that's already used in the EventsArchive table.

Is there any way how I can guarantee that the PK field values of Events table do not collide with the archived values in EventsArchive table? I can imagine switching to finding the Events.PK value by means of generating random long value and checking if this value is not taken in both Events and EventsArchive. But that doesn't sound right.

Best Answer

As others have said, this is an ideal use case for a filtered index. But in order to satisfy your curiosity, you have a few options.

The EventArchive table could have an additional field "date-inserted" and just populate that when you add records to the EventArchive table. the combination of primary key (from the Events table) and the date-inserted should be unique.

I assume you are going to truncate the Events table after each migration? You could also specify the next identity value to use with the DBCC CHECKIDENT command https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql.

In your case, it would be DBCC CHECKIDENT (Events, RESEED, "NextIdentityValue")

But if you simply delete the items in the events table (do this in a loop with commits and/or log backups in between loops to avoid growing your log file out of control) then your identity value won't change.

Related Question