Auto increment vs. GUID

[WDrago]

All,

I'm trying to decide if I should use an auto-increment field or GUID field in a database. Either way I get a unique number identifying the record, so I'm not sure what way is best. Any thoughts?

Thanks,
-Bill

[JorgeM]

There are advantagas and disadvantates to using Auto-incrementing integer vs GUID. Its difficult to advise based on your description. You can go with either, I've used both. For small DB implementations, with regard to peformance, its negligible, but that's a vague statment on my part again as other factors can affect performance (specifically storage design). Will the DB be mainly experiencing reads or writes?

Here's some pros and cons to GUID:

Pros:

• The GUID will be/should be unique within the DB, even accross different servers.
• The GUID is more object ID–like datatype
• You can generate GUIDs anywhere, even in the business layer outside of your DB. That's good for some scenarios.
• If you are replicating your DB, some scenarios require GUID.
• A GUID will not mean anything to the users. Increase security through obscurity.

Cons:

• GUID is larger than INT. How will this affect indexing?
• Difficult to debug, think about a querystring usch as userid='BAE7DF4-DDF-3RG-5TY3E3RF456AS10')
• For best performance, GUIDs may not be the best choice depending on the overall design. Do some testing and research on your own.

[WDrago]

Thank you for the detailed reply. This is exactly the kind of information I was looking for.

-Bill