bigint or uniqueidentifer performance metrics?

If the use of the uniqueidentifier is now preferred, across how many
relations is it optimal to use as the type for primary keys? Is there some
point other than performance why one should not worry about reverting to the
use of bigint for related tables? I've only read a couple of stories about
it so far. What do you think?
<%= Clinton GallagherClinton
I would try to avoid using GUID as a Primary Key.
GUIDs take up 16-bytes of storage, more than an Identify column, which in
turn make the index larger, which increases I/O reads, which can hurt
performance. While the performance hit will be minimal if you do decide to
add a clustered index to a GUID column, every little bit adds up
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||That's what I read Uri but then on the next article GUIDs are praised not to
mention their use in the aspnet_Users table as well as other tables in the
ASP.NET 2.0 Membership, Roles and Profiles implementation Microsoft has
provided.
I think I'll just use their GUID as a foreign key and revert to bigint for
primary keys. It seems there are benefits for grabbing the last inserted
record using @.@.IDENTITY which GUID does not support.
I'm also trying to figure out how to use GUID as a primary key -- anyway --
and auto-generate the value using the newid( ) default binding. I've tried
learning to use newid( ) by entering data into the table but all I get to
see is red circles with ! and error messages or the red circles and no error
messages.
Thanks for your attention...
<%= Clinton Gallagher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqYqhBqOGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Clinton
> I would try to avoid using GUID as a Primary Key.
> GUIDs take up 16-bytes of storage, more than an Identify column, which in
> turn make the index larger, which increases I/O reads, which can hurt
> performance. While the performance hit will be minimal if you do decide to
> add a clustered index to a GUID column, every little bit adds up
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||This is kind of a confusing question, since you really need to give us more
information. Do you have tables that you need to store > 4 billion row? In
2005 there is a way to create an always increasing guid using
newsequentialid that can help a bit with the problems that using a GUID for
a key can create, but GUIDs make fine surrogate keys for tables, other than
performance and typability (the latter of which is just a programmer
convienience, so should not be really important to the question of whether
to choose them or not)
If you need the benefits of a GUID (like the UI can create them and be
guaranteed uniqueness,) then it might be worth it, otherwise use the correct
integer type. If you are going to have > 4 billion rows in many of your
tables, I hope you have spent a lot of money on a fast disk subsystem anyhow
because you will need it!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||Well, good point. I should be comparing an int rather than bigint.
If its true that deleting a record can leave gaps in the sequence of the
key's
value when using an int which can impact sorting the same must be true
when using the uniqueidentifier.
Furthermore, as I understand it even when using newsequentialid( ) there
is no @.@.IDENTITY to get the value of the last inserted record. Do you
know if this is true?
Other than uniqueness, I see no reason to use uniqueidentifier yet..
That's the basis of this inquiry.
<%= Clinton Gallagher
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
> This is kind of a confusing question, since you really need to give us
> more information. Do you have tables that you need to store > 4 billion
> row? In 2005 there is a way to create an always increasing guid using
> newsequentialid that can help a bit with the problems that using a GUID
> for a key can create, but GUIDs make fine surrogate keys for tables, other
> than performance and typability (the latter of which is just a programmer
> convienience, so should not be really important to the question of whether
> to choose them or not)
> If you need the benefits of a GUID (like the UI can create them and be
> guaranteed uniqueness,) then it might be worth it, otherwise use the
> correct integer type. If you are going to have > 4 billion rows in many
> of your tables, I hope you have spent a lot of money on a fast disk
> subsystem anyhow because you will need it!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
One of the differences between uniqueidentifier and identity is that a
uniqueidentifier value can be assigned by the application instead of SQL
Server. This eliminates the need to retrieve the surrogate key value after
insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Well, good point. I should be comparing an int rather than bigint.
> If its true that deleting a record can leave gaps in the sequence of the
> key's
> value when using an int which can impact sorting the same must be true
> when using the uniqueidentifier.
> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
> Other than uniqueness, I see no reason to use uniqueidentifier yet..
> That's the basis of this inquiry.
> <%= Clinton Gallagher
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||That was helpful to be reminded of as the DNF will indeed generate a GUID
consistent with the uniqueidentitifer.
<%= Clinton Gallagher
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OOChgd0OGHA.2696@.TK2MSFTNGP14.phx.gbl...
> One of the differences between uniqueidentifier and identity is that a
> uniqueidentifier value can be assigned by the application instead of SQL
> Server. This eliminates the need to retrieve the surrogate key value
> after insertion.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
>