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.

Tags:

Sql

Sql Server