Exceptions to Using SQL Statement Replication

There are several limitations 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