In a replacement OLTP database, should we create a new identity column for SalesOrder?

Hi All,

Question: We're replacing an existing Oracle OLTP database with a new one in SQL 2012.  Should we create a new IDENTITY column to be used by the new GUI, or should we continue to use the existing column that was an identity in the legacy system?  

Background: Originally, we decided to migrate the legacy identity column to the new system but create a new IDENTITY column.  I don't quite remember our reasoning now, and it seems that using the legacy identity column (without creating a new one) would be just fine.  The only reason I can see for doing this would be a way to ID rows created in the legacy system from rows created in the new system.

The existing GUI will be pointed at the new database until it, too, is replaced by a new GUI.

Originally, my question was going to be "should we use some sort of numeric identity column for SalesOrderDetail," but then I couldn't remember why we had decided to create a new IDENTITY column in the new system.  I see the legacy system never repeated values for SalesOrderDetailID, and I can think of no good reason for this.

Thanks,
Eric B.

September 11th, 2015 12:26pm

The question is not entirely clear, but I don't see a reason why not to continue using the identity column. In SQL Server 2012 (?) and up you can use new SEQUENCE type column, if needed. If your original ID column is INT and you plan to have lots and lots of rows, then you may also consider using bigint to avoid problems in a future.
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 12:38pm

Thanks, Naomi.

Could you think of any good reason to generate and store a [non-IDENTITY] numeric value for sales order detail rows?

-Eric B.

September 11th, 2015 1:25pm

If you need to use that new sales order details row ID value in some other tables when inserting information and you're not using a stored procedure, it may be a good idea to generate the ID in the application and use it. In our system we're using numeric(17,0) keys in many tables and generate IDs manually using a stored procedure and sequence like mechanism. There are some disadvantages to it (say, using numeric(17,0) makes little sense since bigint should work fine and save space), but overall this technique works well and we know the latest used key by checking that sequence table.
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 1:33pm

Thanks, Naomi, I greatly appreciate your help.
September 11th, 2015 2:13pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics