Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server

share|improve this question
I made a post ( ) comparing three types of ID. Thanks for the answer they were very helpful. – BrunoLM Sep 13 '11 at 14:54
Rather than primary key, I think you mean surrogate key i.e. a key that is not the natural key (the latter being the key we use in the real world). Possibly you mean clustered index. – onedaywhen Jan 31 '12 at 8:48
Also remember the difference between (Primary) KEY and INDEX. – Allan S. Hansen Jan 7 '14 at 6:55
Also discussed on SO:… – Jon of All Trades Jun 22 at 21:41
"int has no flaws except by the number limit, which in many cases are irrelevant.": actually, within this context of INT vs GUID, the upper limit of a signed, 32-bit INT is entirely irrelevant given that the upper limit of a signed, 64-bit BIGINT is well beyond nearly all uses (even more so if you start numbering at the lower limit; and same goes for INT) and it is still half the size of a GUID (8 bytes instead of 16) and sequential. – srutzky Oct 7 at 18:41

4 Answers 4

up vote 45 down vote accepted

This has been asked in SO here and here

Jeff's this post will explain alot about pros and cos of using GUID.


  • Unique across every table,every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway


  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

If you are certain about performance and you are not planning to replicate or merge records then use int, and set it auto increment (identity seed in SQL Server)

share|improve this answer
Another con of the GUID approach is that you cannot use it as an identifier for your end-user. Do you really expect your users to tell you on the phone that they have an issue with Order "BAE7DF4-DDF-3RG-5TY3E3RF456AS10" ? :) – Brann Jul 8 '11 at 21:22
If you do not use sequential guids, and your primary key is clustered (the SQL Server defaul) then all your data inserts will be randomly scattered throughout the table, leading to massive fragmentation of your data. That is assuming that the data would normally be inserted in some sort of order, such as chronological. – datagod Aug 6 '11 at 4:32
Sequential guids are only sequential until the SQL instance is restarted. Then the first value will more than likely be lower than the prior one because of the way that the root value is generated, causing all sorts of problems all over again. – mrdenny Aug 6 '11 at 6:37
Another benefit of guids is that ints have a limit. It can be exceeded at some point. Then you have to change the datatype in the table and all related tables. Not an issue with guids. – Chad Kuehn Jun 14 at 17:13
@Brann Ideally you wouldn't be given your PK values out to end-users in the first place. I know it is somewhat common to do so, and it is something I myself have done in the past before I learned not to. But since it shouldn't be done, that particular reason to prefer INT over GUID isn't a valid one. – srutzky Oct 7 at 18:54

Some best practices out there still mention that you should use a data type that accomodates with the less memory possible the whole set of values you're going to use. For instance, if you're using it to store number of employers in a small business and you're unlikely to get to a 100, then no one would suggest in using a bigint value while int (even smallint) would do.

Of course, the drawback of this is like "Say no to scalability!"

Also, I know this is not totally related, but there's another factor regarding this. When not excesive, I usually try to recommend to use a non-autogenerated primary key, if it does make sense. For instance, if you're saving driver's information, don't bother in creating a new autogenerated column for "ID", just use the license number.

I know this sounds really obvious, but I see that being forgotten quite often.

For context: this part of the answer was addressed from a data theoretical approach, where you want your PK to be the uniquely data-identifier for a record. Most of the times we create those when they already exist, hence the previous answer.

However, it is very rare that you can have tight control over these datapoints, and as such, you may need to make corrections or adjustments. You can't do that with primary keys (well, you can, but it can be a pain).

Thanks @VahiD for the clarifications.

share|improve this answer
using meaningful primary keys is not recommended at all, consider below scenario, someone entered wrong license number and you've used this id in 3-4 tables as foreign key, how do you fix this mistake? simply editing the license number could not be enough in this case. – VahiD Oct 7 at 10:39
Funny: I read your comment and I thought "yes, of course", then went back to read my answer and thought "did I say that"? Funny how things change in a couple of years. I was probably coming from a more theoretical background, but unless you have tight control over it (rarely) it does not provide much benefit. I'll update the answer. – Alpha Oct 7 at 17:13
upvote for the development in the years :) – VahiD Oct 8 at 18:28

I have used a hybrid approach with success. Tables contain BOTH an auto-increment primary key integer id column AND a guid column. The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row.

share|improve this answer
What value does the GUID give if the id already is sufficient for humans to identify a row? – Martin Smith Apr 3 at 18:06
The id identifies the row in this table. The GUID (at least in theory) identifies this row anywhere in the known universe. In my project, Android mobiles each have a structurally identical copy of the table on a local SQLite database. The row and its GUID are each generated on Android. Then, when Android is synchronized to the back-end database, its local row is written to the back-end table without fear of conflicting with rows created from any other Android mobile. – rmirabelle Apr 3 at 21:46
@MartinSmith I have used this approach myself and it works quite nicely. The GUID is just an alternate key, with a NonClustered index, and is passed in from the application, but only resides in the primary table. All related tables are related via the INT PK. I find it strange that this approach is not much more common given it is the best of both worlds. It seems like most people just prefer to solve problems in very absolutist terms, not realizing that the PK doesn't need to be a GUID in order for the app to still use GUIDs for global uniqueness and/or portability. – srutzky Oct 7 at 19:05
@easuter I agree with not adding ID fields "just for the sake of it", such as in many-to-many "bridge" tables where the PK should be a composite of the two FKs that are being related. But here it is not a trade-off since the ID field is not merely for the sake of it. Allowing the system to work efficiently is fairly important ;-). AND, I would argue that in your case, since the GUIDs are generated externally, those are not guaranteed unique, even if pragmatically they are. But the responsibility for data integrity is reason enough to have GUID be an alternate key and ID be PK in your case :) – srutzky Oct 31 at 15:26
@easuter I also agree with not adding multiple keys for the sake of it. In my case, the ids are super useful, primarily because they auto-increment (GUIDs do not) and auto-increment enables relative sorting on date inserted (even without a date column), which is critically important for my app. Also, my ORM framework assumes an auto-incrementing key called 'id' on every table, so it can work its magic. Finally, ids are short, which makes for far more human-workable query strings and manual DB queries. – rmirabelle Oct 31 at 16:45

If you're synchronizing your data with an external source, a persistent GUID can be much better. A quick example of where we're using a GUIDs is a tool that is sent to the customer to crawl their network and do certain classes of auto-discovery, store the records found, and then all the customer records are integrated into a central database back on our end. If we used an integer, we would have 7,398 "1"s, and it'd be a lot harder to keep track of which "1" was which.

share|improve this answer
GUIDs are definitely good as external identifiers, and I would keep a non-clustered index of that as the "external key" I would still keep an int as the "internal key" which is the basis for the clustered index and foreign key relationships. If something is going to cross an architectural boundary (e.g. communicating with another app) I do appreciate having something that cannot be mixed up. – Greg Oct 7 at 18:07

Your Answer


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.