TSQL

Question

TSQL

HBhagat

Hall of Fame

Points: 3341
More actions
January 21, 2009 at 9:01 pm

#200320

Comments posted to this topic are about the item TSQL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 1

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.

Marius Els Right there with Babe Points: 763 More actions · Answer 2

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 4

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:hehe: ...

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 SSChampion Points: 11610 More actions · Answer 5

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 SSC Guru Points: 143329 More actions · Answer 6

Heh. Good point.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jcrivera-638025 Valued Member Points: 52 More actions · Answer 7

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.