Unique ID, Several SQL Tables

I have 3 tables (check, active, history) which are all identical in structure (bar constraints on fields).

Data could be initally inserted onto any of the three tables, and is "unique" by way of two fields (docnum + pagenum).

I cannot use a compound primary key (as using ASPxGridView which doesn't like them), using a GUID as a primary key causes issues thanks the asp.net and is not ideal, however the docnum+pagenum is a unique key. So althought the prmary key would be some int value, the validation for uniqueness is dependant on the unique key.

The obvious solution is to use an identity column on each table.
Data will be migrated from one table to another (eg check-> active -> history) and searches are required over both the active and history tables. So there is always the possibility that when retreiving records you will get a duplicate primary key (the identity id field).

I have thought of using a master table that essentially assigns the next number in a int field on any insert to each of the three tables and then increments the number for the next insert but I fear that that will performance overhead.

What options do I have here?

Who is Participating?
cyberkiwiConnect With a Mentor Commented:
One option is to add a field to your select convert(varchar(20), docnum) + "-" + convert(varchar(20), pagenum) as ASPGrid_PK
and setting that as the PK for the gridview
AzaniahAuthor Commented:
If doing that would you suggest then having docnum+pagenum as the primary key in the tables and not having any kind of identity field?

Determining which table the record comes from won't be an issue due to constraints on other fields.
That works for me!
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

AzaniahAuthor Commented:
Although that appears to work (and there is a method for creating a composite key in the ASPxGridView) if I did need to create a unique key across those three tables how would that be achieved?
That would be quite tricky....
First you need a table [x] that has a single identity column
Then you need a new column in the 3 tables - and you need to update all existing data with a value
Then you need to add a new int column [uid]
Finally, add a before insert trigger to each table that will perform an insert into [x] with readpast + cleanup and use the scope_identity() generated to populate the [uid] column
AzaniahAuthor Commented:
Yes thats very similar to the MasterID table I tested. I suspect that this would be a bit of a performance hit.

I'm not an expert in any of this, but my logic would assume that if I am forcing docnum+pagenum to be unique then it may as well be the primary key. They are both int fields, so the indexing will be fairly good I assume (rathar than using an alpha key - or a random GUID - both of which I suspect have potential performance hits).

Is there much difference in performance hit for a primary index being int+int as oppossed to just a single Int (as an identity field would be)?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.