TRUNCATE TABLE (@Parameter) - by walkair

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
1
Sign in
to vote
ID 331444 Comments
Status Closed Workarounds
Type Suggestion Repros 2
Opened 3/4/2008 5:31:32 PM
Access Restriction Public

Description

TRUNCATE TABLE doesn't support table parameter, however DELETE, TRUNCATE's "slower sibling" does.
Sign in to post a comment.
Posted by Microsoft on 3/23/2010 at 5:59 PM
Closed the request.

--
Umachandar
Posted by Microsoft on 3/23/2010 at 5:02 PM
Hi,
I am resolving and closing this request as "won't fix". There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 4/17/2008 at 2:44 PM
Hi,
Thanks for the clarification. You are looking for truncate table to work with table variables. This is currently not supported. Table variables in general use less logging and the perf difference between delete and truncate may not be that significant unless lots of rows are involved. However, this does seem like a valid enhancement suggestion. We will investigate this issue for a future release of SQL Server (beyond SQL Server 2008, which is close to the release point).

Srini Acharya, SQL Engine
Posted by walkair on 3/7/2008 at 3:07 PM
OK - please follow this example:

Working
CREATE TABLE #temp (ID INT)
TRUNCATE TABLE #temp
DELETE FROM #temp

declare @TBL TABLE (ID INT)
DELETE FROM @TBL

NOT working
declare @TBL TABLE (ID INT)
TRUNCATE TABLE @TBL

May be TRUNCATE is not designed for that.
As of today, one of the ways to increase performance is by replacing a temporary tables with table variables (primarily in stored procedures). However by doing so, we are loosing a faster execution of TRUNCATE since it can not accept table variable and will be replaced by DELETE.
Posted by Microsoft on 3/5/2008 at 3:31 PM
Hi,
Thanks for your feedback. Truncate Table is a T SQL command that takes in a table name as its argument. i.e. Truncate table <table name>. This is faster than doing a "Delete" oepration on the table. It is not clear if you are referring to this command. Can you please clarify?

thanks
-- srini Acharya
Relational Engine