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?

  • 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
    Commented Nov 22, 2011 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.
    – Nicole
    Commented Nov 22, 2011 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. Commented Nov 22, 2011 at 15:06
  • @Edward Robertson - I believe this is possible in newer versions of SQL. There is no GUID datatype in sql-2000. Commented Nov 22, 2011 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 Commented Nov 22, 2011 at 15:17

4 Answers 4


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.


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.


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.

  • 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. Commented Nov 22, 2011 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.
    – NoChance
    Commented Nov 22, 2011 at 15:06
  • I see, that makes sense. I was trying to avoid some other details (the spreadsheet). So that's my fault. Commented Nov 22, 2011 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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