General discussion


To GUID or Not to GUID

By Lisa_halidyne ·
Opinions sought - database design:

Guids are the only means of giving global uniquenesss to primary keys when data from remote sources is anticipated.

Should GUIDs be the primary key for every table in every system as a blanket policy??

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

That is a question!

by BrianHarris In reply to To GUID or Not to GUID

We're using GUIDs not only for uniqueness but also for security. Not knowing what the value represents helps us in our application as we're sending a lot of raw XML. We're generating about 100K rows per month in about 2 dozen tables and will archive the data every 6 months. The client wants all of the data to be unique. If we went with a long we'd eventually have to restart our Identity losing the uniqueness across even the archives.

We have a fair number of system tables where we just won't have enough values to justify not using a long.

I would only recommend using GUIDs where you need to worry about having too much data to use a long or other data type for your PK. Or, as in our case, where you might want the security of "hiding" the real values.

Collapse -

too much of a good thing

by Lisa_halidyne In reply to That is a question!

Thanks for the opinion Brian,
You've given me another purpose for GUIDs (security)
I agree that GUIDs definately have their benefits and place. I'm currently in a debate opposing a blanket policy of placing GUIDs everywhere.
a MSDN document (over 2 years old) lists exactly the stand we have taken - use GUIDs when needed -
Otherwise - I'm stumped for a best practices document or 'official' recommendation. Thanks

Collapse -

GAG no.

by BarJabba In reply to To GUID or Not to GUID

I am beggining to restructure a very important tool for UPS. The person who originated the database and VB app didn't really think it through. A necessary ID column was set to int BHUT the ID's from the imported data have leading zero's. Now I have to redo and recollect with a guid. Quite messy to say the least.

If you can use a GUID for those tables and structures that import, tis ok. But, Primary keys are there for reason.

MyTableID int Primary Key Seeded
MyImportID nvarchar(?) GUID Non-seeded
Datacol3 ... Ect

At least you can have a primary key that you can use to cleanup your data or whatever and a foriegn ID(not a key necessarily) that is your imported multi system IDs.



Collapse -

not a question of PKs

by Lisa_halidyne In reply to GAG no.

Yes, we all agree that Primary Keys are paramount to the design of a relational database.

GUIDs definately have their place in systems that expect the import of data accross multiple servers when the same autoincrementing ID could be generated multiple times - or Guids are excellent when one expects to not have enough numbers.

But I'm noticing a trend to use GUIDs as the PK even for little lookup tables - say "County". This overkill using GUIDs is due to the desire to "Pregenerate" the ID before going to the database...rather than retreive the new ID with @@Identity.

My team has placed a call to MS. Microsoft stands behind their MSDN article to only use GUIDs when needed. This brings the two year old document up to date for me!

We also have an email from Mary Kirtland, COM team developer at MS and author of Designing Component Based Applications who reiterates the use of GUIDs responsibly.

Definately GUIDs have a place and time - just not everyplace all the time.

Collapse -

That depends

by Zeus66 In reply to To GUID or Not to GUID

The question:

**Should GUIDs be the primary key for every table in every system as a blanket policy??**

would depend on the reason for the policy. It would not make much sense to require guids as primary keys in a system that uses Access as adatabase engine.

But if the reason they are required in every table is to maintain consistency through out the table structure of a system, that would be reasonable.

One may argue the fact that they are bigger and harder to read that auto-incrementing numbers, but in real life a primary key should not do anything other than uniquely identify a record in a table. So there should not be the need to read it. You should never have to type it in and the user should never see it.

And as far as being bigger, that is very true. The are 4 times as big as an int and create bigger indexes as well.

One reason to use a guid, as you mentioned, is to create GLOBALLY unique keys. Specifically for distributed databases. And one might argue'we will never need to distribute this database over multiple servers'. I would question, why limit yourself. certianly didnt expect the demand they got. Had they been short sighted and said 'we will never need to have more than one server' what would have happened to them?

But it also comes down to a development style. If you just dont like them - lets face it - they are pretty ugly, then you will not want to use them.

Should it be a blanket policy? If you wish to create a standard interface to your tables and the primary key type is a guid then yes - it should be a blanket standard. But if you do not care about creating and dealing with those standards then no - it should not be.

One must make the best choice possible based on the business you are in, the types of projects you are doing and where you see the trends going in the future.

Related Discussions

Related Forums