Exceptions to using SQL statement replication

SQL statement replication is not supported when:

• A replicate database has a different table schema than the primary database.

• Replication Server must perform data or schema transformation.

• Subscriptions or articles include where clauses.

• Updates include one or more text or image columns.

• Function strings rs_delete, rs_insert, and rs_update are customized.

• A DML statement matches one or more conditions listed here. In these cases, traditional replication is used:

  • The statement refers to views, temporary tables, or tables in other databases.

    insert tbl select * from #tmp_info 
    where column = 'remove'
    

  • The user executes the statement with set rowcount option set to a value greater than zero.

    set rowcount 1
    update customers
    set information = 'reviewed'
    where information = 'pending'
    

  • The statement uses the top n clause in select or select into statements, a Java function, or a SQL User-Defined Function(UDF):

    delete top 5
    from customers 
    where information = 'obsolete'
    

  • The base table includes encrypted columns, and the statement references one of those columns in a set or where clause.

  • The statement references system catalogs or fake tables such as ‘deleted’ or ‘inserted’. In this example, the delete executed by the trigger will not use SQL statement replication because it is using the fake table deleted:

    create trigger customers_trg on customers for delete as
    delete customers_hist
    from customers_hist, deleted
    where deleted.custID = customers_hist.custID
    go
    delete customers where  state = 'MA'
    go
    

  • The statement is an insert statement that generates a new identity or timestamp value.

  • The statement is an update statement that changes a timestamp or identity value.

  • The statement is an update statement that assigns a value to a local variable. For example:

    update t set @a = @a + 2, c = @a where c > 1
    

  • The statement makes references to materialized computed columns.

  • The statement is a select into statement that affects a replicate table with encrypted columns.

  • The statement is an insert select or select into using a union clause:

    select c1, c2 from tbl2
    union
    select cc1, cc2 from tbl3
    

  • The statement is an update, insert select, or select into on a table with text/image columns.

  • The statement is a query that uses built-ins:

    If the built-in can be resolved to a constant value, the query is replicated as a SQL statement. For example:

    update tbl set value = convert(int, "15")
    

    However, the following query will not be replicated using SQL statement replication:

    update tbl set value = convert(int, column5)
    

    In warm standby topologies, queries containing the following built-ins can be replicated using SQL statement replication even if the built-in cannot be resolved to a constant value:

    abs	cot	ltrim	sqrt
    acos	datalength	patindex	str
    ascii	degrees	power	strtobin
    asin	exp	replicate	stuff
    atan	floor	reverse	substring
    atn2	hextoint	right	tan
    bintostr	inttohex	round	to_unichar
    ceiling	len	rtrim	upper
    char	log	sign
    convert	log10	soundex
    cos	lower	space