Replicate Calculated Primary Key

Question

Replicate Calculated Primary Key

Ronzo

Hall of Fame

Points: 3917
More actions
June 26, 2017 at 1:59 pm

#324243

I have a table with a calculated primary key, and I want to replicate that value (not formula) to another database. I can successfully create replication, but the behavior is "whacked".
The table create scripts are below, first the publisher, then the subscriber. The only two published columns are col1 and col2 (this is just an example).
I setup transactional publication with no snapshot normally using GUI, except to specify only publishing col1 and col3.
I setup the subscription with no initialization.
Inserts fail, Updates to col1 replicate, but do not change the subscriber col3 value (so only work once), Deletes work.
Is this a SQL Server bug, or is this expected behavior? Does anyone know how I can get this to work "right"?
CREATE TABLE dbo.CalculatedReplication
(col1 int NULL
,col2 int NOT NULL IDENTITY (1,1)
,col3 AS COALESCE (col1, col2) PERSISTED NOT NULL
,CONSTRAINT PK_CalculatedReplication
    PRIMARY KEY NONCLUSTERED
    (col3)
)
;
CREATE
TABLE dbo.CalculatedReplication
(col1 int NULL
,col3 int NOT NULL
,CONSTRAINT PK_CalculatedReplication
    PRIMARY KEY NONCLUSTERED
    (col3)
)
;
Have Fun!
Ronzo

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Ronzo - Monday, June 26, 2017 1:59 PM
I have a table with a calculated primary key, and I want to replicate that value (not formula) to another database. I can successfully create replication, but the behavior is "whacked".
The table create scripts are below, first the publisher, then the subscriber. The only two published columns are col1 and col2 (this is just an example).
I setup transactional publication with no snapshot normally using GUI, except to specify only publishing col1 and col3.
I setup the subscription with no initialization.
Inserts fail, Updates to col1 replicate, but do not change the subscriber col3 value (so only work once), Deletes work.
Is this a SQL Server bug, or is this expected behavior? Does anyone know how I can get this to work "right"?
CREATE TABLE dbo.CalculatedReplication
(col1 int NULL
,col2 int NOT NULL IDENTITY (1,1)
,col3 AS COALESCE (col1, col2) PERSISTED NOT NULL
,CONSTRAINT PK_CalculatedReplication
    PRIMARY KEY NONCLUSTERED
    (col3)
)
;
CREATE
TABLE dbo.CalculatedReplication
(col1 int NULL
,col3 int NOT NULL
,CONSTRAINT PK_CalculatedReplication
    PRIMARY KEY NONCLUSTERED
    (col3)
)
;

Ummm.... give me one good reason to design a table this way...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

col1 int NULL
,col2 int NOT NULL IDENTITY (1,1)
,col3 AS COALESCE (col1, col2) PERSISTED NOT NULL

Take a good look at your design. What prevents two rows of col1 having the value 200 , for example?
What about when the value is not 200, say NULL , and Col2 gets 200 automatically from the identity insert? I am just now following the design logic here 🙂

----------------------------------------------------

Ronzo Hall of Fame Points: 3917 More actions · Answer 3

Replicate to data warehouse, need to be backward compatible with older customers using prior versions of software. So COALESCE old key if it exists, otherwise use IDENTITY (actual case uses negative identity values to avoid collisions with legacy positive values).
My current planned workaround (PAINFUL) is to use custom replication procedures just for this one article/table.

Have Fun!
Ronzo

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Ronzo - Monday, June 26, 2017 2:49 PM
Replicate to data warehouse, need to be backward compatible with older customers using prior versions of software. So COALESCE old key if it exists, otherwise use IDENTITY (actual case uses negative identity values to avoid collisions with legacy positive values).
My current planned workaround (PAINFUL) is to use custom replication procedures just for this one article/table.

This just sounds like you're letting compatibility with old software dictate a really bad idea as a "solution". As we have no other background on what purpose within the application this particular table serves, there's not really anywhere near enough information to make any useful judgment calls on what kind of alternative might make sense. Sorta resembles the proverbial chicken and the egg thing about which came first.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)