Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There's a long-winded debate going on here so I'd like to hear other opinions.

I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not going to change anytime soon).

Now, the database has to be merge published and the DEVs are advocating the use of a separate rowguid column instead of marking the existing PK as the ROWGUIDCOL.

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

From a performance standpoint, I see no reason why I should add a new column to do something I could do with an existing one. Moreover, since it's only "DBA stuff", why not let the DBA choose?

I kind of understand the DEVs' point, but I still disagree.

Thoughts?

EDIT: I just want to add that I'm in the minority in this debate and the DEVs questioning my position are people I respect and trust. This is the reason why I resorted to asking for opinions.
I might also be missing something and could have misunderstood their point.

share|improve this question
    
Is there any chance that a PK value may need to change in the future? –  Robert L Davis May 30 '13 at 17:22
    
No, not really. It's a surrogate key, so the app doesn't really do much with that column. It never changes and it's never displayed to the users. –  spaghettidba May 30 '13 at 17:40
    
Why do they want a separate rowguidcol? And what scheme in general would they choose, if they could, for the PK, and why? –  JustinC May 30 '13 at 20:15
add comment

4 Answers

up vote 5 down vote accepted

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

I agree completely. But... the primary key isn't only used for replication (presumably the application uses it in some way). The argument makes no sense in this context.

In any event, as far as I'm aware, there are only 2 ways for this "DBA stuff" to cross the domain boundary:

  1. If the application is using queries that reference the ROWGUIDCOL column like this:

    DECLARE @a table (id uniqueidentifier ROWGUIDCOL);
    
    SELECT ROWGUIDCOL FROM @a;
    

    I'm assuming none of your columns have this property yet, so the application wouldn't be doing this. (By the way, ROWGUIDCOL is a completely separate concept from replication. It just so happens that merge replication uses it.)

  2. The primary key column would no longer be updatable. If the application is doing this and changes aren't going to be made to use another algorithm, there's no choice but to add a new column to the table, and therefore no discussion is necessary.

Other than those behaviours, the ROWGUIDCOL property is completely transparent. You can add it, and the application would never know. Any type of data replication scenario should be as transparent as possible to applications.

share|improve this answer
    
Thanks for answering. No, the application isn't doing 1. nor 2. For sake of completeness, some tables are already decorated with the ROWGUIDCOL property in the PK. –  spaghettidba May 30 '13 at 21:13
    
While I agree that replication should be transparent to applications, I also believe that databases that have to be published must be thought for replication from the ground up. Identity management in merge replication is totally PITA, for instance: if you know from the start you'll have to merge publish, it's one thing to stay away from. –  spaghettidba May 30 '13 at 21:18
    
@spaghettidba: Yes, I agree completely that if replication is in the cards, the database definitely has to be designed for it. Often simply following best practices will get most of the way there; it's the ugly, hairy legacy databases that tend to have the most design problems. From my experience, merge replication in particular is more challenging than any of the other replication mechanisms. –  Jon Seigel May 31 '13 at 0:40
add comment

I agree. As long as there is no need for the PK value to be able to change, then it would be better to use the existing uniqueidentifier column as the rowguidcol.

share|improve this answer
    
Thanks, much appreciated. –  spaghettidba May 30 '13 at 17:39
add comment

"Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them)."

But it's not used for replication only. It's also (and already) your PK.

share|improve this answer
add comment

Perhaps this may help:

What is the purpose of rowguidcol property

Comments from Hugo Kornelis, SQL Server MVP and Erland Sommarskog, SQL Server MVP

Good luck.

share|improve this answer
3  
This is essentially a link-only answer. These are discouraged because links can stop working. I couldn't see much in the link that answers the question, but summarizing the points you feel are relevant would help your answer attract up votes. –  Paul White May 30 '13 at 22:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.