TSQL

  • HBhagat

    Hall of Fame

    Points: 3284

    Comments posted to this topic are about the item TSQL

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    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: 760

    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: 143327

    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

    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

    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: 143327

    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

    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.

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

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