Hi Greg,
I’ve never seen the point of NEWSEQUENTIALID(). It can only be used as a database default. If you’re already round-tripping to the database, you might as well pick up an int or a big int. To me, the reason for using GUIDs is when you want to generate the IDs in a different tier, confident that you can just throw them into the database later. Any of the sequential versions (even if client-generated), don’t give you that confidence. The biggest mistake I see people making is assuming that their database representation needs to match the layer above. Even if you use a GUID in the layers above, there’s no need to have them sprinkled throughout the database, fragmenting every table and to be joining on them. You could isolate that to one table. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: [email protected] [mailto:[email protected]] On Behalf Of Greg Keogh Sent: Saturday, 3 May 2014 10:09 AM To: ozDotNet Subject: Re: GUIDs I did read a web page years ago where a chap reported that using sequential Guids <http://technet.microsoft.com/en-us/library/ms189786.aspx> produced significant performance improvements -- Greg K On 2 May 2014 23:56, <[email protected] <mailto:[email protected]> > wrote: Probably worth saying that using guids as a primary key is not for everyone. The key is bigger, so that has a size and performance impact on all your indexes and foreign keys, and as a clustering key it means new records are scattered throughout the file rather than being appended to the tail, leading to logical fragmentation. (But if you need to replicate, synchronize or pre-allocate the key offline in the app tier they can make a lot of sense) From: Michael Ridland <mailto:[email protected]> Sent: Friday, May 2, 2014 7:37 PM To: ozDotNet <mailto:[email protected]> Guids are also great for offline distributed clients. AutoInc numbers will be a thing of the past. On Friday, May 2, 2014, Jano Petras <[email protected] <mailto:[email protected]> > wrote: Hi Anthony, Guids are easiest way forward - due to their uniqueness and native support by the DB engine. The only time I would consider using something else would be if there was a requirement for those unique row IDs to be 64bit integers for example or if there is a storage space concern - in this case I would consider using horizontal partitioning and allocating range of IDs to different instances reserving each one with a predefined range of values. On 2 May 2014 16:16, <[email protected] <mailto:[email protected]> > wrote: Anyone doing database replications, are you using guids? Have any recommendations or experiences? I don’t usually use guids but working on systems that may need to scale, so thinking of switching to guids to avoid any future scalability issues Thanks in advance :) Anthony
