declare @a table (id int) insert into @a select 1 insert into @a select 2 insert into @a select 3 insert into @a select null insert into @a select 5 insert into @a select null insert into @a select 7 SELECT id, row_number() over (order by id)as rownumber from id RowNumberNULL 1 NULL 2 1 3 2 4 3 5 5 6 7 7@a
如果要更新,不如新键一个identity列,然后删除旧列,改新列名改旧列名
不知道你的id 有没有自增约束! 有的话只能删除重新建立一个 if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[p_DropIDENTITY]') and OBJECTPROPERTY(id,N'IsProcedure')=1) drop procedure [dbo].[p_DropIDENTITY] GO /*--标识列转换为普通列采用修改标识列名,然后按新规则重列的方法处理 只适用于标识列没有与其他对象有关联的表 转换后,列的相关对象也不会恢复 有一定的局限性,仅供参考--邹建 2005.05(引用请保留此信息)--*/ /*--调用示例EXEC p_DropIDENTITY 'tb ' --*/ CREATE PROC p_DropIDENTITY @TableName sysname --要处理的表名 AS IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0 BEGIN RAISERROR( '"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName) RETURN END --标识列转换处理检查 DECLARE @s nvarchar(1000),@FieldName sysname,@bkFieldName sysname,@sql nvarchar(4000) SELECT @FieldName=QUOTENAME(c.name), @bkFieldName=CAST(NEWID() as char(36)), @s=@FieldName+N''+QUOTENAME(t.name) +CASE WHEN t.name LIKE '%int'THEN N'' ELSE N'('+CAST(c.prec as varchar) +N','+CAST(c.scale as varchar)+N')' END FROM sysobjects o,syscolumns c,systypes t WHERE o.name=@TableName AND o.id=c.id AND c.xusertype=t.xusertype AND c.status=0x80 IF @@ROWCOUNT=0 BEGIN RAISERROR(N'表"%s" 中无标识列',1,16,@TableName) RETURN END --修改标识列名 SET @sql=QUOTENAME(@TableName)+N'.'+@FieldName EXEC sp_rename @sql,@bkFieldName,N'COLUMN ' --转换为标识列处理 SELECT @TableName=QUOTENAME(@TableName), @bkFieldName=QUOTENAME(@bkFieldName) EXEC( 'ALTER TABLE '+@TableName+N'ADD'+@s) EXEC( 'UPDATE '+@TableName+N'SET'+@FieldName+N'='+@bkFieldName+N' ALTER TABLE '+@TableName+N'DROP COLUMN'+@bkFieldName) RAISERROR(N'表"%s"中的标识列"%s"已经转换为普通列',1,16,@TableName,@FieldName)
insert into @a select 1
insert into @a select 2
insert into @a select 3
insert into @a select null
insert into @a select 5
insert into @a select null
insert into @a select 7
SELECT id,
row_number() over (order by id)as rownumber
from id RowNumberNULL 1
NULL 2
1 3
2 4
3 5
5 6
7 7@a
有的话只能删除重新建立一个
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[p_DropIDENTITY]') and OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure [dbo].[p_DropIDENTITY]
GO
/*--标识列转换为普通列采用修改标识列名,然后按新规则重列的方法处理
只适用于标识列没有与其他对象有关联的表
转换后,列的相关对象也不会恢复
有一定的局限性,仅供参考--邹建 2005.05(引用请保留此信息)--*/ /*--调用示例EXEC p_DropIDENTITY 'tb '
--*/
CREATE PROC p_DropIDENTITY
@TableName sysname --要处理的表名
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR( '"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName)
RETURN
END --标识列转换处理检查
DECLARE @s nvarchar(1000),@FieldName sysname,@bkFieldName sysname,@sql nvarchar(4000)
SELECT @FieldName=QUOTENAME(c.name),
@bkFieldName=CAST(NEWID() as char(36)),
@s=@FieldName+N''+QUOTENAME(t.name)
+CASE WHEN t.name LIKE '%int'THEN N''
ELSE N'('+CAST(c.prec as varchar)
+N','+CAST(c.scale as varchar)+N')'
END
FROM sysobjects o,syscolumns c,systypes t
WHERE o.name=@TableName
AND o.id=c.id
AND c.xusertype=t.xusertype
AND c.status=0x80
IF @@ROWCOUNT=0
BEGIN
RAISERROR(N'表"%s" 中无标识列',1,16,@TableName)
RETURN
END --修改标识列名
SET @sql=QUOTENAME(@TableName)+N'.'+@FieldName
EXEC sp_rename @sql,@bkFieldName,N'COLUMN ' --转换为标识列处理
SELECT @TableName=QUOTENAME(@TableName),
@bkFieldName=QUOTENAME(@bkFieldName)
EXEC( 'ALTER TABLE '+@TableName+N'ADD'+@s)
EXEC( 'UPDATE '+@TableName+N'SET'+@FieldName+N'='+@bkFieldName+N'
ALTER TABLE '+@TableName+N'DROP COLUMN'+@bkFieldName)
RAISERROR(N'表"%s"中的标识列"%s"已经转换为普通列',1,16,@TableName,@FieldName)