来自:http://hi.baidu.com/ibelieve9/blog/item/686a34a4ebec54f29052ee91.html
将Customer替换为自己的表
ID为自增字段
使用SQL Server的自增功能来管理表的关键字,时间久后由于删除原因ID会不连续,如何重新“整理”关键字ID,使其重新从1开始,并且重置自增初始值为当前记录个数?
/*允许对系统表进行更新*/
exec sp_configure 'allow updates',1
reconfigure with override
GO
/*取消标识列标记 */
update syscolumns set colstat = 0 where id = object_id('dbo.Customer') and colstat = 1
GO
/*所有对记录的ID进行重排*/
update dbo.Customer
set ID=(select count(1) from dbo.Customer where ID<=t.ID)
from dbo.Customer t
GO
/*得到重排后的记录总个数*/
declare @a int
set @a=(select count(*) from dbo.Customer)
/*重新设置标识的起始值*/
DBCC CHECKIDENT (Customer, RESEED, @a)
GO
/*恢复标识列标记*/
update syscolumns set colstat = 1 where id = object_id('dbo.Customer') and name = 'ID'
GO
/*禁止对系统表进行更新*/
exec sp_configure 'allow updates',0
reconfigure with override