Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ben Kubicek » What will the max id be?
Add to briefcase
36 posts, Page 2 of 4
««1234»»»

What will the max id be? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Lokesh Vij
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: 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
Post #1398262
Lokesh Vij
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: Today @ 8:24 AM
Points: 1,176, Visits: 1,253
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
Stewart "Arturius" Campbell
Posted Wednesday, December 19, 2012 3:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall 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”
Post #1398279
kapil_kk
Posted Wednesday, December 19, 2012 4:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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..
Post #1398283
(Bob Brown)
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: Friday, April 19, 2013 10:27 AM
Points: 690, Visits: 1,100
Thanks for a nice easy Wednesday question.
Post #1398299
Hugo Kornelis
Posted Wednesday, December 19, 2012 4:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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
Post #1398304
Dineshbabu
Posted Wednesday, December 19, 2012 5:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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..
Post #1398322
L' Eomot Inversé
Posted Wednesday, December 19, 2012 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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)
Post #1398331
Sean of the Lynchmob
Posted Wednesday, December 19, 2012 5:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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.
Post #1398336
Dave62
Posted Wednesday, December 19, 2012 7:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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.
Post #1398377
« Prev Topic | Next Topic »

Add to briefcase
36 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.