Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

A Look at GUIDs Expand / Collapse
Author
Message
Posted Thursday, July 27, 2006 4:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 7:56 AM
Points: 33, Visits: 5

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.



. . .
Post #297693
Posted Thursday, July 27, 2006 6:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, 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

Post #297722
Posted Thursday, July 27, 2006 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 4:49 PM
Points: 138, 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.
Post #297763
Posted Thursday, July 27, 2006 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:37 PM
Points: 1, Visits: 5

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?

 

Post #297823
Posted Thursday, July 27, 2006 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 AM
Points: 14, Visits: 43
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.



Post #297893
Posted Friday, July 28, 2006 12:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #298008
Posted Friday, July 28, 2006 6:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 8:09 AM
Points: 6,779, Visits: 1,862

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
Post #298059
Posted Friday, July 28, 2006 6:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 8:09 AM
Points: 6,779, Visits: 1,862
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
Post #298060
Posted Friday, July 28, 2006 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 9, 2011 7:49 AM
Points: 343, Visits: 188
Why expose them to users - ever. I wouldnt inflict them on my worst enemy !!!!


Post #298068
Posted Friday, July 28, 2006 9:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 298, Visits: 335

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
Post #298107
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse