Search

TRUNCATE TABLE (@Parameter) by walkair

Closed
as Won't Fix Help for as Won't Fix

2
1
Sign in
 to vote
Type: Suggestion
ID: 331444
Opened: 3/4/2008 5:31:32 PM
Access Restriction: Public
3
Workaround(s)
TRUNCATE TABLE doesn't support table parameter, however DELETE, TRUNCATE's "slower sibling" does.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

Create support for table parameter for TRUNCATE TABLE function
Example:
DECLARE @Parameter TABLE ( itemID INT )
TRUNCATE TABLE (@Parameter)

Benefits

Improved Performance
Improved Reliability
Faster Development

Other Benefits

 
File Attachments
0 attachments
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
Sign in to post a workaround.
Posted by AaronBertrand on 3/5/2008 at 3:39 PM
If you need the functionality of TRUNCATE due to re-generating IDENTITY keys in a loop, first of all examine your design, but if you are stuck, just use a #temp table instead.
Posted by AaronBertrand on 3/5/2008 at 3:38 PM
DELETE is not slower on table variables. DELETE is slower because it is logged... table variables are not logged, therefore DELETE is the same cost.
Posted by walkair on 3/4/2008 at 5:34 PM
Use DELETE
Disadvantages: slower + use more resources
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement