SQL Server: ensure primary key uniqueness over two tables 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.
For performance in this scenario, you should consider Filtered Index.

It looks like it is ideal solution for your case. Just filter only events with appropiate status and index will be small and fast.

Other pros will be minimal effort to implement this solution (add new index).