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.