/* 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](1, 1)
What you could 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
[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]
,不做任何解释。没有人明白,