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:
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.
Code:
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.