快速对表的某字段赋递增的数值

假如有这张一张表，当时创建时没有用来存放递增的数值的int型字段。在使用的过程中，有这样的需求。

USE AdventureWorks2008R2;
GO

IF OBJECT_ID(N'T33','U') IS NOT NULL
BEGIN
    DROP TABLE T33;
END;
GO

CREATE TABLE T33 (
    col_1 NVARCHAR(20),
    col_2 NVARCHAR(40)
);
GO

code-1:建表

 

插入测试数据

INSERT INTO T33 (col_1,col_2)
SELECT TOP (1000) LEFT(a.[name],20),NEWID() FROM sys.objects AS a CROSS JOIN sys.objects AS b;
GO

code-2:插入数据

 

这时，如果需要在这张表上增加一列int型的字段。除了直接在SSMS的表设计器（添加字段id，并设置为自增列）上做之外，还可以使用脚本来实现。用脚本来实现有一个好处是：如果表已有的数据已经很多时，有很大的优势。

ALTER TABLE T33 
ADD [id] INT DEFAULT(1) NOT NULL;
GO

code-3:添加字段——id

 

有几种办法可以给新建的字段赋予递增的数值，可以使用循环，游标等，但使用CTE效率最高。这里使用的是row_number()窗口函数，其中order by子句不能缺少，又无需对表数据进行排序，所以加了select 1子句，优化器会知道这跟排序无关。

WITH CTE1 AS (
SELECT id,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS row_no FROM T33
)
UPDATE CTE1 SET id = row_no;
GO

code-4:赋值

 

如果还要添加主键的话：

ALTER TABLE T33 ADD CONSTRAINT pk_T33_id PRIMARY KEY CLUSTERED (id);
GO

code-5:添加主键

 

疑问：在添加id字段之前，是否可以使用脚本添加自增列？如何添加？

 

----------2016-01-08 18:00 更新--------

经过@goto13 的提醒，直接在添加id字段时，指定为自增列就已经可以实现最后的效果了。谢谢！不过使用CTE这种赋值也算是另一种思路吧。

 

ALTER TABLE T33 ADD [id] INT NOT NULL IDENTITY(1,1);
GO
ALTER TABLE T33 ADD CONSTRAINT PK_T33_ID PRIMARY KEY CLUSTERED (id);
GO

 code-6：添加自增列和主键约束