sjimmo (9/24/2010)
jeff.mason
It happens under the hood, and always has AFAIK. If there isn't a uniqueidentifier replication will add one to every table on the system and if replication is deleted, the column will be dropped as part of removing replication.
Robert Frasca
Actually, that's a "uniqueifier" not a uniqueidentifier. A uniqueifier is a 4-byte column added to make a primary key unique
Robert, I find your comment interesting, but am unable to prove it.
I just tried to create a publication (SS2005) on a table w/o a PK and recieved the error "This table cannot be published because it does not have a primary key column. Primary key columns are required for all tables in transactional publications."
I took a test table, created a nonunique PK on it, and it allowed me to create a publication.
Maybe, what you described is actually happening behind the scenes, I cannot prove that but none of my tools shows anything hidden nor do I see anything in any of the system tables.
LOL. I think we're having two different discussions. The uniqueifier is created when a non-unique clustered index is created. This makes the clustered index unique. Did you actually define that clustered index as the primary key, i.e. with the primary key constraint? In other words, is this a chicken or egg scenario, i.e. since, by default, the primary key is a clustered index did it do all that automatically or did you have to create the clustered index on a non-unique set of values and then designate it as the primary key after the fact?
The section about "uniqueifiers" is in the "Clustered Index Design Guidelines" section of BOL. It isn't visible or accessible to users by the way.
I was thinking that replication would automatically provide a primary key if one didn't exist but I guess I'm mistaken. Since I always create a primary key on every table it hasn't ever really come up.
"Beliefs" get in the way of learning.