RowGuid as primary key -- sql server replication

sql server replication : RowGuid as primary key

Brian
4/25/2007 6:52:00 PM

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

Hilary Cotter
4/25/2007 11:13:17 PM

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.

[quoted text, click to view]

Hilary Cotter
4/26/2007 12:00:00 AM

Or you might want to look here -

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

[quoted text, click to view]

Paul Ibison
4/26/2007 1:16:00 AM

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,

Brian
4/26/2007 8:58:01 AM

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

Paul Ibison
4/26/2007 9:48:02 AM

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,

Brian
4/26/2007 10:28:01 AM

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.

Paul Ibison
4/26/2007 11:19:11 PM

Hi Brian,
if you're talking about merge, you can enable automatic range management of
the identity values (done for you in 2005 but manually set in 2000).
Cheers,
Paul Ibison

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

View Other Months
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn