sql 2005 PKey design issue

[brin351]

I often make or work on tables with a primary key on them using an auto incrementing number i.e. column properties > identity specificalion = yes, identity increment = 1 etc...

This primary key is then used as a forign key in related tables...

The problem or issue is if the database needs to be moved, backed up , downloaded to my developement machine etc... I need to create the tables constraints etc.. on the new sever then try and fill the tables with records from the old server and I will get forign_key_constaint errors because the auto incriment PK in tables on the new server starts from 1 then 2,3,4 etc...

BUT because records have been deleted the old database might have PK's 1,3,4,6 and the related tables have these as the forign key so the forign_key_constaint errors occurs.

Is there an easy way around this - instead of turning off and on the auto increment and checking the keys/data are the same valid etc..?

OR

Is it BETTER PRACTICE to use manual incrementing PK's instead of auto incrementing PK's. Like when inserting a record manually get the last PK number add 1 and use it - i.e. SET newPK = SELECT TOP 1 pk_field FROM table ORDER BY pk_field DESC; SET newPK = newPK + 1 and use it for the insert.?

OR

Any other advice is appreciated... I run into this problem with websites needing to be moved to a better server, re-developed etc...

[brucevde]

SQL Server has an option called Identity_Insert. When the option is On you can explicitly set the value of the Identity column thus retaining the values from the old table.

Set Identity_Insert TableName ON
Insert statements
Set Identity_Insert TableName OFF

The next Identity value is automatically adjusted (ie Max(id column) + 1)when the option is turned Off.

[brin351]

I have used that feature in the import/export wizard but still ended up with inconsistencies between the two - perhaps it was not due to that. Also when doing it for multipule tables at once regardless of setting that options it's failed, when I've done it for a single table then the dependant table it's worked??? It doesn't make sence to me why unless it tries to insert into the dependant tables first.

[szlamany]

The help file explains that simply...

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

Have you considered using TDF text files with BULK INSERT/KEEP IDENTITY for loading tables from production to test? BULK INSERT also ignores referential integrity errors - so loading tables related by these PK w/AUTO INSERT should be no problem.

[brin351]

Thanks for that szlamany, I know now whats happening.

A guy that works on big business databases suggested a "next number table" with a single record holding the next number and a function to return the number. The reasoning is you can lock the table read the number increment it and unlock it. This is better than getting the last number from the same table because I don't want to lock it for reading to prevent 2 records simultaniously getting the same number. Second is it's independant of database type.

I like this next number approach for tables that need a key generated for them - sql server auto numbers feel like I'm taking an easy way out with consequences.

Thanks again for your input.

[szlamany]

See post #2 in this thread

http://www.vbforums.com/showthread.p...ighlight=batch

for an idea on how to use a "LAST NUMBER" table for getting auto-id's

All without any locking at all - which is always best practice in SQL.

[techgnome]

Why go to all this trouble? You should just be able make a backup, then restore it to a new location.... all with out having to re-load tables or anything.

-tg

[brin351]

See post #2 in this thread

http://www.vbforums.com/showthread.p...ighlight=batch

for an idea on how to use a "LAST NUMBER" table for getting auto-id's

All without any locking at all - which is always best practice in SQL.

Ok so better than locking the last number table to prevent the same number being issued at the same time is to test if the number still exists when updating the table to the next number and if the row was not updateded due to another request at the same time loop and try it again until it is updated.

Is this purely better performance or does locking have potential problems. I'm interested to know thank you.

[szlamany]

In my opinion SQL handles locks internally - based on what it expects is best for the session and the users hittings tables. You really don't have the ability to "force locks" without using LOCKING HINTS on your QUERIES and that generally scares me.

The trick I demonstrated assumes that 99% of the time no one will grab the same row prior to me doing the UPDATE - since I'm in a transaction in a STORED PROCEDURE anyway. So the IF @@ROWCOUNT will most likely never get triggered. In the 1% of the cases where a conflict does arise then the IF @@ROWCOUNT puts me right back up to get the next number.

All that done without asking the engine to lock a row - lock a page - lock anything at all.

But to backup what TECHGNOME has stated - why go through all this trouble? IDENTITY columns are common practice - work fine. The only reason we used the IF @@ROWCOUNT trick was because we had a compound primary key of FISCAL YR+TRANSACTION # and SQL doesn't support IDENTITY columns as the second segment of a primary key (wish they did!).

We always grab entire DATABASES from customer sites to bring back to our development shop using BACKUP and RESTORE.

[brin351]

I did a test of exclusively locking the row and the table for this next number table using query hints and I was scared of my lack of knoledge of what I was actually forcing the database to do. I am how ever still in favour of using a next number table along the lines szlamany suggested.

I have tried "back up" on a remote sever (with management studio 2005) but all I can see is back up the .bak file to the file system which I don't have access to in most cases because it's a shared sql server on a server I don't have ftp/remote desktop access to. So HOW DO I GET THE .bak file to restore to my local machine? Or use it for another remote server that I only have sql management studio access to the database. Is there another type of remote backup?

I can right click and script the database and rebuild it anywhere in a query window then DTS import/export the data - but if I'm faced with numerious tables with identity columns I have to work around when DTS'ing. That annoyes me. Without identity columns I'm freed of that enourmous burden... well I made it a big problem anway...

Thanks for your response