How to change My PK to Identity?
I am making some improvements to a system that I didn't write. We have lots of tables which do not use the IDENTITY property - but these tables are using integer surrogate primary keys. The primary keys are assigned using a stored procedure from a key table. This is an ugly, horrible disgusting solution that was fairly common 20 years ago to get around limitations of database managers back then. But we're in the 21st century now so we should be using something more elegant and manageable.
So, I have a central table called Trade with lots of foreign keys into it on the surrogate column - tradeId. What I need to do is change the tradeId column to Identity via a script that can be applied in the dev environment, QA/UAT, and then production. An easy way around this is to harness the power of the scripting capabilities of SQL Server Management Studio (SSMS). So, to solve our problem right click on the table and select Design.
Where it says Identity Specification, expand it and then select the drop down next to (Is Identity) - select Yes. Change the Identity Seed value to the max value in your table. I've decided to do this via a script as I don't know what the production max seed value is so I will have to find that with something like:
select max(tradeId) from trade
before I change the table.
So, now that we have changed the Identity, from the Table Designer menu select Generate Change Script.
SSMS will in the background (via SMO) generate a script for you to make the change. I do not recommend applying the script as is though, we have further work to do yet. You should get a window popping up asking you to you want to save the change script to a file, select yes. Sometimes if you have a lot of data you will get a warning saying this process may take a long time - up to you what you want to do here. My advice is always test in a development environment first.
The script will have lots of ALTER TABLE DROP CONSTRAINT statements to drop any foreign keys into Trade. It will then create a new temp table with an Identity column. I recommend you edit this section to put in a dynamically allocated Identity seed value.
So for my table the script for the temp table looks like this (truncated):
CREATE TABLE dbo.Tmp_Trade
(
TradeId dbo.entityid NOT NULL IDENTITY (1, 1),
ModifiedBy dbo.entityid NOT NULL,
ModifiedAt datetime NOT NULL,
Version int NOT NULL,
...
)
What you should do is replace the CREATE TABLE above with something like this:
declare @newTradeSeed int
declare @stmt nvarchar(max)
select @newTradeSeed = max(tradeId) + 1 from dbo.trade
select @stmt = 'CREATE TABLE dbo.Tmp_Trade
(
TradeId dbo.entityid NOT NULL IDENTITY (' + cast(@newTradeSeed as nvarchar(10)) + ', 1),
ModifiedBy dbo.entityid NOT NULL,
ModifiedAt datetime NOT NULL,
Version int NOT NULL,
...
)
exec (@stmt)
Further down the script IDENTITY_INSERT is switched on for the new tmp table so that the data can be copied in with an INSERT..SELECT statement. Once the data is in, we can drop the original Trade table and then rename the tmp table back to Trade. Once this has been done, we simply add our foreign keys back which SSMS kindly scripted out for us.
It is important on busy systems to run all the above within a transaction to prevent consistency errors. I like to run it in a TRY..CATCH block within a transaction. So:
BEGIN TRY
BEGIN TRANSACTION
<my script>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if @@trancount > 0 ROLLBACK TRANSACTION
<some error logging code>
END CATCH
(Or something roughly like the above in structure).