Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 9:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
Comments posted to this topic are about the item TSQL
Post #641350
Posted Thursday, January 22, 2009 2:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 25, 2014 10:35 AM
Points: 1,385, Visits: 1,241
I don't mean to be overly picky (it's a good question, the intention was clear, and I got my point :) ), but Truncate Table will not necessarily reset the value to 1, it will reset it to whatever was selected as the Seed value in the table declaration.




http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #641430
Posted Thursday, January 22, 2009 3:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:43 AM
Points: 217, Visits: 170
Tao Klerks (1/22/2009)
I don't mean to be overly picky (it's a good question, the intention was clear, and I got my point :) ), but Truncate Table will not necessarily reset the value to 1, it will reset it to whatever was selected as the Seed value in the table declaration.


That is definitley something to remember: the identity will be reset to whatever the seed was defined with, and only if no seed was defined will the default value of 1 be used for the column counter.

Thanks Tao
Post #641480
Posted Thursday, January 22, 2009 6:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
I think that the second choice would be better stated as "Delete * From Table".

The problem is, the "Delete table" choice could mean the "Delete" table menu selection from SSMS, which implies a Drop and Recreate.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #641669
Posted Thursday, January 22, 2009 8:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:44 PM
Points: 1,520, Visits: 739
Hey... I was aware that Truncte will reset the identity, I thought there could be an undocumented stuff on 2008( i have not installed) which resets the identity with delete command(till now its not supported). so choosen wrong one ...

I think the question should be something like this:

Truncate Table TableName -> starts the identity column value from 1



Thanx.
Vinay


http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
Post #641757
Posted Friday, January 23, 2009 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 2,607, Visits: 17,903
RBarryYoung (1/22/2009)
I think that the second choice would be better stated as "Delete * From Table".

The problem is, the "Delete table" choice could mean the "Delete" table menu selection from SSMS, which implies a Drop and Recreate.


When I saw "DELETE TABLE", I thought "DROP TABLE". Even though I don't personally ever write it this way, DELETE tablename is valid syntax and will remove all the rows.

<sotto voce>DELETE * from tablename isn't valid syntax and will throw an error.... sorry Barry! Oversight on the *, I'm sure</sotto voce>
Post #642518
Posted Friday, January 23, 2009 7:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Heh. Good point.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #642882
Posted Friday, January 30, 2009 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 8:17 AM
Points: 46, Visits: 14
Both syntaxes "DELETE tablename" and "DELETE FROM tablename" are correct in T-SQL, as far as I'm concerned.

Another problem of using "DELETE" as a substitute of TRUNCATE is that the DELETE will generate a transaction that will fill the log (specially if the table is a big one...).

A limitation of TRUNCATE (as well as DELETE) is that it will generate an error if the table has a relational integrity (as the parent table) with another one (as the child).

If TRUNCATE for any reason cannot be used and you decide using DELETE, then you can also use the DBCC CHECKIDENT to make the proper seed's reset:

DBCC CHECKIDENT ('owner.tablename', RESEED, seed_value)

Thanks.
Post #646980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse