RESEED in SQL 2000 and SQL 2005/2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by AJ » RESEED in SQL 2000 and SQL 2005/2008

16 posts, Page 1 of 2

1 2 »»

RESEED in SQL 2000 and SQL 2005/2008

Rate Topic

Display Mode

Topic Options

Author

Message

Raghuram (AJ)

Posted Tuesday, May 20, 2008 12:02 AM

SSC-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

Dugi

Posted Tuesday, May 20, 2008 12:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:48 AM
Points: 1,252, Visits: 3,367

good thing to know ...thnx! Hehe

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #503322

Sunil Kumar-659768

Posted Tuesday, May 20, 2008 1:37 AM

Grasshopper

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

ChiragNS

Posted Tuesday, May 20, 2008 3:04 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

krzysztof-202150

Posted Tuesday, May 20, 2008 3:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 08, 2011 2:40 AM
Points: 8, Visits: 104

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

Grant Fritchey

Posted Tuesday, May 20, 2008 5:45 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

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

ronmoses

Posted Tuesday, May 20, 2008 6:17 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:09 AM
Points: 648, Visits: 684

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

Danny Ocean

Posted Tuesday, May 20, 2008 7:47 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 1,074, Visits: 1,130

Sorry! it's not working..

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

thx in advance...
VinayK

Keep Learning - Keep Growing !!!
http://growwithsql.blogspot.in

Thanks
Vinay Kumar

Post #503663

Kit Brandner

Posted Tuesday, May 20, 2008 9:03 AM

Grasshopper

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

Raghuram (AJ)

Posted Tuesday, May 20, 2008 9:28 AM

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

16 posts, Page 1 of 2

1 2 »»

Permissions