T-SQL Reseeding

USE Play
GO

CREATE TABLE [dbo].[ReseedTest](
	[ReseedTestID] [int] IDENTITY(1,1) NOT NULL,
	[ReseedText] [nvarchar](50) NULL,
	CONSTRAINT [PK_ReseedTest] PRIMARY KEY CLUSTERED([ReseedTestID])
)
GO

SET NOCOUNT ON

-- Clear table data. Reset to PKID 1 (counter is at 0, increments before writing)
TRUNCATE TABLE dbo.ReseedTest

-- This row will have an ID of 1. 
INSERT INTO dbo.ReseedTest(dbo.ReseedTest.ReseedText) VALUES(N'1') -- Insert row 1
SELECT * FROM dbo.ReseedTest rt
DELETE dbo.ReseedTest -- Delete row 1, do not reset counter (which is at 1)

-- This row will have ID 2.
INSERT INTO dbo.ReseedTest(dbo.ReseedTest.ReseedText) VALUES(N'2') -- Insert row 2. 
SELECT * FROM dbo.ReseedTest rt
DELETE dbo.ReseedTest -- Delete row 2, do not reset counter (which is at 2)

-- The next row inserted uses new_reseed_value + the current increment value.
-- This is because we've already inserted rows into this table since its creation and we didn't TRUNCATE it.
DBCC CHECKIDENT('dbo.ReseedTest', RESEED, 0) -- Reset counter to 0. 
INSERT INTO dbo.ReseedTest(dbo.ReseedTest.ReseedText) VALUES(N'3') -- Insert row 3 with id 1
SELECT * FROM dbo.ReseedTest rt

-- This time, we will.
TRUNCATE TABLE dbo.ReseedTest -- Reset to PKID 1.

-- Current identity value is set to the new_reseed_value. 
-- If no rows have been inserted into the table since the table was created, 
-- or if all rows have been removed by using the TRUNCATE TABLE statement, 
-- the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. 
-- Otherwise, the next row inserted uses new_reseed_value + the current increment value.
DBCC CHECKIDENT('dbo.ReseedTest', RESEED, 0)

-- This row will have PKID 0.
INSERT INTO dbo.ReseedTest(dbo.ReseedTest.ReseedText) VALUES(N'1')
SELECT * FROM dbo.ReseedTest rt
GO

DROP TABLE dbo.ReseedTest
GO