The NLSCASE database property

Enterprise Replication supports both case-sensitive databases and NLSCASE INSENSITIVE databases. (Databases created with the NLSCASE INSENSITIVE option ignore letter case in operations on NCHAR and NVARCHAR strings, and on strings of other character data types that are cast explicitly or implicitly to NCHAR or NVARCHAR data types.)

The database server does not prevent a case-sensitive database from being replicated by a database that has the NLSCASE INSENSITIVE property, nor the replication of an NLSCASE INSENSITIVE database by a case-sensitive database. No warning or exception is issued by the database server in either of these cases when you define replication participants.

These two types of database behave differently, however, in operations that classify NCHAR and NVARCHAR strings as duplicates or as distinct values, if the character strings that are being compared differ only in letter case. It is the user's responsibility to make sure that replication participants with different NLSCASE attributes will not cause exceptions or unexpected behavior when replicating the results of operations like the following on NCHAR or NVARCHAR data:

• sorting and collation
• foreign key and primary key dependencies
• enforcing unique constraints
• clustered indexes
• access-method optimizer directives
• queries with WHERE predicates
• queries with UNIQUE or DISTINCT specifications in the projection clause
• queries with ORDER BY clauses
• queries with GROUP BY clauses
• cascading DELETE operations
• table or index storage fragmentation BY EXPRESSION
• table or index storage fragmentation BY LIST
• data distributions from UPDATE STATISTICS operations

To avoid the risk of consistency problems that can result from differences in case-sensitivity, the following policy might be useful when you define replication pairs:

• Replicate case-sensitive databases only with case-sensitive databases.
• Replicate NLSCASE INSENSITIVE databases only with NLSCASE INSENSITIVE databases.