As I understood in normal cases we are creating a table with Primary key which is having Identity property So Rename or Delete a column which is associated with Primary Key constraint will not be possible because constraint Rules are validating column structure. Tto achieve this we have to process some steps in the following way: Let us assume TableName = 'Employee' and ColumnName = 'EmployeeId' 1. Add new column 'EmployeeId_new' in the 'Employee' table ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

Now remove column 'EmployeeId' from 'Employee' table ALTER TABLE Employee DROP COLUMN EmployeeId This will throw error because of Primary Key Constraint rules are applicable and validating column structure. *### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'### So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column ALTER TABLE Employee DROP constraint [PK_dbo.Employee] Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error ALTER TABLE Employee DROP COLUMN EmployeeId Now Column 'EmployeeId' removed from table So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId' sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN' To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId' ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

8. 现在，带有'EmployeeId'的表'Employee'随着现有的主键约束被修改为Identity规则

考虑使用SEQUENCE而不是IDENTITY。

在sql server 2014(我不知道低版本)，你可以简单地使用序列。

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

从这里开始:将序列作为列的默认值

简单的解释

使用sp_RENAME重命名现有列

EXEC sp_RENAME 'Table_Name。Existing_ColumnName'， 'New_ColumnName'， 'COLUMN'

重命名示例:

现有列UserID被重命名为OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

然后使用alter query添加一个新列来设置为主键和标识值

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

使用实例设置主键

新创建的列名为UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

然后删除重命名列

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

删除重命名列的示例

ALTER TABLE Users DROP COLUMN OldUserID

现在，我们向表上的现有列添加了一个主键和标识。

这里有一个很酷的解决方案: 在列上添加或删除标识属性

简而言之，在SQL管理器中手动编辑您的表，切换标识，不要保存更改，只显示将为更改创建的脚本，复制它并稍后使用。

这节省了大量的时间，因为它(脚本)包含了所有与您更改的表相关的外键、索引等。手动写这个…上帝保佑。

不能将列更改为IDENTITY列。您需要做的是创建一个新列，从一开始就定义为IDENTITY，然后删除旧列，并将新列重命名为旧名称。

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

我不相信您可以使用tsql将现有列更改为标识列。但是，您可以通过Enterprise Manager设计视图来完成。

或者，您可以创建一个新行作为标识列，删除旧列，然后重命名新列。

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED