Need an alternative to identity field as primary key 1

[jmille34]

What are the good alternatives to using identity fields as primary keys? Rowguid maybe? Identity fields just mess up everything when it comes to replication. We make extensive use of "select Scope_Identity()" throughout my application. Is there something similar to pull the rowguid? The code generally looks something like this:

set nocount on;
INSERT INTO table (column) values (value);
select Scope_Identity() as id;

If there were a direct replacement for that 3rd statement it would make migrating much easier. We're still in the planning stages and are not committed to this solution by any means, so if I'm completely barking up the wrong tree, please let me know. If rowguid is the way to go, can the field be named anything we want, such as using it as the same name as the old identity fields?

The problem we're facing is that sql server has what seems to be to be a very kludgy way of handling replication on tables with identity fields. In general if you have db replication between two synchronized servers and then you do simultaneous inserts on each server, they will get the same identity field and step on each other. That also means I can't just lock the table, get the highest id, add 1 to it, and use that value as the next id field, because if I did that on both servers simultaneously, I'd still be in the same pickle. I need something with a degree of randomness to it, and since the replication function likes to put a rowguid column into all my tables during replication setup anyway, it implies to me that it might make a good primary key. Yes, no, maybe?

A buddy suggested squashing together the datetime (down to the millisecond) and appending a few random digits. That sounds even more like a kludge, but I'm negotiable.

Also note that we're pretty used to the speed of doing lookups by identity field, so I hope changing isn't going to make database access much slower. But again, I'm negotiable.

 

[gmmastros]

If you want to use a GUID, that's fine.

When using identity, you normally let the database determine the next number. Not so with a guid.

Ex:

Declare @IdGuid uniqueidentifier

Set @idGuid = NewId()

Insert Into Table(IdColumn, DataColumn) Values(@IdGuid, 'Some Data')

Basically, you create a new guid and then insert that in to the database. Make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[jmille34]

Are there any caveats to using a guid for the primary key? How unique is unique? Something like one in a million if two inserts are within 1 second of each other or something like that?

 

[SQLDenis]

it is unique accross the globe, that is 2 GUIDs will never be the same ever

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Microsoft guarantee that GUIDs are world wide unique

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jmille34]

If Microsoft says it, that's good enough for me!

Thanks guys this is a big step in the right direction for me. Stars are on the way.

 

[ESquared]

Going entirely from memory, GUIDs are constructed from the current time, a serial number in your network card (the MAC address?), and a random number.

Theoretically, if some unscrupulous company manufactures knock-off network cards that are duplicately serialized with others in the world, there's a very slim chance of a GUID not being unique in the world. Not sure if that's even plausible, though.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[SQLDenis]

Interesting

1 trillion UUIDs would have to be created every nanosecond for 10 billion years to exhaust the number of UUIDs.

http://en.wikipedia.org/wiki/Universally_Unique_Identifier

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Lamprey13]

jmille34,

I'm not sure I fully understand your problem.

Are you saying you have one server that you do an insert on and you can get the identity just fine, but when you replicate that table over to another server there are issues because that other server has the same table also with an identity on the same column? Is the issue that both servers have inserts and you need to sync the two? Or is this an issue where you have a source table and need to replicate it over to another database and the destination table does not need that particular column to be defined as identity?

I guess, I not sure which situation you have as a lot of the time you have a source system with the Identity and a destination system that just needs that "identity" to be unique. Seeing as you were talking about using a GUID, I don't see why you cannot just remove the Identity from the destination column add a unique constraint and replicate away..?

 

[Lamprey13]

Dang, nevermind.. I missed the part about two simultaneous inserts.

I don't know if it'll help in your case, but this is a common problem with data warehousing. You might do some searches for how we do it there. The gist is that you have a "master" table that other systems get their IDs from.

 

[mrdenny]

What replication topoligy are you using? SQL Server is supose to break the identity field into ranges and assign each server a range so that when records are entered into the second server they come from that servers range of idenity values.

Granted this gives you numbers out of sequence, but they will fill in over time, and it's better than the duplicate id issue.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jmille34]

The two servers are both web servers that host their own databases.. each server should be able to run completely independent of the other if necessary, which means the apps need to be able to do inserts the same way on both, so the thing with identity on one and unique on the other wouldn't work for us.

In case anyone cares about the bug in ms sql 2000 which is causing all these problems, is that in order to get around the identity problem above, sql server will allocate a block of say 1-1000 for server 1 and 1001-2000 for server 2.. when either server gets through 80% of their bank of identities, it will allocate the next block up. This is supposed to happen fairly fast. The problem is that using merge replication (where you can insert on either server and additions propogate to the other) there are various timer settings, the optimum being "continuous".. if you don't use that, the most often you can go is once per minute. But if you use continuous, the thing about allocating the next block of identities is BROKEN. I looked it up a while back, and the technet article basically said it was broken and suggested the 1-minute fix. And even that wouldn't be so bad, except that now that my site is getting traffic, occassionally I will get a huge batch that will fill up that block of 1000 and all subsequent inserts will fail. Also, there are admin functions for handling users that will insert records inside a loop, and if that loop gets too big, it will fail. So to get around that, or at least fail gracefully, I put it in a transaction so it would roll back, but then it was generating monstrous transactions, and that had its own problems. I think there is a hotfix by now, but the problem of inserts inside a loop would still be there, so..

So at this point, I'm thinking identity fields are just not what I need given my limited resources. Meh.. oh well, only 85 tables and 700 source code files to modify.. that should only take maybe 20 minutes right?

 

[mrdenny]

OK, if that's the problem, stupid question then. Why not increase the size of the range from 1000 to say 100,000?

As another option (if you are using SQL 2005), why not mirror between the two machines, and setup the connection strings for the web sites two use one as the primary and the other as the fail over. Which ever one you are going to use as the backup, setup the witness on this machine and off you go. A single database, with fail over in the event that machine1 dies.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com