SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / A Look at GUIDs / Latest Posts

RE: A Look at GUIDs

adi - doccolabs.com — Tue, 02 Mar 2010 09:56:06 GMT

Posted an article about GUID's in my blog - see it [url=http://bit.ly/afeq3y]here[/url]

RE: A Look at GUIDs

Ed Hammar-581110 — Thu, 13 Dec 2007 09:51:59 GMT

[quote][b]Kay Mohd (12/12/2007)[/b][hr]Question:What kind of mess i'll be seeing in the future?Can't GUID size indexing problem be solved with partitioning?Can you suggest other alternatives to GUID, based on the above scenario?Thanks in advance[/quote]- I'm not sure what you mean by the first question. You might be asking if using GUIDs will make your data more difficult to deal with and the answer is, a little. It's a little easier for a human to look up record number 82342 in two or more different tables than it is to look up record 7823F847-EA50-4D3B-BBE2-6DD2B093C8B8 in two or more different tables. So when you are researching a problem INTEGER ID's make things slightly easier. You might ask yourself how hard is it to join two or more tables though.- In your situation a GUIDs size poses less of a problem because some of the other options require mutiple keys which causes the alternative indexes to begin to approach the size of the GUID if not exceed it.- The most obvious alternative is to use a compound key in your situation. Include two columns in your tables, one a server identifier and the other a record identifier. This compound key can be used to uniquely identify each record regardless of which database it's replicated to. Another alternative is to partition your IDENTITY values into ranges and assign each server a range, the problem with that approach is the ranges are finite and eventually you might have to do some maintenance to avoid a key collision. I really despise the partition/range approach as there are some many pitfalls with it. One location might do more business than another yet you might not know that initially and assign the same size range to each location which will cause you to have to do far more maintenance to keep the high volume location up and running than is required to keep the low volume location up and running.By the way, I would never recommned the IDENTITY partition/range approach in you situation. I've seen it implemented a number of times and it was always ugly and very costly.

RE: A Look at GUIDs

Kay Mohd — Thu, 13 Dec 2007 03:49:23 GMT

Thanks andy, it's a relief to know that using GUID is not really a recipe for disaster.Most probably i will partition by projectID.

RE: A Look at GUIDs

Andy Warren — Wed, 12 Dec 2007 06:06:33 GMT

I think GUID's are made for that kind of scenario. All the same pros and cons apply! As long as the hardware isn't fully utilized already I suspect it will absorb the overhead of the CPU/space without issue. Partitioning works best when you really have a good partitioning criteria, not something I'd suspect would match a guid. More likely is an order date or location id.

RE: A Look at GUIDs

Kay Mohd — Wed, 12 Dec 2007 00:25:18 GMT

Since GUID has turned into chicken-egg arguments, wonder if anyone can really advise me on this issue:Here's my scenario, a purchase order (PO) application: We want to have a centralized database with remote sites connected to it.Some of the sites are without connection, they will have their own servers with scheduled replication to the centralized database.The schema design is something like this:Each PO will have many revisions Each revision will have many PO line itemsEach PO line item will have many Delivery SchedulesIn the past i used int IDENTITY as transaction ID in revision and line item tables. transaction ID in revision table is FK to line item table, and transaction ID line item table is FK to Delivery Schedules table.This work well in standalone database.Now that we need to merge replicates, int IDENTITY produced in remote DB will conflict with IDENTITY produced in central DB.I'm thinking of using GUID to replace int IDENTITY.Question:What kind of mess i'll be seeing in the future?Can't GUID size indexing problem be solved with partitioning?Can you suggest other alternatives to GUID, based on the above scenario?Thanks in advance

RE: A Look at GUIDs

Jwalant Natvarlal Soneji — Fri, 27 Jul 2007 22:42:00 GMT

Dear friends, the whole discussion was interesting to learn, but for a small developer like me, its cumbersome to use guid as a single query run to look for a particular id in the table having 3 lacks record takes more considerable amount of time than using integer.

Also when I use the lookup in MS BIDS and the column is guid, i suspect it takes too much time.

Thanks,

Jwalant Natavarlal Soneji

RE: A Look at GUIDs

Ed Hammar — Fri, 27 Jul 2007 11:52:00 GMT

There is one additional complication on the database side. While the uniqueidentifiers are guaranteed unique, they are not guaranteed to be sequential. That means as you generate new uniqueidentifiers as primary keys and insert them into a table there is a much higher chance you'll cause a page split. SQL stores data on 8k pages, a page split occurs when there is not enough room on the page for the new row, so the page is basically split into two pages so that the insert can success. Page splits in general are not bad, it's the frequency of page splits that can have an impact on performance as each split requires additional locking and disk IO. In my view the worry about page splits is overblown, but it's something you'll have to access in your environment to be sure. Fast drives do much to alleviate this potential issue.

Although I think your article was good and I'd love to see more people taking advantage of GUIDs in their database designs, I have one issue with your article. The above excerpt. I'd love to know your source for this information and I'd love for you to further explain how page splitting will be increased due to the nature of GUIDs. I've never found any Microsoft documentation warning of such a scenario and what you claim goes against my understanding of how b-trees work.

My concern is that your statement is the opposite of the reality. Assuming that we can agree that SQL Server's indexes are all b-trees, based on my understanding of b-trees, if you insert a preordered set of records into a b-tree more page splits will occur than if you had inserted those same records in a random order. Additionally, page splits isn’t what's kills performance rather rebalancing of the b-tree is what is so detrimental to performance. Inserting rows into a b-tree in order causes the tree to become unbalance far more frequently than if rows were inserted into the tree in a random order.

In any case I'd love to know where you got that information or have you explain it more thoroughly.

RE: A Look at GUIDs

zootie — Fri, 27 Jul 2007 09:48:00 GMT

Mike, as mentioned in other posts (and w/o trying to convert you, just trying to help with your legacy headache), one common mistake when using GUIDs is to cluster using only the GUID PK (ie, the cluster and PK are the same index). An easy solution to avoid many of the problems you mention is to just add a creation datetime field before the GUID field, and cluster on it (while the PK is still the GUID by itself). Mosttimes, this gives you the best of both worlds. The main drawbacks are that your need a creation datetime field (which is typically a non-issue when you're compliance minded) and that your cluster index key will require more space, and all indexes will be slighlty larger (which is not that much when you consider that you're already using a GUID). However, IMO the benefits are well worth it...

RE: A Look at GUIDs

Jamie Thomson — Fri, 27 Jul 2007 08:23:00 GMT

Hi Mike,

Great post. Its good to have some real-world experiences on here. It kind of confirms what I was already thinking as well.

Do you have any stats about this "dramatic improvement"?

-Jamie

RE: A Look at GUIDs

Michael MacGregor — Fri, 27 Jul 2007 08:15:00 GMT

The database that I have inherited is infested with GUIDs. I should probably at this point inform you that I am not a fan of GUIDs, never have been and probably never will be especially after having to work with this schema.

The original reasoning behind choosing GUIDs as the clustered PKs was reasonably sound, to provide uniqueness to values that are generated at divers locations. However, due to the nature of the system with a high record insertion, the GUIDs are causing performance issues, locking up resources while page splits occur, blocking out waiting processes and ultimately resulting in timeouts.

Sure we could increase the timeout duration, but, to be honest, the wait time involved is unacceptable in our particular system, and not just from our perspective but most especially from our customers who do not appreciate delays.

You mention about hot-spots caused by using identity keys, well I've never yet had any issues with those, and I have been able to dramatically improve performance by eliminating the GUIDs in favour of identity. This on SQL 2005 as well.

However, the article was definitely thought-provoking and I did in fact learn something new, which is always a good thing, and for those of you who like using GUIDs, all power to you, I'm just not about to be a convert.

Mike

RE: A Look at GUIDs

Andy Warren — Wed, 02 Aug 2006 08:33:00 GMT

I also received a question about whether both implementations guaranteed unique. I asked Steve to follow up with MS to get a 'good' answer, and Paul Randal from the storage team was kind enough to provide this answer:

"These two intrinsics are thread-safe, and multiple concurrent users will get different UUIDs. http://blogs.msdn.com/sqlprogrammability/archive/2006/03/23/559061.aspx gives more detail on this topic."

RE: A Look at GUIDs

alexweatherall — Wed, 02 Aug 2006 07:46:00 GMT

Another caveat about using GUIDs as keys:Beware of using non-sequential GUIDs as a non-clustered PRIMARY KEY on large historical data. If you want to delete a block of data (say all events before '2006-01-01'), even if your clustered index is on your eventdate column, it will have to remove nodes from your non-clustered index for all rows affected by the DELETE statement. If your non-clustered index is on a "random" GUID, then this will hit a high percentage of pages on your index (with large data = very slow!!!). This occurs even if you batch your DELETE (with SET ROWCOUNT and a loop). If the GUID was sequential, then the nodes will be together and the DELETE from index operation would be much faster. If you use the COMB technique described elsewhere in this discussion (I think there was a link earlier) or a sequential key (int, bigint, sequentialguid etc), then this problem is somewhat alleviated.Or drop and recreate the index, but that could be expensive too!

RE: A Look at GUIDs

tymberwyld — Wed, 02 Aug 2006 06:48:00 GMT

Yes, I've always had it in mind to re-think the key. I like your idea and I'll look into it. We don't use the keys for anything really (meaning no one tracks them). I had thought to move the date portion to YYYYMMDD but never got around to it. One thing I was also thinking of doing was to move the Clustered Key from this PKey to some other columns, but I could never really think of a good key for this.Thanks for the input!

RE: A Look at GUIDs

alexweatherall — Tue, 01 Aug 2006 16:35:00 GMT

You might not be seeing a performance hit if your looking at events user by user - but it may get slower the longer you run this model (depending on activity rates?). Also, if using this generated key structure, store your dates as YYYYMMDD, then they'll be indexed by User and in date order (other wise you won't get all the events from one year together?!?) Surely you would be better storing this data in a binary column instead of a varchar column. It would take up 16 (GUID) + 4 (date) + 2 (seqno) = 22 bytes instead of the 50ish bytes taken up by the string. This allows for your required generated key (not questioning your choices of your primary key structure here - thats another topic!), just the data size. This would reduce your PK column size by half, improving index performance and size.If it's a hidden key, then it doesn't matter that it's binary column (ie not human readable), and it will be more efficient (slightly) to "decode" it as well.So you could generate the column data like this:DECLARE @UserUID uniqueidentifierDECLARE @Date datetime -- or char(8)DECLARE @SeqNo smallintDECLARE @GeneratedActivityKey binary(22)SET @UserUID = NEWID()SET @Date = GETDATE()SET @SeqNo = 22SET @GeneratedActivityKey = CONVERT(CONVERT(binary(16),@UserUID)+CONVERT(binary(4),@Date)+CONVERT(binary(2),@SeqNo))SELECT @GeneratedActivityKeywill give something like 0x00171A973D6A436E8536A0788420686C00012ABC0012Only taking up 22 bytes and still giving you your clustered pages (by user,date,seqno)It would probably be more efficient on selects to use a composite key however.

RE: A Look at GUIDs

Stewart Joslyn — Mon, 31 Jul 2006 07:33:00 GMT

In this scenario, the GUID is not unique in the table so I would expect exactly the same result if you were using an integer or anything else as the user id - except for any saving from having a shorter index key.I assume that you mainly are concerned with searching by user - if by date then changing the order of the key components might show up in insert performance.

RE: A Look at GUIDs

tymberwyld — Mon, 31 Jul 2006 07:27:00 GMT

We've used GUIDs a lot in one implementation. This was a time-tracking system and one of the things we implemented was in the Activities table, the PKey was a concatenation of the User's GUID Key + the Date + a SeqNo (ex. {00171A97-3D6A-436E-8536-A0788420686C}:01032006-0001). Mostly, we used GUIDs at the Parent Entity levels in the DB (Users, Companies, etc) and concatenations at the Child levels.I've never seen a performance hit from doing this even though the PKey is really a VarChar(70). I understood the risks of page splits, but I think in this case there MIGHT be less page splis because the Rows for each user would be lumped together on the same page(s) (as much as possible). This probably accounts for the reason that there really isn't a performance hit when selecting rows.

RE: A Look at GUIDs

jim650313 — Sun, 30 Jul 2006 23:42:00 GMT

We use GUID's for an object relational mapping (ORM) layer in our framework. This allows clients to use the ORM for their own use (that we may or may not have knowledge of) to add tables / objects etc. We can confidently add to the ORM with out fear of a collision. Also GUID's are used (not as a primary key) to provide a unique reference enable collaboration with other software eg CRM, Finance systems. So far it has served us well.JS

RE: A Look at GUIDs

B. Hedge — Fri, 28 Jul 2006 14:31:00 GMT

I think that GUIDs are more valuable than most people give them credit for. There is a debate about the performance impact but if used wisely to set up a good live archive system then performance in the primary production DB will improve. I also like to use GUIDs on frequently updated records and rows. Instead of doing record locking I update the GUID after every update... my business layer has the ability to display the differences to the losing update.

RE: A Look at GUIDs

BobAtDBS — Fri, 28 Jul 2006 09:23:00 GMT

Heck, I would! Making my worst enemy type Guids all day would be awesome!

Seriously, there is no reason for any user to ever even see a Guid, much less need to type one into an input box.

RE: A Look at GUIDs

Stewart Joslyn — Fri, 28 Jul 2006 07:00:00 GMT

Why expose them to users - ever. I wouldnt inflict them on my worst enemy !!!! :-)

RE: A Look at GUIDs

Andy Warren — Fri, 28 Jul 2006 06:25:00 GMT

To clarify, sequential guids CAN be guessed easily compared to trying to do the same for the standard GUID. Thus the security risk if you expose them to users.

RE: A Look at GUIDs

Andy Warren — Fri, 28 Jul 2006 06:24:00 GMT

First, thank you to everyone who read and also posted a comment! I think I could probably frame it a bit better now, but I have an older article that starts to address the "why" at http://www.sqlservercentral.com/columnists/awarren/usinguniqueindentifierinsteadofidentity.asp.

As to performance, it may seem sacrilegious, but not every decision you make has to be designed to get the absolute best performance. You have to look at the whole picture and the cost. Ten years ago, most people would have considered you out of your mind if you said you were going to use 3000 minutes on a cell phone each month, or turn off your land line for a cell phone. Since then the economics have changed. The same is true for the PC industry, CPU speed is fantasic, storage costs are down, storage access times are down, memory if not cheap at least affordable, and the ability to use more than 4-8g of memory is finally here. It's possible to bog down even the best hardware of course, but I'll argue that in most circumstances modern hardware masks any performance hit from using guids to the point that it rarely matters.

RE: A Look at GUIDs

ALZDBA — Fri, 28 Jul 2006 00:15:00 GMT

I like the article, but concering the newsequentialid ...

can someone find the flaw in my testquery which proves they'r not sequential ???

Declare @Test table (

MyNewSeqId uniqueidentifier DEFAULT newsequentialid(),

MyIdentity int not null identity(1,1),

MyNewId uniqueidentifier DEFAULT newid(),

Inserted datetime DEFAULT getdate()

);

set nocount on;

declare @x integer;

set @x = 0;

while @x < 10000

begin

INSERT into @Test DEFAULT VALUES;

set @x = @x + 1;

end;

set nocount off;

with cteMyGuidTest (MySeqNo, MyNewSeqId, MyIdentity, MyNewId, Inserted, MyNewSeqId_String3)

as (

SELECT row_number() OVER(ORDER BY substring(convert(char(36) , MyNewSeqId),1,3), MyNewSeqId ASC)

, MyNewSeqId, MyIdentity, MyNewId, Inserted

, substring(convert(char(36) , MyNewSeqId),1,3) as x

FROM @Test

)

select T1.MySeqNo, convert(varbinary(100),T1.MyNewSeqId) as MyNewSeqId1_Binary,T1.MyNewSeqId, T1.MyIdentity --, T1.MyNewId -- , T1.MyNewSeqId_String3

, T2.MySeqNo, convert(varbinary(100),T2.MyNewSeqId) as MyNewSeqId2_Binary, T2.MyNewSeqId, T2.MyIdentity --, T2.MyNewId --, T2.MyNewSeqId_String3

from cteMyGuidTest T1

inner join cteMyGuidTest T2

on T1.MyNewSeqId_String3 = T2.MyNewSeqId_String3

and T1.MySeqNo = T2.MySeqNo - 1

order by convert(varbinary(100),T1.MyNewSeqId) , T1.MyNewSeqId, T2.MyNewSeqId;

with cteMyGuidTest (MySeqNo, MyNewSeqId, MyIdentity, MyNewId, Inserted, MyNewSeqId_String3)

as (

SELECT row_number() OVER(ORDER BY substring(convert(char(36) , MyNewSeqId),1,3), MyNewSeqId ASC)

, MyNewSeqId, MyIdentity, MyNewId, Inserted

, substring(convert(char(36) , MyNewSeqId),1,3) as x

FROM @Test

)

select T1.MySeqNo, convert(varbinary(100),T1.MyNewSeqId) as MyNewSeqId1_Binary,T1.MyNewSeqId, T1.MyIdentity --, T1.MyNewId -- , T1.MyNewSeqId_String3

, T2.MySeqNo, convert(varbinary(100),T2.MyNewSeqId) as MyNewSeqId2_Binary, T2.MyNewSeqId, T2.MyIdentity --, T2.MyNewId --, T2.MyNewSeqId_String3

from cteMyGuidTest T1

inner join cteMyGuidTest T2

on T1.MyNewSeqId_String3 = T2.MyNewSeqId_String3

and T1.MySeqNo = T2.MySeqNo - 1

order by T1.MyNewSeqId, T2.MyNewSeqId;

RE: A Look at GUIDs

zootie — Thu, 27 Jul 2006 12:37:00 GMT

NewSequentialId was interesting, but not being able to call it directly was a non-starter for us.I found 2 implementations for NewSequentialID in an extended stored procedure, so you could use it in SQL 2000: xpguid and yaflaguid (the later includes source code, it also has an implementation of an alternate algorithm)This is an email I originally sent to some coworkers, with some links for more information.MSDN NewSequential ID DocumentationI came across this new SQL 2005 function - NewSequentialID() -, and I thought you might be interested.Microsoft’s first implementation of Universally Unique IDs (GUIDs) used the network card MAC address, which is unique by itself, plus some randomized and counter data. However, there was some uproar about it infringing on privacy: it was first used in Office as internal document structure identifiers, and it was possible to determine the originating computer based on the ID (since it embedded the 6 byte MAC address). MS changed the default algorithm so you wouldn’t use the MAC address (I think it still uses it, but only as an aid, and you couldn’t reconstruct it from it). NewSequentialID()/UuidCreateSequential go back to using the MAC address for a simplified algorithm with some advantages on a DB design (ie, clustering on the primary key)http://blogs.msdn.com/sqlprogrammability/archive/2006/03/23/559061.aspxBlog entry describing the SQL developer’s logic and some notes on the implementation of NewSequentialID() by calling UuidCreateSequential. It’s interesting that they saw duplicate GUID generation on AMD 64 systems (and that they fixed it, albeit using a mutex). This is probably the reason why you can't call it directly.http://scrappydog.com/blogs/blog/archive/2005/11/14/9380.aspxA blog entry indicating that duplicates are being generated. However, his interpretation of the manual kind of makes me think that he might have some other issues. One of the posts kind of implies that there might be an issue with uniqueness on a cluster. You probably want to research it further if you want to use this function. http://www.sqlmag.com/Article/ArticleID/50164/sql_server_50164.htmlSQL Server Magazine article about it (if you have a subscription).http://www.sqljunkies.com/WebLog/odds_and_ends/archive/2005/09/06/16664.aspx#commentsSomeone that sat down to test performance of a GUID vs integers. The conclusion was that if you design properly, there is little or no difference in performance. What no one seems to have stated is that (I think) the reason why a GUID is so close to an integer in performance is because the CPU word size has gotten bigger, and because the CPU is routinely waiting for other subsystems, and because the extra overhead involved in using data types smaller than the CPU word size more than compensates for the GUIDs large size. This is even truer now with 64 bits CPU (which is kind of why I initially justified GUIDs to myself when the first details on the Itanium where available).http://www.informit.com/articles/article.asp?p=25862&redir=1&rl=1More performance testing, and another alternative to random GUIDs, COMBs – using dates combined with a guid to generate an ordered GUID (same/similar thing the one above does in one of the tests).http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspxA long thread on the good and bad of GUIDs (I found some of the links above here).http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=275248Also, it seems that the table designer is not aware of NewSequentialId() as a valid default value, and it displays some warnings when you create/modify a table using it. I don't know if SQL 2005 SP1 fixed this.

RE: A Look at GUIDs

Mike Lu-213884 — Thu, 27 Jul 2006 09:36:00 GMT

Nice artical. We had an argue about using GUID or int as a key, and this artical clarifies some concerns.

I found a confusing part, in the artical you say "as noted in BOL because of the way they are calculated, it is possible someone could guess the next key." I guess you intended to mean "it is NOT possible someone could guess the next key.", correct?

RE: A Look at GUIDs

Jonathan Schafer — Thu, 27 Jul 2006 07:46:00 GMT

I don't add to each real table. I only add them to table variables when I need to do additional work on the data. The ORDER BY clause would work too.

RE: A Look at GUIDs

Jesper-244176 — Thu, 27 Jul 2006 06:15:00 GMT

Thanks for a great article, Andy. I am in the process of designing a client/server system (.NET and SQL Server 2000/2005) in which GUIDs are used as clustered primary keys throughout the database in order to reduce the number of round trips between client and server. But then I found an article by Zach Nichter, which gave me second thoughts:

http://www.sql-server-performance.com/zn_guid_performance.asp

It seems that the two of you basically agree on the downsides of using uniqueidentifiers as clustered primary keys. But it seems that while Zach's conclusion is that this should be avoided, you are much more positive. Is this correct? And does this mean that I shouldn't redesign my system after all? I guess the answer is "it depends", but that's OK

RE: A Look at GUIDs

JB-242273 — Thu, 27 Jul 2006 04:08:00 GMT

Probably u don't need to add GUID column for each table to get random sets.

u a can achieve it by adding NEWID() in the ORDER clause.

RE: A Look at GUIDs

John Mitchell-245523 — Tue, 25 Jul 2006 06:23:00 GMT

Not if you use bigint, which is 8 bytes. Then you can have more than 18 billion billion records in your table.

John

RE: A Look at GUIDs

Eric-340515 — Mon, 24 Jul 2006 08:32:00 GMT

One reason not to use integer PKs and instead use GUIDs is if the table is potentially going to exceed 4 billion records (for instance, the history table to your primary transaction table). A 4 byte integer holds values between -2,147,483,648 to +2,147,483,647, so you are in trouble if any table exceeds 4.2 billion records.

RE: A Look at GUIDs

BobAtDBS — Mon, 24 Jul 2006 07:16:00 GMT

Excellent, factual article Andy.

I started using GUIDs as Primary Keys for the very reasons mentioned in your article several years ago (SQL 7 also has NEWID as a function). My criteria was

1) Being able to generate the PK from the client and passing it to associated recordsets long before uploading the batch to the server. This was really big for us, our clients are typically on a slow connection, a long ways from the server.

2) Designing the database ready for replication, where a GUID is going to get added to the table anyway (This really kills the 16 byte versus 4 byte argument, if you use replication you'll end up with both if you start with the Identity field).

3) Deciding that the performance issue with page splits was pretty bogus for our particular usage patterns. Few folks ever mention that using GUIDs for PKs solves another problem at the same time. If you use Identity as your PK and have a lot of inserts, you end up with one hot spot on the hard disk where all the activity is taking place, with everyone needing to update the same page. With GUIDs, the activity gets spread around a bit. In my experiments (six years ago), I got higher insert rates with GUIDs than with Identity values.

The purists (you know who you are) always trot out the "a good primary key should be a natural key" argument. I just don't care. It is really nice to be able to do every join on a 1 to 1 FK to PK basis. It is really nice to know that no user, no manager, no third party is EVER going to need to change a PK because somewhere, someone allowed a PK with a value that has human readable significance.

RE: A Look at GUIDs

Stewart Joslyn — Mon, 24 Jul 2006 06:57:00 GMT

I take your point but surely this is the very last line of defence. All keys should be internal to the app / db and invisible to the user who should not have any direct access to the tables anyway. All comms, of course, should be encrypted for virtually any system.

RE: A Look at GUIDs

milzs — Mon, 24 Jul 2006 06:49:00 GMT

GUIDs also add a level of security, as was noted in the article, which is especially important in government and military developed applications. I develop a number of security-related and classified web and client/server applications, and using a GUID as the primary key helps prevent users from seeing records that are not theirs. If we exposed an Identity PK that had values 1 and 4 for a specific user, it doesn't take a rocket scientist to figure out that there are probably records with IDs of 2 and 3 too. Depending on how securely written the app is, it may or may not be easy to get at those records. Very difficult to "guess" like this when using GUIDs. Despite the performance hits a GUID may come with, we find that the benefits of GUIDs from a security point are worth it. This logic probably has merits in other arenas too, especially e-commerce and financial-related systems. My 2 cents... Steve

RE: A Look at GUIDs

Stewart Joslyn — Mon, 24 Jul 2006 01:42:00 GMT

There always is the risk that GUIDs are only unique if the network cards generating them have unique ids. They're supposed to but not if they're clones!The scenario of generating ids on the client without the need for round trips is addressed more easily by giving each client an unique integer id (I assume that you know who the clients are - not just anonymous access) and concatenate that with the client's internally generated unique integer ids for each object. That gives, at worst, 8 bytes instead of 16, provides an audit trail identifying the originating client and is human readable.I see human readability as a major disadvantage of GUIDs - they make debugging and issue investigation a nightmare!

RE: A Look at GUIDs

Jonathan Schafer — Sun, 23 Jul 2006 22:20:00 GMT

I never use GUID's as primary keys. However, I have found them very useful in the following scenario. Because the generated numbers are essentially random and unique, whenever I need to randomly select a number of records from a group, I assign a GUID to each row. Assume a table with 1000 records and I want to select 100 at random. I can assign a GUID to each row, and then select the top 100 ordering by the GUID. Because the generated GUID's are random, I know that the rows that get selected will be sufficiently randomized. This has worked well for me in the past.

Jonathan

RE: A Look at GUIDs

Jamie Thomson — Sun, 23 Jul 2006 13:38:00 GMT

Not a bad article Andy. I learnt some stuff I didn't know before.

Having said that, the article was a "How" to use them. I would really likemto see a "Why" I should use them Specifically, why would i use them rather than generate sequential IDs (which is what I do currently - I don't use IDENTITY).

-Jamie

RE: A Look at GUIDs

Richard Yeo — Sun, 23 Jul 2006 13:17:00 GMT

If you are writing cross platform code then using GUID's could certainly be useful. Different RDBMS's implement Identity columns in different ways. Returning Indentity values has always been a bit of a pain. I can see advantages of using GUID's particularly if you don't like using stored procedures and keep your logic in the middle tier.

A Look at GUIDs

Andy Warren — Tue, 11 Jul 2006 10:27:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/alookatguids.asp