Skip to main content
Database Administrators

Return to Answer

Incorporated comment
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

forFor simple numeric IDs, just go with identity and forget all the problems of manually generating them.

youYou can always create a "super table" that uses an idetityidentity as the PK and have a type column, and any other info. When you need a new ID (assuming you mean unique IDS across different tables) just insert into this table and grab the SCOPE_IDENTITY()SCOPE_IDENTITY() and then insert into the actual table you need.

Basically you create a table: MasterIDs with an identity PK, when you need to insert a row into your Table1, INSERT INTO MasterIDs and get the identity generated by that row using SCOPE_IDENTITY() and then insert into Table1 using that value as the PK.

Table1 will have a non-identity int PK. You would do the same process to insert into Table2, etc. Let SQL Server manage the identity values in the MasterIDs table, which you can then use in your other tables. MasterIDs could contain other tables, like type (so you could know what table, Table1 or Table2, etc, uses that identity value.

for simple numeric IDs, just go with identity and forget all the problems of manually generating them.

you can always create a "super table" that uses an idetity as the PK and have a type column, and any other info. When you need a new ID (assuming you mean unique IDS across different tables) just insert into this table and grab the SCOPE_IDENTITY() and then insert into the actual table you need.

For simple numeric IDs, just go with identity and forget all the problems of manually generating them.

You can always create a "super table" that uses an identity as the PK and have a type column, and any other info. When you need a new ID (assuming you mean unique IDS across different tables) just insert into this table and grab the SCOPE_IDENTITY() and then insert into the actual table you need.

Basically you create a table: MasterIDs with an identity PK, when you need to insert a row into your Table1, INSERT INTO MasterIDs and get the identity generated by that row using SCOPE_IDENTITY() and then insert into Table1 using that value as the PK.

Table1 will have a non-identity int PK. You would do the same process to insert into Table2, etc. Let SQL Server manage the identity values in the MasterIDs table, which you can then use in your other tables. MasterIDs could contain other tables, like type (so you could know what table, Table1 or Table2, etc, uses that identity value.

Post Merged (destination) from dba.stackexchange.com/questions/1671/…
Post Migrated Here from stackoverflow.com (revisions)
Source Link
RacerX
RacerX

for simple numeric IDs, just go with identity and forget all the problems of manually generating them.

you can always create a "super table" that uses an idetity as the PK and have a type column, and any other info. When you need a new ID (assuming you mean unique IDS across different tables) just insert into this table and grab the SCOPE_IDENTITY() and then insert into the actual table you need.