RE: GUIDs

[Andrew McGrath]

Hi Anthony,
As part of your discovery process, you might find this CodeProject article 
useful.
http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

Regards
Andrew


From: "GregAtGregLowDotCom" 
Sent: Saturday, May 03, 2014 10:18 AM
To: "ozDotNet" 
Subject: RE: GUIDs

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: www.sqldownunder.com



From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] 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 produced significant performance improvements -- Greg K



On 2 May 2014 23:56,  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
Sent: ?Friday?, ?May? ?2?, ?2014 ?7?:?37? ?PM
To: ozDotNet



Guids are also great for offline distributed clients. AutoInc numbers will be a 
thing of the past.

On Friday, May 2, 2014, Jano Petras  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,  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 J



Anthony

RE: GUIDs

[GregAtGregLowDotCom]

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: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] 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, mailto:piers.willi...@gmail.com> > 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:rid...@gmail.com> 
Sent: ‎Friday‎, ‎May‎ ‎2‎, ‎2014 ‎7‎:‎37‎ ‎PM
To: ozDotNet <mailto:ozdotnet@ozdotnet.com> 

 

Guids are also great for offline distributed clients. AutoInc numbers will be a 
thing of the past. 

On Friday, May 2, 2014, Jano Petras mailto:jano.pet...@gmail.com> > 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, mailto:anthonyatsmall...@mail.com> > 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

 

 

 

 

Re: GUIDs

[Greg Keogh]

I did read a web page years ago where a chap reported that using sequential
Guids  produced
significant performance improvements -- *Greg K*


On 2 May 2014 23:56,  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 
> *Sent:* ‎Friday‎, ‎May‎ ‎2‎, ‎2014 ‎7‎:‎37‎ ‎PM
> *To:* ozDotNet 
>
> Guids are also great for offline distributed clients. AutoInc numbers will
> be a thing of the past.
>
> On Friday, May 2, 2014, Jano Petras  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,  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 J
>>>
>>>
>>>
>>> Anthony
>>>
>>>
>>>
>>>
>>>
>>
>>

Re: GUIDs

[piers.williams]

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
Sent: ‎Friday‎, ‎May‎ ‎2‎, ‎2014 ‎7‎:‎37‎ ‎PM
To: ozDotNet




Guids are also great for offline distributed clients. AutoInc numbers will be a 
thing of the past. 

On Friday, May 2, 2014, Jano Petras  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,  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 J

 

Anthony

Re: GUIDs

[Michael Ridland]

Guids are also great for offline distributed clients. AutoInc numbers will
be a thing of the past.

On Friday, May 2, 2014, Jano Petras  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, 
> 
> > 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 J
>>
>>
>>
>> Anthony
>>
>>
>>
>>
>>
>
>

Re: GUIDs

[Jano Petras]

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,  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 J
>
>
>
> Anthony
>
>
>
>
>