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 12»»

RESEED in SQL 2000 and SQL 2005/2008 Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2008 12:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, Visits: 299
Comments posted to this topic are about the item RESEED in SQL 2000 and SQL 2005/2008

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
Post #503309
Posted Tuesday, May 20, 2008 12:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
good thing to know ...thnx!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #503322
Posted Tuesday, May 20, 2008 1:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:56 AM
Points: 12, Visits: 68
Hi,

I have implemented the same thing but there was no discrepancy i mean after executing following command with Truncate
Dbcc CheckIdent('dbo.reseedtest','RESeed',0) the result was same for with SQL 200 and SQL 2005.
Post #503352
Posted Tuesday, May 20, 2008 3:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
I also got no discrepiancy while testing in 2005 & 2000.
in both the cases after truncating the table the seed started with 0.


"Keep Trying"
Post #503384
Posted Tuesday, May 20, 2008 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 9:34 AM
Points: 8, Visits: 123
From my experience the behaviour in both SQL 2000 and 2005 is exactly the same.
A few years ago I was actually surprised that I was getting different results when calling RESEED on brand new table as opposed to the table that had some date previously inserted and then deleted. And that was when I was using SQL 2000.
Post #503405
Posted Tuesday, May 20, 2008 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 14,835, Visits: 27,311
Nice one AJ.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #503513
Posted Tuesday, May 20, 2008 6:17 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: 2 days ago @ 9:33 AM
Points: 821, Visits: 833
This has been a headache for me, not knowing whether a table is going to reseed at 1 (virgin) or 2. Every technique I've tried to determine the "virginity" of a table has failed, or provided inconsistent results. Fortunately for me, it's not a deal-breaker in our system -- none of our identity values are visible to the user so it really doesn't matter if the first record comes in as 2 instead of 1. But the anal-retentive in me isn't very happy about it.

Ron Moses
ConEst Software Systems


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #503545
Posted Tuesday, May 20, 2008 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:15 AM
Points: 1,242, Visits: 1,546

Sorry! it's not working..

Can any one tell me differnece between "NORESEED" and "SEED" WITH Example.?



thx in advance...
VinayK




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #503663
Posted Tuesday, May 20, 2008 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 12:06 PM
Points: 19, Visits: 196
AJ, could the problem be in your script? I haven't tried the code myself, but the original table definition has an identity seed of 1, and the RESEED command used later on has a seed value of 0.
Post #503791
Posted Tuesday, May 20, 2008 9:28 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, Visits: 299
All,

My bad.

It could be my script...coz I tested a similar scenario on fellow colleagues' machines with successful results and only then did I decide to write the article. While I find out what gave me different results...I agree that the behavior is same for both 2000 & 2005/2008. My search continues as to what changed between then & now.

A few found the virgin tables & truncate's effect on identity useful...that's a mild consolation.


The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
Post #503815
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse