Exceptions to using SQL statement replication

A DML statement matching one or more conditions as listed below cannot use SQL statement replication. 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 does 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 standby 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-in functions:

  If the built-in function statement 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 is not replicated using SQL statement replication:

  update tbl set value = convert(int, column5)
  

  In warm standby topologies, queries containing the following built-in functions can be replicated using SQL statement replication even if the built-in function 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