Reseed IDENTITY columns using TRUNCATE

Posted: August 11, 2010 in SQL Server 2005

A quick want to reseed the identity columns in a table is to use the TRUNCATE command. Using the DELETE command will NOT set the IDENTITY column back. You can see the way these command behave using the example below. NOTE: There is a place for each of these commands so use as required.

-- Create temp table 1
CREATE TABLE #Test1 (col1 INT IDENTITY, col2 VARCHAR(5));

-- Insert records into temp table 1
INSERT INTO #Test1 (col2) VALUES ('a');
INSERT INTO #Test1 (col2) VALUES ('b');
INSERT INTO #Test1 (col2) VALUES ('c');

-- Create temp table 2
CREATE TABLE #Test2 (col1 INT IDENTITY, col2 VARCHAR(5));

-- Insert records into temp table 2
INSERT INTO #Test2 (col2) VALUES ('a');
INSERT INTO #Test2 (col2) VALUES ('b');
INSERT INTO #Test2 (col2) VALUES ('c');

-- Select from temp tables
SELECT * FROM #Test1;
SELECT * FROM #Test2;

-- Delete from table 1
DELETE FROM #Test1;

-- Delete from table 2
TRUNCATE TABLE #Test2;

-- Insert records into temp table 1 to check what identity value is set
INSERT INTO #Test1 (col2) VALUES ('a');
INSERT INTO #Test1 (col2) VALUES ('b');
INSERT INTO #Test1 (col2) VALUES ('c');

-- Insert records into temp table 2 to check what identity value is set
INSERT INTO #Test2 (col2) VALUES ('a');
INSERT INTO #Test2 (col2) VALUES ('b');
INSERT INTO #Test2 (col2) VALUES ('c');

-- Select from temp tables
SELECT * FROM #Test1;
SELECT * FROM #Test2;

-- Drop temp tables
DROP TABLE #Test1;
DROP TABLE #Test2;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s