SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
HBhagat
HBhagat
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2038 Visits: 1015
Comments posted to this topic are about the item TSQL
Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2799 Visits: 1249
I don't mean to be overly picky (it's a good question, the intention was clear, and I got my point Smile ), 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.
Marius Els
Marius Els
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 183
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 Smile ), 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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80098 Visits: 9519
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."
Vinay Thakur-585143
Vinay Thakur-585143
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3112 Visits: 801
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 oneHehe ...

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
Chad Crawford
 Chad Crawford
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7273 Visits: 18732
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>
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80098 Visits: 9519
Heh. Good point.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
jcrivera-638025
jcrivera-638025
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search