adams.squared (11/25/2013)
I have a table of 200,000 records. The natural primary key for this table is the combination of columns A1, B2, C3 and D4. I want to create a surrogate key so that I can have one column for my primary key. Right now to do this, I concatenate the four columns that make up the natural primary keys. If those columns are char(20), then my surrogate key is now char(80).
Why do you want to create a surrogate key from these four columns instead of using them as they are and saving space?
adams.squared (11/25/2013)
I know I could use the Identity option, but over time some records are added and some are deleted for whatever reason. I need to be able to recreate the table. If I recreate the table, I need to be able to ensure that the surrogate key is still assigned to the correct record and I can't do that if I use the Identity option.
You don't have to rebuild the table and loose the primary key if its an identity. You can use IDENTITY_INSERT.