Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
HBhagat
HBhagat
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 915
Comments posted to this topic are about the item TSQL
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 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 Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 179
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
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1640 Visits: 769
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2436 Visits: 18644
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
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
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 (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

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