RowGuid as primary key

Cslah posted on Wednesday, April 25, 2007

I'm developing on SQL Server 2005 and our database needs to be 'replication-capable'. To do this we have added a Guid column, with a uniqueidentifier type and marking the RowGuid column property to True. We also have an integer ID column marked as the primary key.

My question is if I should make this rowguid column the primary key in the tables in our database and get rid of the integer primary key. Will it make any difference on the speed of the indexes or anything? We have to have this thing there anyways so the argument against it because it causes bloat is not valid. I would think that since we've got this RowGuid there, and it's unique, we might as well use it for the index. I searched on google and couldn't find anything that said it's a performance hit. Our tables aren't going to have millions (let alone tens of thousands) of records.

Any comments on this?

B

Bayu replied on Thursday, April 26, 2007

Funny question actually,

You moved from integer primary keys to Guid primary keys because you could not ensure uniqueness of the integers across replicated tables.

And now you ask if you would need to keep the integer primary key .....!? :D

You answered your own question already I believe. ;-)


In case of replicated tables you cannot ensure unique integers. So in fact it CANNOT even be a primary key any more. You would have to get rid of the primary key constraint anyways, and when you have done that you will be left with an integer column that is of no use whatsoever.

Hence, imo: ditch it! ;-)

Regards,
Bayu

xal replied on Thursday, April 26, 2007

Here's a good article that talks about performance and shows some test results:

http://www.informit.com/articles/article.asp?p=25862&seqNum=1&rl=1

Andrés

ajj3085 replied on Thursday, April 26, 2007

It will impact peformance somewhat, if you do not use what is called a sequencial guid.  For a replication scenario though, I would avoid such guids, because I'm not sure how the generation of the SG affects the chance of a collision. 

You can add a repl_id column, which contains straight guids to use for replication only.  That's probably your best bet, and the least amount of work.

HTH
Andy

Cslah replied on Thursday, April 26, 2007

Thanks everyone. 

I haven't actually removed the integer primary key yet and have both it and the RowGuid column (which is only there for, and marked for, replication) in my DB. I was just pondering if it makes sense to combine them.

I posted this question on the sql server replication newsgroup also. It seems that SQL Server takes care of your integers colliding when replicating different databases. For example, on two different databases that are replicated, if you create a new item on each one, the PK is 1 on each record in both databases, but the data is different. Replication somehow will resolve this using the RowGuid column and probably a combination of the int primary key.

Thanks for the responses.

Brian