Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

What will the max id be? Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 3:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
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

Post #1398262
Posted Wednesday, December 19, 2012 3:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
palotaiarpad (12/19/2012)
For me is a possible source of inconsitency.


Not sure why you said this as "inconsistency"...it's just the property of TRUNCATE command!


~ 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

Post #1398263
Posted Wednesday, December 19, 2012 3:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 4,121, Visits: 5,488
Good back-to-basics question, Thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1398279
Posted Wednesday, December 19, 2012 4:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
bcoz it is its behavior that every time we execute TRUNCATE command it will reset the identity value..



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1398283
Posted Wednesday, December 19, 2012 4:34 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for a nice easy Wednesday question.
Post #1398299
Posted Wednesday, December 19, 2012 4:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Post #1398304
Posted Wednesday, December 19, 2012 5:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:19 AM
Points: 981, Visits: 565
Easy one.. Remembering basics...

--
Dineshbabu
Desire to learn new things..
Post #1398322
Posted Wednesday, December 19, 2012 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
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
Post #1398331
Posted Wednesday, December 19, 2012 5:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 298, Visits: 459
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.
Post #1398336
Posted Wednesday, December 19, 2012 7:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 2,150, Visits: 2,152
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.
Post #1398377
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse