Resetting Identity Seed/PK''s
-
If you're all telling me that it's normal practice to move from dev to production with the pk gaps included then fine, I'll learn to live with it - no problemo - honest....
LOL - I just stumbled upon this post...2 years ago I had the exact same problem as you Lee...tootling along just fine with the development database - inserting and deleting and testing and developing - when it got to the stage where I was told - let the users start testing...so I make a test copy - gaps and all - the users start testing - by some wonderful miracle (barring a few minor negligible - at that time bugs), - everything ran ok and before I knew it, my client was telling me that the test db is now officially the production db...
was I going to touch that darn thing then...NOT A CHANCE....did I have a problem with the gaps...YES! YES! YES! - stupid to be obsessed with something that wasn't really a problem with anything except the messed up inside of my head - learning to live with it was very hard...but you know what - they say that Time's a great healer...it must be 'cos I'd forgotten all about it until just now!
**ASCII stupid question, get a stupid ANSI !!!** -
Without commenting on whether its a good idea to do this, and stipulating this is only for development (having multiple users running this simultaneously would be a really bad idea), you can reset the seed in two lines:
DBCC CHECKIDENT('table',RESEED,0)
DBCC CHECKIDENT('table',RESEED)
-
Sorry for not getting back earlier out of office yesterday.
A big thanks to everyone that has responded to this thread, you've put my mind at rest regarding this now seemingly rediculous notion of mine - lesson learned: leave the damn thing alone!
Chris >>
Why is having gaps in the sequence anything less than perfect?I now know it is
It is more or less normal practice yes, or at least it is not normal practice removing gaps. But that is depending on how people move from dev to production. Should you really have data in dev that is moved to production? And especially with automatically generated surrogate keys?
- More or less normal practice -
- Automatically generated id's, hmmm, raises a question. Should I change my approach and not rely upon this, and build my own id generator type sp? Just thinking about this.......
......so if I had a table with a column to hold my id's and table name, wrapped it into a transaction, would this be the way to go???
Something like this mebbe:
CREATE PROCEDURE spIDGenerator(@tbl_name VARCHAR(32), @id_increment INT, @id_returned INT OUTPUT)
AS
BEGIN TRAN
IF NOT EXISTS(SELECT col_ids FROM tbl_ids WHERE col_tbl_name = @tbl_name)
BEGIN
INSERT INTO tbl_ids (col_tbl_name, col_ids) VALUES(@tbl_name, 1 + @id_increment)
END
BEGIN
UPDATE tbl_ids SET col_ids = col_ids + @id_increment
WHERE col_tbl_name = @tbl_name
SELECT @id_returned = (col_ids - @id_increment) FROM tbl_ids WHERE col_tbl_name = @tbl_name
END
COMMIT TRAN;
GO
Michael >>
SET IDENTITY_INSERT
Yes I've used this in the past, thanks for the memory jogger
Sushila >>
Thank you very much, empathy at last
I was starting think I was really weird, nice to know there's more than one of us out there 
Looks like I'll be following in your footsteps.
Scott >>
DBCC CHECKIDENT('table',RESEED,0)
DBCC CHECKIDENT('table',RESEED)
Hmmm, I was playing around with this in the test script I posted earler when trying to 'fix' this 'problem'
Can you clarify what the second line will do when not setting a seed value? AHHHHHHHH, don't worry, just found out - smacks self on head (need a emoticon for that)! It does what my script did!
Big thanks for that one Scott, prolly wont' use it now though I've learnt my lesson
Finally >> Thanks again to all, bye for now!
- More or less normal practice -
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply