Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have the need to create a table with a unique id as the PK. The ID is a surrogate key. Originally, I had a natural key, but requirement changes have undermined this idea. Then, I considered adding an auto incrementing identity. But, this presents problems.

A. I can't specify my own ID.
B. The ID's are difficult to reset.

Both of these together make it difficult to copy over this table with new data or move the table across domains, e.g. Dev to QA. I need to refer to these ID's from the front end, JavaScript...so they must not change.

So, the only way I am aware of to meet all these challenges is to make a GUID ID. This way, I can overwrite the ID's when I need to or I can generate a new one without concern for order (E.G. an int based id would require I know the last inserted ID).

Is a GUID the best way to accomplish my goals? Considering that a GUID is a string and joining on a string is an expensive task, is there a better way?

share|improve this question
2  
You can't have you cake and eat it... GUID sounds right for your requirements. As for it being a string - you are assuming... It is not stored or joined on as a string, so not as expensive as you think. –  Oded Nov 22 '11 at 14:38
    
I think "arbitrary" fits your description, not meaningless. It seems to have a lot of meaning, otherwise you could just use auto_increment and reinsert with new ID when moving data. –  NickC Nov 22 '11 at 14:43
1  
NOTE: a GUID is not a string, it is binary data that is displayed as a string for humans. Please do not store a GUID as a string in the database. Both SQL Server and Oracle have ways of storing GUIDs in their binary form (I'm not sure about other DMBSs), and they are much more efficient in terms of both speed and size if represented correctly as binary data. –  Edward Robertson Nov 22 '11 at 15:06
    
@Edward Robertson - I believe this is possible in newer versions of SQL. There is no GUID datatype in sql-2000. –  P.Brian.Mackey Nov 22 '11 at 15:07
1  
@P.Brian.Mackey - The 'uniqueidentifier' (props @JDT) type has been around since at least SQL Server 2000, possibly before, and it's purpose is for storing GUIDs:"SQL Server 2000 - The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs)." msdn.microsoft.com/en-us/library/aa223933(v=sql.80).aspx –  Edward Robertson Nov 22 '11 at 15:17

4 Answers 4

up vote 3 down vote accepted

GUID (the uniqueidentifier-data type in SQLServer) seems like the best option based on the information you provided. I work on a large application that has tables with 500000+ rows using GUID's as primary keys with some pretty large joins and the bottleneck is never in the joins of the primary keys.

Various other sources also seem to indicate that GUID's are a good choice, these are worth checking out:

Some of the bigger issues seem to be:

  • Loss of chronology (no way to know which row was inserted last and no ability to remove everything inserted after a certain point in time), can be fixed easily by using an 'insertdate'-column containing the date and time the row was added
  • Larger storage requirements but unless you deal with very large databases storage is pretty cheap
  • Harder to remember and work with in testing which is a valid issue but there are often alternatives to using a GUID to identify a row

I'm inclined to say that performance here would not be the issue and based on your requirements GUID are a very good - if not your only - choice.

share|improve this answer

Based on what you have told us, GUID is the best "built-in" way to meet your needs. trying to come up with a "roll-your-own" key that meets your needs sounds error prone, difficult, and like you would be reinventing the wheel.

share|improve this answer

Note that while GUID sounds good, you must be careful about:

1-Your server must support it (Microsoft backend for example) so you can generate it automatically to guarantee its correctness and uniqueness.

2-Your database(s) must support it (Microsoft SQL Server for example), because if you move your data to another database, it may be cumbersome to generate it.

3-You must have a consistent way to generate the values (either from the Client or from the server) - This way you guarantee consistency (this is a personal guess).

4-The user must not have to enter it because it is very cumbersome.

5-It does not preserve sequence, so you can't 'reset' it and start over. but you can assume that it would be unique.

share|improve this answer
    
I'm not sure what you mean by you can't 'reset it'. I have an excel spreadsheet that will be uploaded. It's contents will be used to overwrite some table data. In these cases, first all data will be deleted for a given table. Then, the extracted excel data will be inserted into said table. –  P.Brian.Mackey Nov 22 '11 at 15:01
    
@P.Brian.Mackey, I mean you can't restart from a known beginning point unlike numeric or alpha keys. I wrote that because in your original question, you have hinted to the case of moving the table. –  Emmad Kareem Nov 22 '11 at 15:06
    
I see, that makes sense. I was trying to avoid some other details (the spreadsheet). So that's my fault. –  P.Brian.Mackey Nov 22 '11 at 15:09

Considering that a GUID is a string and joining on a string is an expensive task, is there a better way?

If you make a GUID the PK, it will be a clustered index on the table. The reason why auto incrementing integer IDs are popular for PKs are that over time, the clustered index fragments very little, so little if any maintenance is needed on the PK. The index is sequential.

GUIDs will work out fine, provided regular maintenance is done on the index. If there are large amounts of inserts and deletes on the table, just make sure the process doing those inserts/deletes rebuilds the index after it completes. Also, over time a GUID index will fragment after many inserts/updates, so just rebuild the index/PK at regular intervals to keep the queries that use it running optimally.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.