|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,176,
Visits: 1,253
|
|
palotaiarpad (12/19/2012) I'm also wrong, but i learned something. Could someone please explain, why is the identity resetted after a truncate? For me is a possible source of inconsitency.
TRUNCATE command apart from removing data from the table, resets the seed value of the identity column defined in the table.
Another command to re-seed identity is DBCC CHECKIDENT. It can be used something like below:
DBCC CHECKIDENT ('test_trun', RESEED, 0)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com
Follow me @Twitter

|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,176,
Visits: 1,253
|
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129,
Visits: 4,312
|
|
Good back-to-basics question, Thanks
____________________________________________ Space, the final frontier? not any more... All limits henceforth are self-imposed. “libera tute vulgaris ex”
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061,
Visits: 1,151
|
|
bcoz it is its behavior that every time we execute TRUNCATE command it will reset the identity value..
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Friday, April 19, 2013 10:27 AM
Points: 690,
Visits: 1,100
|
|
| Thanks for a nice easy Wednesday question.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 5,243,
Visits: 7,055
|
|
palotaiarpad (12/19/2012) Could someone please explain, why is the identity resetted after a truncate? Because conceptually, TRUNCATE TABLE is closer to DROP / CREATE than to DELETE.
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 761,
Visits: 446
|
|
Easy one.. Remembering basics...
-- Dineshbabu Desire to learn new things..
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,102,
Visits: 7,165
|
|
SQL Kiwi (12/19/2012)
Another good one. 19% wrong right now  Yes, a good one. And in the last 6 hours the wrong rate has grown from 19% to 27%, which seems rather high for such an easy question.
Tom Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:02 AM
Points: 100,
Visits: 353
|
|
palotaiarpad (12/19/2012) why is the identity resetted after a truncate? For me is a possible source of inconsitency.
As Lokesh has already explained, resetting the identity seed is a characteristic of the 'truncate' command. If you want to retain the seed then you use 'delete' instead. So, there's no inconsistency, it is just a case of using the right command according to your needs.
At least you learnt something, so from that point of view it is a good question.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 1,500,
Visits: 1,512
|
|
palotaiarpad (12/19/2012) I'm also wrong, but i learned something. Could someone please explain, why is the identity resetted after a truncate? For me is a possible source of inconsitency.
Hello, if resetting the identity is a possible source of inconsistency you may try using DELETE instead of TRUNCATE. DELETE will remove the data without resetting the identity.
|
|
|
|