文章出處

SQLSERVER truncate table之后是否會重置表的自增值

今天清理業務庫數據的時候，開發人員說可以使用truncate table把兩個表的所有數據清理掉

這兩個表都有自增ID，都做了表分區，單表200GB，使用 SELECT IDENT_CURRENT('') 的時候兩個表

的當前自增值是3000012，這兩個表幾乎是一模一樣的，除了一兩個字段不一樣

我執行兩個SQL語句把兩個表都truncate掉，然后就忘記了收縮數據庫和重置種子值

由于分區函數的最后一個值是2000000，那么后插入的數據都會積聚在最后一個文件組

1、重置種子值，讓數據重新利用第一個文件組

2、修改分區方案，使用 split range，向分區函數添加新值

雖然不重置也可以，但是如果使用 split range，向分區函數添加新值工作量就大了，為了不增加工作量當然重置種子值最好

因為當時太忙，繼續搬數據庫去了

下班回來才想起這件事，還需要收尾工作，馬上遠程到業務數據庫的機器，使用下面的SQL語句查看分區情況

--分區情況
SELECT  pat.* ,
        fg.[groupname] AS '分區方案對應的文件組名稱' ,
        patsch.name '當前分區函數對應的分區方案'
FROM    sys.destination_data_spaces AS dds
        INNER JOIN sysfilegroups AS fg ON dds.[data_space_id] = fg.[groupid]
        INNER JOIN ( SELECT $PARTITION.Fun_New_PostLog_Id(id) AS 分區編號 ,
                            MIN(id) AS Min_value ,
                            MAX(id) AS Max_value ,
                            COUNT(id) AS 記錄數
                     FROM   dbo.PostLog
                     GROUP BY $PARTITION.Fun_New_PostLog_Id(id)
                   ) AS pat ON pat.[分區編號] = dds.[destination_id]
        INNER JOIN sys.partition_schemes AS patsch ON dds.[partition_scheme_id] = patsch.data_space_id
ORDER BY  pat.[分區編號]

發現兩個表的新插入的數據都放在第一個文件組，并且自增id又從1開始了

馬上測試一下，結果發現truncate table真的重置了種子，以前是知道的，不過以前用到truncate的情況很少

USE [test]
GO
CREATE TABLE truncatetabletestidentity (id INT IDENTITY(1,1),NAME NVARCHAR(20))
GO

INSERT [dbo].[truncatetabletestidentity]
SELECT 'ni' UNION ALL
SELECT 'we' UNION ALL
SELECT 'pp'

SELECT * FROM [dbo].[truncatetabletestidentity]
GO

TRUNCATE TABLE [dbo].[truncatetabletestidentity]

接下來收縮數據庫，磁盤空間又“多出“了450GB空間，磁盤預警消失了~

如有不對的地方，歡迎大家拍磚o(∩_∩)o

2014-4-7補充：

在MSDN里面提到

DBCC CHECKIDENT (Transact-SQL)

DBCC CHECKIDENT (table_name, RESEED,new_reseed_value )

將當前標識值設置為 new_reseed_value。如果自從創建表以來未在表中插入任何行，或者已使用 TRUNCATE TABLE 語句刪除所有行，則在運行 DBCC CHECKIDENT 之后插入的第一行將使用 new_reseed_value 作為標識。否則，插入的下一行將使用new_reseed_value + 當前增量值。

如果該表不為空，那么將標識值設置為小于標識列中的最大值的數字時，將會出現下列情況之一：