自增MSSQL自动增长ID号重新排序

什么是MSSQL自动增长ID号

MSSQL自动增长ID号，也叫做自增长列，是一种常用的列类型，它可以自动的生成一组独一无二的ID号。在使用过程中，我们只需要定义该列的类型为自增长ID即可，每插入一条记录，该列都会自动递增1。MSSQL自动增长ID号广泛应用于主键、唯一键等需要保证数据唯一性的场合，可以大大简化开发过程中数据唯一性的验证操作。

为什么需要重新排序自增长ID号

虽然MSSQL自动生成的ID号能保证数据唯一，但是在实际的开发中，可能会发生由于删除记录等原因导致ID号没有占用的情况，也就是说有些ID号会被浪费掉。如果数据库数据量很大，浪费掉的ID号也会很多。此时，如果不对ID号进行重新排序，查询数据时就会浪费很多的存储空间、CPU资源和IO资源等。所以重新排序自增长ID号可以优化数据库性能，提高查询速度，提高存储利用效率。

如何重新排序自增长ID号

方法一：使用ALTER TABLE语句

使用ALTER TABLE语句可以改变表的结构，其中一个关键字就是IDENTITY，IDENTITY是一种常用的属性，可以在定义列时直接指定。下面我们将使用ALTER TABLE语句来重新排序自增长ID号，具体的步骤如下：

创建一个测试数据库

CREATE DATABASE TestDB;
GO
USE TestDB;

在测试数据库中创建一张表，定义ID列为自增长的主键

CREATE TABLE TestTable
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(50)
);

插入几条数据

INSERT INTO TestTable(Name) VALUES('Tom');
INSERT INTO TestTable(Name) VALUES('Lucy');
INSERT INTO TestTable(Name) VALUES('Mike');
INSERT INTO TestTable(Name) VALUES('John');
INSERT INTO TestTable(Name) VALUES('Kate');

使用ALTER TABLE语句来重新排序ID号

SET IDENTITY_INSERT TestTable ON
 
DECLARE @ID INT
SET @ID = 1 
UPDATE TestTable
SET ID = @ID, 
    Name = Name, 
    @ID = @ID + 1
ORDER BY ID ASC
 
SET IDENTITY_INSERT TestTable OFF

上述代码中，首先使用SET IDENTITY_INSERT语句来打开表的标识，使之能够插入指定值。接着，我们定义一个变量@ID，表示要更新的ID。然后使用UPDATE语句，将ID和名称重新排列，同时@ID的值也会递增。最后，我们再使用SET IDENTITY_INSERT语句来关闭表的标识。

方法二：使用ROW_NUMBER()函数

ROW_NUMBER()函数是SQL Server 2005中新增的一种函数，它返回一个加入行号的结果集。我们使用ROW_NUMBER()函数可以很方便的给自增长ID重新排序：

UPDATE TestTable SET ID = newID FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS newID, ID  FROM TestTable
) t WHERE TestTable.ID = t.ID

上述代码中，我们首先使用SELECT语句返回一个加入行号的结果集，然后将其作为子查询，并在外层使用UPDATE语句来更新原表。

总结

通过本文的介绍，我们了解了什么是MSSQL自动增长ID号以及为什么需要重新排序自增长ID号。我们还学习了二种重新排序自增长ID号的方法，分别是使用ALTER TABLE语句和ROW_NUMBER()函数。

ALTER TABLE语句的方法虽然需要手动指定每个ID的序号，但是该方法更灵活，适用性更强，可以根据自己的需求灵活的进行修改。而ROW_NUMBER()函数的方法更简洁，代码较少，适合于对ID号数量不大的情况。

无论是哪种方法，重新排序自增长ID号都可以优化数据库性能，提高查询速度，提高存储利用效率，是数据库开发中非常重要的一环。