Heh... a better cautionary note would be to never ever use something that you don't know the ramifications of. It clearly states the following in Books Online under "Truncate Table".

Contrary to what that note states, it IS possible to take advantage of the minimal logging and guaranteed removal of all pages that TRUNCATE TABLE has over mere DELETE's provided you've met the conditions for TRUNCATE TABLE to run (also in Books Online, the "help" system that comes with SQL Server). Here's how...

First, we need a test table to demo with.

--=============================================================================

-- Create a test table

--=============================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL DROP TABLE #MyHead

;

--===== Create the empty test table

CREATE TABLE #MyHead

(

SomeID INT IDENTITY(1,1),

SomeData UNIQUEIDENTIFIER

)

;

And now, one possible solution. As always, the details of how it works are in the code. Run the following as may times as you want to see that the sequence of the IDENTITY column is preserved.

/******************************************************************************

Demonstrate how to truncate a table while preserving the continuous value

of the IDENTITY column. Run this as many times as you want.

******************************************************************************/

--=============================================================================

-- Truncate the table and preserve the IDENTITY seed so that it will

-- continue numbering from where it left off.

--=============================================================================

--===== First, show the current MIN and MAX values of the IDENTITY column

-- just so we can see that this all works correctly. This is NOT a part

-- of the solution. This will obviously return NULLs the first time

-- it's executed because the table is empty.

SELECT MinSomeID = MIN(SomeID),

MaxSomeID = MAX(SomeID)

FROM #MyHead

;

--===== Declare an obviously named variable

DECLARE @NewSeed INT

;

--===== Begin a transaction and get the new seed while locking the table to

-- prevent anyone from sneaking in on the process.

-- The ISNULL is to handle the table when it first comes into existance

-- which is not the case here but is included for completeness of the

-- solution.

BEGIN TRANSACTION

;

--===== Using ISNULL on IDENT_CURRENT doesn't seem to work on my machine

-- so we'll fix that in a minute. In the meantime, get the current

-- value of the IDENTITY. The TABLOCKX will cause the code to wait

-- until there's nothing using the table.

SELECT TOP 1

@NewSeed = IDENT_CURRENT('#MyHead')

FROM #MyHead WITH (TABLOCKX)

;

--===== Now, increment that value current IDENTITY value to be used as a RESEED

SELECT @NewSeed = ISNULL(@NewSeed,0)+1

;

--===== Empty the table. This will be much faster and cause nearly 0 logging

-- compared to doing a delete.

TRUNCATE TABLE #MyHead

;

--===== Since TRUNCATE reseeds the IDENTITY column with it's original value,

-- we need to reseed it with the expected continuation value.

DBCC CHECKIDENT ('#MyHead', RESEED, @NewSeed)

;

--===== Let other people use the table with the new continued seed.

COMMIT

;

--=============================================================================

-- Insert some data into the trucated table and note the we were able to

-- continue the sequence of the IDENTITY value.

--=============================================================================

--===== Insert the test data

INSERT INTO #MyHead

(SomeData)

SELECT TOP 100

SomeData = NEWID()

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Demonstrate that the sequence of the IDENTITY column continued.

-- This is NOT a part of the solution.

SELECT MinSomeID = MIN(SomeID),

MaxSomeID = MAX(SomeID)

FROM #MyHead

;