Filtering data in a publication I'm looking to replicate a table that has a particular column for deciding if a row should be replicated. If the column is set to 'Y', then replicate. From my searching, Static Row filters are my friend here, but I have a question on them.

All of the rows will be initially inserted with the column set to 'N'. Then, a script is run that identifies which rows are supposed to be replicated and updates those rows to 'Y' (the logic behind which rows is complex and not easy to identify at insertion time). Additionally, a subsequent run of the the script may find the a row is no longer valid for replication and will update the 'Y' back to 'N'.

My question is this: If my action on the source table is an update action, won't that break on the subscriber side when the update transaction tries to run against a row that doesn't exist on the published table (since the row was not valid for replication until the update occurred?) Similarly, if I update the row to 'N', will that update trigger a delete on the published table (since the row would no longer meet the filter criteria)? Or is the SQL server replication agent smart enough to turn that update into a insert/delete respectively?
Assuming you are running transaction replication. This is how it works in your scenario


When initial records are inserted with replicated column value as 'N' on publisher no records will be replicated to subscriber.
Once you update the some of the records with replicated column value as 'Y' based on your logic on publisher only those 'Y' records will replicated to subscriber.
And for last question once you update any of the replicated column 'Y' value records on publisher back to 'N' in subsequent runs of your logic those records will be deleted from the subscriber.


All of these operations can be monitor by executing the EXEC sp_browsereplcmds on distribution database. In the execution results look for command column to see what exact is performed by replication process.