rowguid as Primary Key ?

If you are using replication and need a rowguid, and you need a unique
primary key for the table anyway, is there anything wrong with using the
unique rowquid for the Primary Key? If not, what are the downfalls?
Thanks,
Steve
Using rowguids for a pk is a bad choice.
Please consult http://www.aspfaq.com/show.asp?id=2504
However you can use the rowguid column as your primary key and have merge
replication use it.
Requirements are
1) call the column rowguid
2) don't use a pk, but use a unique index - really they are the same thing,
only the unique index allows a single null value.
3) give the rowguid column the ROWGUIDCOL attribute
4) give the rowguid column a newid() default
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:E926325C-2B49-41C9-A7EC-71CD6A373C50@.microsoft.com...
> If you are using replication and need a rowguid, and you need a unique
> primary key for the table anyway, is there anything wrong with using the
> unique rowquid for the Primary Key? If not, what are the downfalls?
> Thanks,
> Steve

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

RowGuid

Hi

What impact is there in setting a unqiueidentifier primary key to be a row guid?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

I guess you don′t want to open up taht religious discussion, right ? There were many, many, many discussions already about this out there which can be find googling / MSNning around:

http://www.google.de/search?hl=de&q=GUID+Primary+Key&meta=

There are really many opinions out there, for me (for my personal opinion) you should avoid them, as long as you don′t really need them, like for distributed applications)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens

Thanks for the response but I wasn't asking if I should use a UNIQUEIDENTIFIER as a primary key.

I was just wondering what the impact was in setting the rowguid flag.

For example...

CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)

CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)

sql