修改列的标识属性:

In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer. Clear the Allow nulls check box for the column you want to change. In the Column Properties tab, expand the Identity Specification property. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

就是这样，对我很管用

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

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

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

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规则

修改列的标识属性:

In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer. Clear the Allow nulls check box for the column you want to change. In the Column Properties tab, expand the Identity Specification property. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

就是这样，对我很管用

简单的解释

使用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

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

在对象资源管理器中右键单击表名。你会得到一些选择。点击“设计”。将为该表打开一个新选项卡。您可以在“列属性”中添加标识约束。