Truncate/Clear table variable in SQL Server 2008
Solution 1
just delete everything
DELETE FROM @tableVariable
Solution 2
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.
Solution 3
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.
Solution 4
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.
shrekDeep
Updated on July 05, 2022Comments
-
shrekDeep about 2 years
Is it possible to truncate or flush out a table variable in SQL Server 2008?
declare @tableVariable table ( id int, value varchar(20) ) while @start <= @stop begin insert into @tableVariable(id, value) select id , value from xTable where id = @start --Use @tableVariable --@tableVariable should be flushed out of -- old values before inserting new values set @start = @start + 1 end
-
aelveborn almost 9 yearsWhy do you need to create a separate procedure to execute a truncate? How are you going to ensure concurrency given that you keep creating and dropping the procedure with the same name? And most importantly, how are you going to use that for table variables?
-
zanlok almost 8 yearsA better answer would be "you can't" and then explain the alternative.
-
Kiran Kotla almost 8 yearsYou can just use
DELETE FROM @tableVariable
, as described in the accepted answer, to get functionality substantially equivalent toTRUNCATE TABLE
(except for the logging - this could certainly be a problem if there were a lot of rows in the variable, or the SQL that created the variable was being run very often). -
Kiran Kotla almost 8 yearsThis will work but bear in mind, as @Martin%20Smith has pointed out below, that this deletion will be logged. If there a lot of rows in the variable, or this SQL will be executed regularly, this could be problematic.
-
Martin Smith almost 8 years@BartRead - The advantage
TRUNCATE
has overDELETE
of all rows is transaction logging. -
Akira Yamamoto over 5 yearsIt can't be faster if you can't truncate. There's just the delete option.
-
knuckles about 2 yearsThis answer is not relevant to the question. A table variable does not refer to a table name stored as a string, it refers to a variable of type
TABLE
. Table variables are in-memory only and exist only for the duration of the session in which they are declared. They can be used instead of temp tables in some circumstances, and can be passed as parameters to stored procedures. See official documentation for details.