Presents your 
SQL SERVER E-NEWSLETTER for December 5, 2002

<------------------------------------------->

KNOW THE DIFFERENCE BETWEEN DELETE AND TRUNCATE

In SQL Server, when you're numbering the identity value in a table,
there is a significant difference between the TRUNCATE and DELETE 
commands.
The TRUNCATE command will reseed the identity property, while the 
DELETE command will maintain the identity property value.

For example, you may have a table that is logging information for a
specific duration of time (day, week, month, etc.). Once that duration 
is completed, you want the identity value to start at one again.

In this situation, using the TRUNCATE TABLE command is simple and sets
the new identity value to one. The TRUNCATE command is a logged command
and, therefore, does not delete the rows as transactions, but 
de-allocates the pages containing data for that table. (Note that you cannot 
truncate a table that has another table referencing it through a foreign key.)

On the other hand, the DELETE command will retain the identity value. 
If you have a table with the identity ranging from 1 to 15 inclusive and
you delete all the rows, the next insert will take the identity value 
of 16.

The following is a sample script that demonstrates the different effects
the DELETE and TRUNCATE commands have on the identity values of a table.

SET NOCOUNT ON
GO
CREATE TABLE Reseed
(pkey1 INT IDENTITY NOT NULL,
col1 INT NULL)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
PRINT REPLICATE('+',80)
PRINT 'Created Reseed table and inserted rows.'
GO
INSERT Reseed SELECT col1 FROM Reseed
GO
SELECT pkey1 FROM Reseed
GO
DELETE Reseed
GO
PRINT REPLICATE('+',80)
PRINT 'Deleted Reseed.'
GO
SELECT pkey1 FROM Reseed
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed SELECT col1 FROM Reseed
GO
PRINT REPLICATE('+',80)
PRINT 'Inserted rows after deleted rows.'
GO
SELECT pkey1 FROM Reseed
GO
DELETE Reseed
GO
DBCC CHECKIDENT ( 'Reseed' , RESEED , 0 )
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed SELECT col1 FROM Reseed
GO
PRINT REPLICATE('+',80)
PRINT 'Inserted rows after DBCC RESEED.'
GO
SELECT pkey1 FROM Reseed
GO
TRUNCATE TABLE Reseed
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed (col1) VALUES ( 1)
GO
INSERT Reseed SELECT col1 FROM Reseed
GO
PRINT REPLICATE('+',80)
PRINT 'Inserted rows after Reseed table was truncated.'
GO
SELECT pkey1 FROM Reseed
GO
DROP TABLE Reseed
GO

----------------------------------------