SQL SERVER TRUNCATE TABLE does not reset self-value of tables

Today, when cleaning up business-Library data, developers say they can use TRUNCATE table to clean up all data from two tables

Both tables have their own ID, all made table partitions, single table 200GB, two tables using SELECT ident_current (')

The current self appreciation is 3000012, the two tables are almost identical, except for one or two fields.

I executed two SQL statements, truncate all two tables, and then forgot to shrink the database and reset the seed values

Since the last value of the partition function is 2000000, then the data that is inserted will accumulate in the last filegroup

1, reset the seed value, let the data reuse the first filegroup

2, modify the partition scheme, use split range, add new values to the partition function

It's OK to not reset, but if you use split range, adding new values to the partition function is a lot of work, so it's best to reset the seed value without increasing the workload.

Because I was too busy to move the database.

Come back from work to remember this matter, also need to finish work, immediately remote to the business database machine, use the following SQL statement to view the partition situation

--Zoning case
SELECT  pat.*,
        fg.[ GroupName] as ' partition scheme corresponding filegroup name ', patsch.name '
        partition scheme for current partition function ' 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 partition number,
                            MIN (ID) as Min_value,
                            MAX (ID) as Max_value,
                            count (ID) as number of records
                     from
  dbo. Postlog
                     GROUP by $PARTITION. fun_new_postlog_id (Id)
                   ) as Pat on PAT. [Partition number] = DDS. [DESTINATION_ID]
        INNER JOIN Sys.partition_schemes as Patsch on DDS. [PARTITION_SCHEME_ID] = patsch.data_space_id ORDER by  pat.[ Section number]

The newly inserted data for two tables was found to be in the first filegroup, and the self-increasing ID starts at 1.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Immediately tested, the results found that TRUNCATE TABLE really reset the seed, was known before, but the previous use of truncate rarely

Use [test]
go
to CREATE TABLE truncatetabletestidentity (id INT IDENTITY (1,1), NAME NVARCHAR
    
) go INSERT [dbo]. [Truncatetabletestidentity]
Select ' NI ' union all
Select ' we ' UNION ALL
select ' PP '
    
select * FROM [dbo].[ Truncatetabletestidentity]
go
    
TRUNCATE TABLE [dbo].[ Truncatetabletestidentity]

Next shrink the database, disk space and " more out " 450GB space, disk warning disappeared ~

If there is a wrong place, welcome to shoot Bricks O (∩_∩) o

2014-4-7 Supplements:

Mentioned in MSDN

DBCC checkident (Transact-SQL)