假如有这张一张表,当时创建时没有用来存放递增的数值的int型字段。在使用的过程中,有这样的需求。
USE AdventureWorks2008R2;GOIF OBJECT_ID(N'T33','U') IS NOT NULLBEGIN
DROP TABLE T33;END;GOCREATE 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);GOALTER TABLE T33 ADD CONSTRAINT PK_T33_ID PRIMARY KEY CLUSTERED (id);GO
code-6:添加自增列和主键约束