SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Numeric Primary Keys
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/12/2001 :  21:08:57  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Tim writes "The most obvious thing to do for records that have no unique column/s is to create an int identity column. For example, I have a 'address' table that should have a unique address_id for each record. Now, normally, I would configure a column address_id as 'int identity(1,1) primary key'. But, for theoretical sakes, what happens if one day, I hit the max value for an int (2^31-1). The next time I add a record, I'll get error 8115 (Arithmetic overflow). During this time though, many records have been deleted leaving holes in possible address_id values which I can reuse. How can I effectively reuse these values? Any ideas?"

Article Link.

D.Poole
Starting Member

United Kingdom
1 Posts

Posted - 06/22/2001 :  07:39:26  Show Profile  Reply with Quote
The thought of re-using AddressId's fills me with dread! What happens if you want to retrieve archived data?
If you must do it then it can be done by using
SET IDENTITY_INSERT {TableName} ON
INSERT {Table} ( {FieldNames} )
VALUES ( {ValueList} )
SET IDENTITY_INSERT {TableName} OFF

Given that there are 4 billion people in the world and by far fewer addresses, how likely are you to run out of ids in an address table?

Go to Top of Page

JohnNowak
Starting Member

New Zealand
2 Posts

Posted - 06/29/2001 :  01:58:25  Show Profile  Reply with Quote
My take on a similar scenario was to add another column (bit) marking if the record was valid or not - whenever a record is to be removed the bit gets flipped (and the record is not delete - though key values may be removed). When a new record is added, first I check the table for the first non-valid record it finds (e.g. select top 1 addressID from address where valid = 0) and then updates that field. If it doesn't find a value then add a new record.

This way, no current/active fields are changed.

Any comments on whether there is a better way of doing this than what I am doing? It doesn't seem to inefficient (or any worse than other options I could come up with).

Address may be a bad examples but there are times when insufficient ID's is a concern.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/29/2001 :  09:49:44  Show Profile  Visit nr's Homepage  Reply with Quote
This is ok for current data but can be a pain when you want to look at historic data and have to find the corresponding backups table to get get the correct address.

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 07/11/2001 :  12:49:48  Show Profile  Reply with Quote
Depending on how the table was used I would regularly drop the identity column and recreate it. This is a quick and dirty way to reclaim the holes.

I end up doing this a lot because the SQL tables I use are from a thrid party application and they look down on adding columns to their tables. So when I need to run through the tables sequentially, which is a lot, I simply alter the table by adding a row Id and when I'm done I drop the column.

adding an index on the row id helps.

Cat

Go to Top of Page

Dave Bach
Starting Member

USA
1 Posts

Posted - 07/25/2001 :  13:59:48  Show Profile  Reply with Quote
Having done Identity in the past I found using the GUID a simpler solution and resolves INSERTS of parent and child rows.

I use "SELECT newid() AS id" that will give me a new GUID and then use it in the INSERT statements.

Used GUID on all tables in latest e-Commerce project and found it to be the best fit.
Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 07/25/2001 :  14:44:37  Show Profile  Reply with Quote
I generate my own GUID, by the following:

replace(convert(char,getdate(),121),' ',':')

Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/26/2001 :  09:01:56  Show Profile  Reply with Quote
Probably not likely but what if two new records are created in the same millisecond?
I assume newid() uses the current date/time as well


Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 07/26/2001 :  09:16:34  Show Profile  Reply with Quote
Ya its not likely, but if it were to happen, one record would get kicked out. When I obtain the guid I insert it into a table where the guid is the PK. If the same guid is obtained a second time, when it tries to insert, it will fail. One process has to be served first right? Anyway, in my situation thats okay because I am using this as a sessionID tracker, so the user would get an error message and they would try to log in again.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000