SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Look at GUIDs


A Look at GUIDs

Author
Message
JB-242273
JB-242273
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 6

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.



. . .
Jesper-244176
Jesper-244176
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 33

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


Jonathan Schafer
Jonathan Schafer
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 46
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.
Mike Lu-213884
Mike Lu-213884
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14

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?


zootie
zootie
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 48
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 Documentation
I 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.aspx
Blog 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.aspx
A 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.html
SQL Server Magazine article about it (if you have a subscription).

http://www.sqljunkies.com/WebLog/odds_and_ends/archive/2005/09/06/16664.aspx#comments
Someone 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=1
More 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.aspx
A 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=275248
Also, 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.



ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28787 Visits: 8986

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;



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24719 Visits: 2746

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24719 Visits: 2746
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Stewart Joslyn
Stewart Joslyn
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 188
Why expose them to users - ever. I wouldnt inflict them on my worst enemy !!!! :-)



BobAtDBS
BobAtDBS
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 365

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.




Student of SQL and Golf, Master of Neither
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search