In a typical scenario, an object that needs to be persisted in the database will be created in application code without an identifier, and later saved to a table in the database. Some database construct, such as an identity column, will generate a new identifier, typically an integer that's unique for all of the rows of that table. This identifier can then be returned to application code and then treated as a key.

    UserId int IDENTITY(1,1) NOT NULL, DisplayName nvarchar(max), 
    UserName nvarchar(50), [Password] nvarchar(50))

INSERT INTO Users (DisplayName, UserName, [Password]) 
    VALUES ('Joe Shmo', 'jshmo', 'pw1')

Another common scenario is for the identifier to be GUID:

    UserId uniqueidentifier NOT NULL, DisplayName nvarchar(max), 
    UserName nvarchar(50), [Password] nvarchar(50))

DECLARE @id uniqueidentifier = NEWID()
INSERT INTO Users (UserId, DisplayName, UserName, [Password]) 
    VALUES (@id, 'Joe Shmo', 'jshmo', 'pw1')

This second way provides the following advantage: the GUID can be generated in application code, which can then be used as a key for application caching/referencing purposes before the object is ever saved. However, there is a cost associated with using GUIDs in size and, especially look-up costs. These may be trivial when the table is small, but can become a major factor when the table is massive.

Are there any best practices, or techniques for the cases where an identifier unique to the object type needs to be generated in application code, but cannot be a GUID for whatever reason?

One thought was to have a another table generating identifier wrapped by an identifier service which application code could call.

  • What about using a single sequence? Does your RDBMS support sequences?
    – Falcon
    Sep 21, 2011 at 19:35
  • You'll have more chances to have answers if you ask this question on dba.stackexchange.com Also, don't forget to precise the SQL server you use. Different servers behave differently, especially when it comes to performance. Sep 21, 2011 at 19:36
  • We are using SQL Server 2008R2, and there is a probable migration to SQL Azure in the future.
    – afeygin
    Sep 21, 2011 at 19:50
  • 1
    @MainMa - actually, as it's a design question it's also on topic here. However, if afeygin wants we can migrate to dba.
    – ChrisF
    Sep 21, 2011 at 19:54
  • 1
    When you say you cant use a GUID, do you mean you cant use an MS style GUID, or that you cant use any form of UUID? Can you elaborate on why you think there's a meaningful lookup cost when using GUID's/UUID's as keys? Because I'm not sure I buy your premise. Sep 21, 2011 at 20:42

6 Answers 6


both approaches are valid

if only one database server generates the keys, a sequence ID is fine, and is (as you noted) somewhat more efficient (and a heck of a lot easier to read) than a GUID

if multiple servers generate the keys then go with a GUID

another option is to let the database generate a sequence key for internal relationships, and use a GUID as a unique secondary key

as usual, it depends on how you create and use the data

  • The the third approach--GUID for a unique secondary key--is an interesting thought.
    – afeygin
    Sep 21, 2011 at 21:52
  • GUID as ID looks enticing if you need globally unique identifiers, sure, but when performance and indexing requires then having another autoincremented id... why not just use a regular auto-incremented id and then add GUID columns to those tables that REALLY require it! A nice article I found here: rclayton.silvrback.com/do-you-really-need-a-uuid-guid Aug 29, 2017 at 14:43
  • 1
    @KyleBurkett: autoincremented id = sequence ID, only works when a single server can generate all the keys Aug 29, 2017 at 18:19
  • Sure, its why I upvoted your answer for pointing that out. Still, people could use a distributed sql environment but choose to do inserts only at one node... autoincrement could still do the trick for them Aug 31, 2017 at 15:59

A primary key is implemented via a unique index. For best performance, you should choose the smallest possible column length and if possible use numeric value. This has a performance effect in sorting and finding data.

The datatype int is usually large enough for most applications, but it may not be in your case.

A disadvantages identity fields is that if you use strictly sequential numbers, the indexes don't get built in an efficient way, however, this is really a minor issue. The other silly thing, is that you can't display the key value before you have completed the insert. This may sometime add some complexity to your GUI design. Also, you need to use SCOPE_IDENTITY() to capture the last inserted id which is an extra bit of work for the developer.

GUID is almost universally unique, so they are more generic than identity columns. With GUID, the key is text and its larger than the int type. It is difficult to test with and difficult to provide to end user as part of a business application. For example, when used for customer number, invoice number, etc. it becomes impractical and causes problems for the end user. Also GUIDs are not 100% unique. I don't know the exact probability of duplication. The only nice thing about them is that they can be generated at the client without the round-trip to the server.

NewID() and NewSequentialId() function makes use of GUID.

You can take a look at this for some insight: http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html

If you are considering a cluser index, you may want to check this: http://randommadness.blogspot.com/2008/07/guids-and-clustered-indexes.html http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html


If you want to use a persistence layer like NHibernate, then using a Guid has one significant advantage over an identity column: Guid's can be generated on the client side as soon as the entity object is created in memory, without hitting the database to do an insert. Using an incrementing identity column, you have to do the insert to get your value.

This is a specific benefit of the general benefit of being able to generate Id's in a distributed manner that Guids allow. If you plan to have any kind of mobile devices, or distributed databases, or just the ability to batch-import rows, it can be very helpful to generate new Ids without hitting the database.


I know of a technique called Guid.Comb (see NHibernate) which has the advantages of a GUID but has a few tweaks to avoid database problems. The problem with GUIDs is that database indexes get fragmented due to their random nature. This is a severe problem, especially as GUIDs are often used as primary keys. But be aware that not every GUID algorithm suffers from that problem, as the database vendors are already aware of it. Not all GUIDs are inherently bad to look up.

What you basically do is creating an identifier that eliminates some of the randomness to get an ordering into the GUIDs. Look at this article here to get an idea of how it can be done.

I can see no reason for not using GUIDs if you won't run into space problems. For large data volumes this can be a very, very serious problem with huge impacts.

When you really can't have a GUID as PK, you can use a GUID as a secondary key, as Steven A. Lowe already stated.

  • Right, a handful of our tables do grow to enormous volumes. Most table don't--for these the GUID approach is fine.
    – afeygin
    Sep 21, 2011 at 21:54

Either one is OK depending on your requirements. With an integer identity column there is value little maintenance that will need to be done with the column over time because the data on the index is sequential. You can insert 1000s and 1000s of row and the index on the column will hardly fragment.

With a GUID, the index will fragment over time and periodic maintenance (rebuilding the index, etc.) will have to be done to maintain the index on the key column. So, as long as you are aware of this, I beleive either are OK.

The integers take up less space but are easier to guess, guids take up more and are harder to guess. As you said GUIDs can be generated in code, although SQL Server can do an identity column or newID() column as well. I also believe there is something called a sequential guid which helps in the fragmentation department.

If you ever had to merge two databases together, guids would be better as row IDs would probably collide between the two databases.

  • Not sure how a sequential GUID will help. If I have to generate it on the DB-side, I might as well use an integer identifier. If I generate it on the application server, it's not guaranteed to be unique across multiple servers. If I create a GUID service, I might as well have the DB generate me an integer identifier. Merging is not a consideration at this point, although it maybe be in the future... still I'd stick with the YAGNI principle.
    – afeygin
    Sep 21, 2011 at 21:48

It turns out that most of us (including me) have been wrong about fragmentation of Random GUIDs. It's actually a myth that we've all perpetuated because of a poor understanding, some really poor tests, and accepting and embracing supposed "Best Practice" Index Maintenance recommendations that not only aren't a "Best Practice", we're never meant to be a "Best Practice" and are, in fact, a VERY WORST PRACTICE.

I can't write an article about it here on a short post and I know people frown on links because they might someday go away, but you've got to watch the following 'tube to the end where I show the results of inserting 100,000 rows into a Random GUID clustered index for 58 days with less than 1% fragmentation until the 58th day.

Random GUIDs can actually be used to PREVENT fragmentation.

"Black Arts" Index Maintenance #1.2

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.