Truncate/Clear table variable in SQL Server 2008

just delete everything

DELETE FROM @tableVariable

No, you cannot TRUNCATE a table variable since it is not a physical table. Deleting it would be faster. See this answer from Aaron Bertrand.

I'd add to the "technically" correct answer of using DELETE @VariableTable that if you happen to also have an Identity-Field in your @Table Variable (e.g. i int (1,1)) and you'd like to re-use this table (even if you re-declare it in a loop) it is still within scope and there it no way to reseed it either.

See: Table Variable Identity Column

It is best to use #TempTable in these cases - then you may Truncate or use DBCC to reseed.
You will reap performance improvements with Truncate and be able to create additional indexes.
I think the rule of thumb is, if you're ever going to delete everything using DELETE @VariableTable, then you've introduced a code-smell that says, you should have used #TempTable and TRUNCATE instead.

Table variables do not support TRUNCATE syntax - the only way of truncating them is implicitly by letting them fall out of scope.

Both temporary tables and table variables can be cached when used in stored procedures and the below may well end up with the same table variable being used after truncation rather than an actual drop and create

CREATE PROC dbo.foo @start INT
AS
  BEGIN
      DECLARE @tableVariable TABLE (
        id    INT,
        value VARCHAR(20))

      INSERT INTO @tableVariable
                  (id,
                   value)
      SELECT id,
             value
      FROM   xTable
      WHERE  id = @start;
  --Use @tableVariable 
  END

GO

WHILE @start <= @stop
  BEGIN
      EXEC dbo.foo @start

      SET @start = @start + 1
  END

Of course a far easier alternative would be to switch to using a #temp table instead as that supports TRUNCATE directly.

DML on both table variables and temp tables writes to the tempdb transaction log. Whether or not it is worth switching to TRUNCATE rather than DELETE depends on the size of data involved. TRUNCATE will just log the page deallocations. DELETE will log the actual deleted values. One other difference between the two is that TRUNCATE deallocates the last page from the table and DELETE doesn't. If only a small quantity of data is inserted and deleted in each loop iteration then the overhead from logging the deleted rows can be less than the overhead from constantly deallocating and reallocating the single page in the table.

Conversely if you will be inserting and deleting large amounts of data on each iteration you may find that TRUNCATE not only makes the operation of deleting all rows more efficient but also can benefit the subsequent insert statement.

Truncate/Clear table variable in SQL Server 2008

Related

Recent Posts