Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BruceT
Yak Posting Veteran
78 Posts |
 Posted - 2010-10-20 : 13:53:14
|
| Hi all,Other than storage requirements does anyone have any input on performance issues of using a GUID vs char(36) for a primary key and/or foreign keys?At issue, we're refactoring a large very under-normalized database that currently uses char(18) (user generated) primary keys. Converting those to uniqueidentifiers would be a major hassle, where changing them to char(36) defaulted to Newid() would make life way easier. I'm just not sure if there's a significant performance hit between using a binary column vs a character type. The limited testing I've done with a million plus rows suggests there's not much difference. I'm interested in what others with more experience might think.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-20 : 14:28:38
|
| What's wrong with using integer or bigint for the primary key? |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2010-10-20 : 14:36:41
|
| Integer or bigint...I need to make sure they are unique across servers for replication and remote syncronization which is why GUID's are appealing. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-20 : 14:46:41
|
| If you go with GUIDs, don't store them as char(36), it's a space waster. Also don't cluster on them if you're defaulting to NEWID(). If you must cluster on them, use NEWSEQUENTIALID instead, or this technique if you're not using SQL 2008:http://www.informit.com/articles/article.aspx?p=25862 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 06:46:38
|
| "I need to make sure they are unique across servers for replication and remote syncronization which is why GUID's are appealing."Dunno if its any use in your circumstances, but one technique I have seen is to use IDENTITY with the Increment set to something other than 1.So Server-1 has IDENTITY(1, 10) - i.e. 1, 11, 21, 31, 41Server-2 has IDENTITY(2, 10) - i.e. 2, 12, 22, 32, 42...Server-10 ...This is pre-designed to only allow 10 individual server ranges - so not very scalable. However, there are some (kludgey) get-of-jail-cards for scalability: Server-11 has IDENTITY(-1, -10) - i.e. -1, -11, -21, -31, -41and even:Server-21 has IDENTITY(2147483641, -10) - i.e. 2147483641, 2147483631, 2147483621, ...which will meet in the middle somewhere! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-21 : 09:46:08
|
quote:
Originally posted by Kristen
"I need to make sure they are unique across servers for replication and remote syncronization which is why GUID's are appealing."Dunno if its any use in your circumstances, but one technique I have seen is to use IDENTITY with the Increment set to something other than 1.So Server-1 has IDENTITY(1, 10) - i.e. 1, 11, 21, 31, 41Server-2 has IDENTITY(2, 10) - i.e. 2, 12, 22, 32, 42...Server-10 ...This is pre-designed to only allow 10 individual server ranges - so not very scalable. However, there are some (kludgey) get-of-jail-cards for scalability: Server-11 has IDENTITY(-1, -10) - i.e. -1, -11, -21, -31, -41and even:Server-21 has IDENTITY(2147483641, -10) - i.e. 2147483641, 2147483631, 2147483621, ...which will meet in the middle somewhere!
If you do something like this, make sure you add a check constraint to the ID column so that it doesn't allow a value not in the identity sequence so that someone can't mess it up with a manual insert.ID%10 = 1, ID%10 = 2, etc.CODO ERGO SUM |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2010-10-21 : 13:16:42
|
| Kristen and Michael, great solutions! Unfortunately, I don't know how many "servers" are going to be involved at deployment. What I'm dealing with is a central server that remote (not connected to the central db) users (walking around with laptops that have their own copy of the db) are going to be syncing back to the central db periodically. And so I need to make sure that each remote db generates unique primary keys.Thanks for the suggestion though, I may be able to use that elsewhere!Bruce |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 15:46:10
|
| "walking around with laptops that have their own copy of the db"That's a prime candidate for GUIDs ... much as it pains me to say so!! |
|
|
|
|
|
|
|
|
char(36) vs uniqueidentifier