SQL Server – Creating a Multi Table Identity Column

database-designsql server

I am currently designing a job execution engine and have run into a problem I am looking for some advice on.

I am converting some processes to a new system and need to define the table structure. The process consists of 3 steps with different data for each step (3 different webservice calls). Currently a flat file is generated with a RowID and the data for all 3 steps on the same row. The new design is for each process will pull directly from tables and each step will have its own table with only the data fields needed for each step.

The problem is that the rows between the tables need to be linked so that after step 1 completes successfully we can act on step 2 with the proper data. since none of the step data will be guaranteed to be unique (through business rules) I need to have an ID to link the tables together. The basic table structure will be as follows:

CREATE TABLE Step1
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE Step2
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE History
(
BatchID INT,
BatchRowID ???,
StepID INT,
Result BIT --True False flag
)

I am looking for a solution to create a faux multi table IDENTIY column. so that Process2 that uses a step 4 will have gaps where BatchRowID's are used for Process1. The first thing that comes to mind is to use a GUID but as the clustered PK on the tables will be BatchID, BatchRowID I want to try and avoid a GUID if I can. This is for multiple reasons, 1) Clustered GUIDs suck for inserts (however this is overcome slightly by including BatchID) and 2) The additional space used over an INT in the base table and in every index added to the table.

If any one has implimented something similar I would love to hear your solutions.

Currently the host server has not been determined so SQL Version could be 2008 or 2012.

Best Answer

If the version is at least 2012 use a SEQUENCE

Related Question