Truncate/clear table variable in SQL Server 2008
For clearing a table variable in SQL Server 2008, utilize the DELETE command:
Beware, this clears all rows, but omits resetting identity columns. If you require identity reset, you'll have to redeclare the table variable.
Stop right there!✋ TRUNCATE TABLE does not apply to table variables. If you're dealing with a temporary table, however, TRUNCATE TABLE #TempTableName; is your friend.
Going beyond the basics - Table variables, loops, and you
Why should I clear table variables anyway?
If you like clean, precise data within your WHILE loops—clearing out table variables is your move. Especially if your stored procedures or scripts are pulling off multiple operations.
Alright, but DELETE or TRUNCATE?
Here's the kicker. DELETE is logged—each row removal is jotted in the transaction log like a diligent diary entry. This can hit performance if you're dancing with large data sets. TRUNCATE, in contrast, less-logged, nodeals with page deallocations—quite the smooth criminal with potential performance benefits.
Know your tools - Table variables vs Temp tables
Why not forgo table variables and join the #TempTables club?
Although #TempTables seem to come with all the cool features, it's not a one-size-fits-all. Know your data! Table variables show up for small data sets with lower overhead and memory storage—like that nifty pocket knife you carry around.
But, performance is king!
Agreed! If you adore the need for speed and happen to juggle large data, give a nod to #TempTables. Yet notice, DELETE tends to create a little fuss with enlarging the transaction log size, which can degrade performance. So, if performance is your aim—temp tables are your game!
A strategic plan for Adaptation and optimization
Handling the Identity crisis in table variables
Dealing with identity columns? If you're teaming up with table variables, redeclaration is your only hope. Lucky for you #TempTables are a little friendlier—TRUNCATE these tables and manage identity columns like a pro with DBCC CHECKIDENT.
Reaping benefits of temp tables caching
In the realm of stored procedures, SQL Server dishes out a neat trick of caching temp tables. So, you've hit the jackpot with reduced compilation overhead as it retains the metadata. As DBA Granny says, "Less overhead, more tea time!"
Keeping clarity in your code - especially in loops
Putting your best foot forward in batch processing is all about making sure that only the new data gets an invite to the party. Using DELETE FROM @tableVariable ensures no old data crashes your party.
Was this article helpful?