Preface: I'd like to not start a debate on whether tis nobler to use identity column for unique row identifiers or not and those kind of things. I'm just curious about performance.

When I build web applications, I often need to put the primary key of a table into the query string to pass the record selector to a page. If I were to use an identity column as my unique key, it would be very easy to just change the value on the query string. However, if I use a GUID, then it's much much harder. To use a GUID I could either create a GUID column (in addition to the identity int column) or just use the GUID as my primary key. I'm curious about the performance of the two data types when used as the primary key or as an additional index. Is one better than the other?

Peter
------------------------------------------------------
Work smarter, not harder.

__________________
-Peter
compiledthoughts.com
twitter/peterlanoie

(Must bite tongue.... )

The longer a key is, the more it adversely affects performance. An integer is 4 bytes, a uniqueidentifier (GUID) is 16. The index which contains this column (primary or otherwise) will be bigger, because fewer keys will fit in a given index block, which means there will be more index blocks, which means the b-tree will likely be "higher", resulting in more accesses to retrieve any given key, so things will be slower.

I don't understand why you'd want to create both an identity and a unqueidentifier column in the same table (unless you are using replication). Course, I don't understand why anyone would use a GUID if they weren't using replication, but that starts to get into forbidden territory, so I'll shut up. :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I thought I explained why I am using the GUID. I need a "hard to assume" identifier for the table because the identifier is going onto the querystring of a web application.

viewWidget.aspx?id=9FB58B72-914B-4a4f-BD4D-0C385DBD5958
    vs.
viewWidget.aspx?id=123

I would imagine it's much harder to get a possitive hit off a guess of a GUID than of an integer.

The only reason I'd have for using both ident and guid is to have the guid act as the "public" row identifier.


I hadn't thought about the fundamental aspect of datatype size. Now that you point it out I realize that it would therefore makes a lot of sense to use an integer over the GUID.

What reasons are there for using a GUID in an RDBMS table? Obviously the GUID is a meaningless bit of data in most all data models I would imagine. The thing I could come up with is where you have systems that interact and need to be able to shared data but not rely on one to generate the data element's unique identifiers (gee, looky there). In a self contained system, I suppose there's little reason to use them.

Peter
------------------------------------------------------
Work smarter, not harder.

I was taught that "security by means of obscurity" is not a smart thing to to. It gives you a false sense of security, while you're still vulnerable to abuse.

Either you protect the stuff you need to protect, or you don't. Design your application in such a way that people who are not allowed to access YourPage?ID=23 can't access it, even if they do have access to YourPage?ID=22.

Back to the performance question: I'd think that a GUID is slower, but I wonder if you'd really see the difference. I wouldn't be surprised if the difference was somewhere in milli or pico seconds.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

pico seconds?

Wow. How fast is your system, and where can I get one? :D

Seriously, I would tend to agree that a GUID key, since it is larger, is going to be slower, but the difference may be difficult to measure.

BTW, that's the same reason I use when arguing natural keys vs. identity keys. especially when the natural key is a string. People argue to use an identity because it is smaller, so faster than a natural string key. It's unlikely you'll see much difference.

But that argument is forbidden in this thread, so I won't make it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Well, it's a simple 486, 133 Mhz with 64 MB of RAM. I just overclocked it and tweaked SQL Server a bit. It's amazing what you can accomplish by configuring your system correctly.... ;)

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Guids on SQL Server can perform FASTER than numeric types, once you exceed about 100,000 rows on modern hardware. This is due to the indexing optimizations (cascading of the guid blocks) MS has implemented.

There are other pros to using Guids, too... you'll never run out of id space, you can generate the id's in a non-database place, and id maintenance when shipping data around becomes easier in many scenarios.

Sometimes, bigger is better!