Primary Key Creation - Suggestions 2

[Auguy]

Quick question. I am developing an application that will run on a few office computers as well as one laptop in the field with no way of connecting to the "Live" database. I plan on making a copy of the database on the laptop. The database will be small and fortunately there will not be many changes in the field and I will have to merge them at some time. My question is about the primary key when new records are added in the field. Normally I use auto-incrementing identity integer column as the primary key. I'm thinking about using an integer column named SourceNbr (1-9) to identify the laptop database and then use this number together with another column (auto-incrementing) to create the primary key (maybe a computed column). Something like like SourceNbr * 200000 + Other Column. How do you handle something like this? I'd rather not use a GUID, but I guess I could.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

In my opinion, this is exactly what GUID should be used for. If you decide to take my advice, please take 5 minutes and read this:

https://msdn.microsoft.com/en-us/library/ms189786.aspx

It talks about using NewSequentialId for the primary key. This will prevent tons of page splits and therefore poor performance.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ElEye]

My opinion: I agree with your idea of an offset, say 2000000, for each user. Perhaps you'll need that ID field to be bigint rather than int. A uniqueidentifer might sound interesting but that value's uniqueness is based on the database that generates it which means, however unlikely, one generated on one database could conceivably be the same as one generated on another.

Best of success with this. I don't envy your having to deal with non-realtime data like that. Obviously not your first choice.

Dave
[

http://flashspot.com

]

 

[Olaf Doschke]

>that value's uniqueness is based on the database that generates it which means, however unlikely, one generated on one database could conceivably be the same as one generated on another

Wrong, uniqueidentifier is used for merge replication, which is the exact similar case, even if you don't plan to use that feature and develop your own merging technique.

https://technet.microsoft.com/en-us/library/ms151206(v=sql.105).aspx

Don't ever argue with the "however unlikely" excuse. Think about this: Even if it would occur, would it occur in the related database? And if so, would it occur in the replicated table, where it would have the only bad impact on a later merge? If it ever was the case it's much more likely the same GUID or UUID has been generated on different computers in databases not having any relation with each other.

Now if it would occur it would merge two records, which shouldn't be merged. A very big issue, indeed. I'd say in regards of costs for bug fixes this will be your least cost, don't even think about it.


One separate thing: In regard of page splits due to newid() vs newsequentialid(), that's only having an impact when doing the clustered index on the id column. Why would you waste the option to physcally eep data together on that column? You simply wouldn't make a clustered index on the guid. Anyway, that's a separate discussion.

Bye, Olaf.

 

[gmmastros]

@ElEye

According to this page:

https://msdn.microsoft.com/en-us/library/ms189786.aspx

Each GUID generated by using NEWSEQUENTIALID is unique on that computer. GUIDs generated by using NEWSEQUENTIALID are unique across multiple computers only if the source computer has a network card.

I infer from this that each computer (that has a network card) will generate unique id's. The problem with offsets is that you (or your successor) will eventually need to revisit the code because you will eventually run out of offset values.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ElEye]

Gentlemen (Olaf and George,)

I stand corrected.

Thank you for your insight.

Dave
[

http://flashspot.com

]

 

[Auguy]

Thanks to everyone for this discussion.

Auguy
Sylvania/Toledo Ohio