Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Server Database Engine

Guids as primary keys

Is it a good idea to use Guids as primary key. What is the impact on performance. Guids are random by nature that may have an impact on writing clustered index. Any comments or suggestions

[196 byte] By [ZK] at [2007-12-17]

«« VC2005 %50 Slower then VC2003
»» Connecting to reporting services

# 1

I'm not a complete expert, but one problem I know of with GUID's with clustering keys is that they are not naturally increasing so this could introduce lots of fragmentation in environments with lots of inserts. They are also wide (16 bytes) so having them in all indexes and foreign keys can be a hit to the size of the database, scan performance, etc.

m_hemaly at 2007-10-6 >

top of Msdn Tech,SQL Server,SQL Server Database Engine...

# 2

http://www.aspfaq.com/show.asp?id=2504 -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <ZK@discussions.microsoft.com> wrote in message news:0f672e6d-bf30-40dc-856b-884183cce87e@discussions.microsoft.com...Is it a good idea to use Guids as primary key. What is the impact on performance. Guids are random by nature that may have an impact on writing clustered index. Any comments or suggestions

MVPUser at 2007-10-6 >

# 3

I prefer to use int or bigint set as identity. In my single db server environments, it works very well.

It is easier to read/remember, plus using it as an index is quicker since even with bigint, the column is half as wide as a guid.

Just be careful of setting it as a clustered index. This could cause hotspots in your data pages.

However, one note on identity. When you use enterprise manager (2000) or Management Studio (2005) the identity seed is set at 1 as a default. By accepting this, you lose half of the available IDs. Always set this to the minimum for the data type you are using. (-2147483648 for int, -9223372036854775808 for bigint)

BobP

BobP1339 at 2007-10-6 >

# 4

I would avoid GUIDs but if you will use them take a loot at "NEWSEQUENTIALID() function" http://msdn2.microsoft.com/en-us/library/ms189786(en-US,SQL.90).aspx

CristianLefter at 2007-10-6 >

# 5

NEWSEQUENTIALID() has lot of problems. How about programatically generating Sequential Guids. At least don't have to be used with default constrained and can guaranteed to be unique at system/application scope

ZK at 2007-10-6 >

# 6

Yes, a GUID is bigger than an int, and it's not really readable, but it is still the best choice as a primary key, if you take following guidelines into consideration:
. Do NOT create a clustered index on the GUID primary key
. generate the GUID on the middle tier (not via Newid(), this takes CPU on the server)
. set the rowguidcol attribute on the column

Why is it better than an Identity Int?
. It allows to combine records from different databases (e.g. Production and development)
. It allows to distribute the database over multiple servers, knowing that you can still combine the data afterwards
. You can generate master and detail records on the middle tier, instead of having to roundtrip between the 2
. You will need a GUID col when you use merge replication

in short, an Identity Int may be a microsecond faster, but a Guid is so much more flexible

FluffyBunnyfeet at 2007-10-6 >

# 7

I was looking on Microsoft and found this:

http://msdn2.microsoft.com/en-us/library/ms190215.aspx

The last part of it answers your question.

"Consider using the IDENTITY property when global uniqueness is not necessary, or when having a serially incrementing key is desirable."

So it comes down to your application. Is the key needed on more than one server, or is it going to be replicated using transactional replication? Use a GUID.
If not, use IDENTITY.

BobP

BobP1339 at 2007-10-6 >

# 8

Thanks for your help Bobp

ZK at 2007-10-6 >

# 9

Thanks for your help Fluffy

ZK at 2007-10-6 >

# 10

GUIDs qualifies all three major requirements of Clustered Key. They are:

1. Narrow
2. Non Updatable
3. Unique

The only major drawback in Primary Key GUIDs with clustered index is that they were not sequential in SQL Server 2000 but in SQL Server 2005 Microsoft has removed that limitation with new function newsequentialid().

For SQL Server 2000 one can use the solution described in following URL:

http://sqldev.net/xp/xpguid.htm

Jitendra Pardasani

Pardasani at 2007-10-6 >

# 11

I'll have to disagree with you here: They are not narrow compared to integers. They are certainly updatable, moreso than, say, an IDENTITY. And they are not any "more" unique than any other datatype. There is a larger domain of possible values, but there is no guarantee that you won't generate a duplicate at some point. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Pardasani@discussions..microsoft.com> wrote in message news:0b3e7a34-dc5d-43d0-a9d5-c1cfbae58cff@discussions.microsoft.com... GUIDs qualifies all three major requirements of Clustered Key. They are:1. Narrow2. Non Updatable3. UniqueThe only major drawback in Primary Key GUIDs with clustered index is that they were not sequential in SQL Server 2000 but in SQL Server 2005 Microsoft has removed that limitation with new function newsequentialid().For SQL Server 2000 one can use the solution described in following URL:http://sqldev.net/xp/xpguid.htmJitendra Pardasani

MVPUser at 2007-10-6 >

# 12

I have to agree with Adam. And in my opinion the benefits of integer surrogate keys far outweigh the benefits of GUIDs.

As an aside, we (meaning my company) never really use IDENTITY. We generate our own surrogate keys.

-Jamie

JamieThomson at 2007-10-6 >

# 13

Hello...

I think you should use both... On a small table that will only hold lookup values you should consider using an Int as a key, but when the table will hold a lot of data, then you should use a Guid. A GUID is only 16 Bytes, but you ensure that you DB can be spread across multiple servers. Its only a few extra bytes you are going to spend where so that should make no real difference.

I belive a key is there only for the DB. You shouldnt care what it is.

Remember a few years ago, there where a bunch of guys that thought they could "save a few byte" if they would store the YEAR with only a 2 digit value?

If you stick to integer key, then you will run into problems once you want to scale out... I mean diskspace isnt reall expensive these days...

Hatzi74 at 2007-10-6 >

# 14

Actually, (and you're not the only one ni the forum to make this mistake) a GUID in SQL Server is 32 bytes. MS Access uses a 16 byte GUID, but for replication only.

As far as using a GUID for a primary key, I'm working with a database that has 125M+ rows and a GUID defnied as the PK. The performance is horrible. I count this as one of my worse decisions.

If you truly need a globally unique identity, then by all means use a GUID. However, I would not make it the primary key except in the most extreme circumstances. I will be changing our databases to use an auto-increment bigint for the primary key and use the GUID as the record identifier.

ProfessorDave at 2007-10-6 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech