RowGuid as primary key

I'm developing on SQL Server 2005 and our application needs to be
'replicable'. I can see how this is done using a RowGuid and marking to be
used for replication, no problem.
My question, however, is if I should make this rowguid column the primary
key in the tables in our database. Will it make any difference on the speed
of the indexes or anything? I would think that since we've got this RowGuid
there, and it's unique, we might as well kill two birds with one stone...
Any comments on this?
If you are using transactional replication the key should be based on the
int data type, preferably using the identity property. If you are using
merge replication let it create a rowguid column along with the unique index
it will also create.
"Brian" <noone@.discussions.microsoft.com> wrote in message
news:8CD86631-6C4D-416C-8831-2010DF1F487F@.microsoft.com...
> I'm developing on SQL Server 2005 and our application needs to be
> 'replicable'. I can see how this is done using a RowGuid and marking to be
> used for replication, no problem.
> My question, however, is if I should make this rowguid column the primary
> key in the tables in our database. Will it make any difference on the
> speed
> of the indexes or anything? I would think that since we've got this
> RowGuid
> there, and it's unique, we might as well kill two birds with one stone...
> Any comments on this?
|||I'd not recommend this. Have a look on the programming newsgroup for a list
of reasons why this is considered a bad idea
My main reason would be space - it occupies 16 bytes, whereas an integer
used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore the
index will be narrower and faster to search.
Rgds,
Paul Ibison
|||Or you might want to look here -
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:9FADF7A1-C019-4327-8D29-B7B8562AE335@.microsoft.com...
> I'd not recommend this. Have a look on the programming newsgroup for a
> list
> of reasons why this is considered a bad idea
> My main reason would be space - it occupies 16 bytes, whereas an integer
> used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore
> the
> index will be narrower and faster to search.
> Rgds,
> Paul Ibison
|||Yes, normally I wouldn't consider it, but for replication it has to be there
anyways. So if it HAS to be there, is it bad to use it for the primary key
also?
|||You could add your own guid with the rowguid property and set it as the OK
but there would still be the issue that the index would be a lot wider than
an int index, and hence slower.
Cheers,
Paul Ibison
|||Thanks Paul. It sounds like having an int primary key along with the RowGuid
for replication is the way to go. I've been pondering and doing web searches
for how SQL Server maintains the int between machines with replication. I
haven't really found any problems with this so I'm assuming that SQL server
completely takes care of this for us.
Brian