| Author |
Topic |
|
moto2001
Starting Member
3 Posts |
 Posted - 2008-07-09 : 14:57:37
|
| HiI have heared about several Dis-Advatages of auto-incerment PK.I don't know what is those dis-Advatages yet, but my Prof hates Auto Incerments anyway, so Is it common and acceptable to add GUID entry for each primary key, and why not? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-09 : 15:18:55
|
| Wow you're about to open a can of worms with that question I think. Here are my opinions. I've never had any problems using an identity column with a big int or int data type. I have 2 main reasons for choosing identity over guid. First, storage size. An int, 4 bytes, big int, 8, guid 16. You can do the math on your table and calculate your space savings by using int or big int. Second, convenience. GUID's have absolutely no rhyme or reason so you if you're trying to grab a group of sequential records you need to go by something else like a date field. Since an identity column will sequential unless something out of the ordinary happens it's a simple matter to grab the last 5 records inserted or sort them descending if need be. Seems like a small thing but when you're working late and trying to fix something it's a convenience that becomes valuable.Mike"oh, that monkey is going to pay" |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-09 : 16:03:40
|
| If you use a GUID for a CLUSTERED primary key, unique constraint, or index, rows will be inserted in random order throughout the table, so you will have to do a lot of re-indexing to keep fragmentation to reasonable levels.I prefer IDENTITY integer or bigint columns for surrogate keys, because it makes for more efficient lookups between foreign keys. Even a bigint is half the size of a guid, and an integer is one quarter the size.I would ask your Professor for something a little more concrete for his reasons for hating IDENTITY (auto-increment) keys.CODO ERGO SUM |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-09 : 18:46:27
|
if you want sequential GUIDs, you can use NEWSEQUENTIALID() in 2005, or Gert Draper's XPGUID library in 2000. that way you won't get fragmentation of the PK.one reason to use GUIDs is if you really need the id to be globally unique, say across different dbs on different servers, different continents, different planets.
elsasoft.org |
|
|
|
moto2001
Starting Member
3 Posts |
Posted - 2008-07-10 : 15:32:48
|
| Thanks everyone that was helpfull |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-10 : 15:41:37
|
I read somewhere that the GUID is only guaranteed to be unique for the current server only.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-11 : 02:41:52
|
| I could be mistaken, but I thik that's for machines without a network card. The generation algorithm of guids uses the network card's MAC address (globally unique) to help ensure uniqueness.--Gail ShawSQL Server MVP |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 04:14:58
|
acctually no it doesn't. not anymore. only version 1 of the algorithm used it. version 4 doesn't use it anymore.and version 4 has been around since sp2 for win NT (but don't hold on that one  )_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-11 : 06:36:14
|
I was called to a client for two years ago and they were having trouble with replication.They had a "master" central database and several worldwidde "onsite" local databases. For some reason two of the local databases generated the same GUID and that was a huge problem when importing into central database. We were able to figure out the conflicting GUID and updated one of the local databases.Since then, I have always suggested to use a surrogate key in these situations.One SMALLINT for denoting the local database instance, and one local identity INT for that particular database.Six bytes total instead of 16 bytes for every record. Multiply that with 90 million records per local database and 24 sites, and you get the idea.It saves more than 20 GB in index size and 20 GB in data space on central database.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 07:40:05
|
| seriously? you got duplicate guids?? wow!!!_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-11 : 11:16:04
|
quote:
Originally posted by spirit1
seriously? you got duplicate guids?? wow!!!_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
There are only 3.4028236692093846E+38 possible guids. Sometimes you get lucky, but I would also be suspicious of a manual intervention.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-11 : 13:05:40
|
In their case, they had both web clients (about 4000) and desktop clients (about 100) and GUIDs were created at client and sent to database.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-11 : 13:48:32
|
quote:
Originally posted by Peso
In their case, they had both web clients (about 4000) and desktop clients (about 100) and GUIDs were created at client and sent to database.
E 12°55'05.25"N 56°04'39.16"
Maybe the web server gave the same guid to two different users.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-11 : 14:22:47
|
I have read that under heavy load, it is possible for sql server to give same newid() value.But this was two different sites.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-11 : 16:18:54
|
Peso - how did you divvy up the smallint siteids? If two new sites come into existence, what's to keep them from grabbing the same smallint next in line?
elsasoft.org |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-12 : 00:33:16
|
The value of the SMALLINT column is decided centrally.The value is set whenever a new site is opened.When the new database is scripted, the SMALLINT value is a default constraint.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
GUID as a PK