0

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.

  • 2
    Which SQL Server version? If you're on 2012, a SEQUENCE might do. – spaghettidba Nov 5 '14 at 16:31
  • If pre 2012, can you not have table Step1 have an IDENTITY column and after inserting, return the assigned IDENTITY value and use that in your Step2 and History INSERT statements? – Mike Nov 5 '14 at 16:52
  • @spaghettidba Not sure on version but 2012 may be possible. After looking at SEQUENCE it will solve the problem exactly like I want to solve it. Thanks for the hint on that one. – RubberChickenLeader Nov 5 '14 at 17:48
  • @Mike, The problem is that I will have 2 different processes with 2 different sets of tables so using an IDENTITY on one table and carrying it over wont work. All ready had thought about that. – RubberChickenLeader Nov 5 '14 at 17:50
  • Use SELECT @@version; to check your version. – ypercubeᵀᴹ Nov 5 '14 at 18:01
3

If the version is at least 2012 use a SEQUENCE

  • The higherups will find space on a 2012 server so I can impliment the SEQUENCE. Thanks. – RubberChickenLeader Nov 5 '14 at 19:59

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.