Ms SQL Server, mssqlserver

Ms SQL Server, mssqlserver

/* BY Dylan SUN */

In SQL server when you want to make an existing column to be auto-incremented.

The following code is not working:

ALTER TABLE [dwh].[ExchangeRate]ALTER COLUMN [ExchangeRateId] Int Identity(1, 1)

What you coshould do is:
-Create a new column with auto-increment
-Delete existing column constraint
-Delete the existing column
-Rename the new column back
-Add deleted constraint

Step 1: Create a new column

ALTER TABLE [dwh].[ExchangeRate]ADD [ExchangeRateId2] Int Identity(1, 1)

Step 2: Delete existing constraint

ALTER TABLE [dwh].[ExchangeRate]DROP CONSTRAINT [IxExchangeRate_ExchangeRateId_U_NC_]

Step 3: Delete existing column

ALTER TABLE [dwh].[ExchangeRate]DROP COLUMN [ExchangeRateId]

Step 4: Rename new column

Exec sp_rename 'dwh.ExchangeRate.ExchangeRateId2', 'ExchangeRateId','COLUMN'

Step 5: Add deleted contraint

ALTER TABLE [dwh].[ExchangeRate] ADD  CONSTRAINT [IxExchangeRate_ExchangeRateId_U_NC_] PRIMARY KEY CLUSTERED ([ExchangeRateId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]