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 @ 10:16 PM
Points: 1,227, Visits: 1,356
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 @ 10:16 PM
Points: 1,227, Visits: 1,356
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: Thursday, June 13, 2013 9:46 AM
Points: 3,157, Visits: 4,341
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: Today @ 7:27 AM
Points: 1,127, Visits: 1,274
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: Today @ 6:18 AM
Points: 691, Visits: 1,104
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 @ 3:50 PM
Points: 5,289, Visits: 7,219
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: Wednesday, May 29, 2013 10:36 PM
Points: 773, Visits: 464
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 @ 5:53 PM
Points: 7,179, Visits: 7,276
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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
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: Thursday, May 30, 2013 4:47 AM
Points: 106, Visits: 354
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 @ 8:24 AM
Points: 1,526, Visits: 1,561
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.