NCHAR and NVARCHAR expressions in case-insensitive databases

In databases created with the NLSCASE INSENSITIVE property, the database server makes no distinction between uppercase and lowercase variants of the same letter in NCHAR and NVARCHAR expressions, regardless of whether a localized collation order is defined for the locale.

This disregard for letter case can change the values that case-insensitive operations on NCHAR or NVARCHAR expressions return, compared to the same operations on the same expressions in a case-sensitive database, if letter case variants are the only differences among the operands of relational operators, or among the arguments to string functions.

Suppose, for example, that for a record in a table of a database in the default locale, the NCHAR column lname stores the value McDavid.

In a case-sensitive database, the Boolean expression lname > "MCDAVID" evaluates as true, because the database server uses the codeset order of the default locale to compare the two operands. Although both strings begin with uppercase M, the next character in the column value is lowercase c, the ASCII 99 code point, but the next character in the quoted string is uppercase C, the ASCII 67 code point. Because 99 is greater than 67, the column value is greater than the quoted string in a case-sensitive database.

In a case-insensitive database, however, the same expression lname > "MCDAVID" evaluates as false, because the database server ignores letter case variants when it compares the two operands. Both strings have the same letters in the same sequence, so by these criteria, the column value is identical to the quoted string.

Because a database that has the NLSCASE INSENSITIVE property disregards letter case in comparisons that include an NCHAR or NVARCHAR operand, operations on NCHAR or NVARCHAR character strings in case-insensitive databases can produce results that differ from those of a case-sensitive database. Contexts in which a case-sensitive database and a case-insensitive database might use the same SQL operations to return different results from the same data set include these:
  • 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 in the projection clauses
  • queries with ORDER BY clauses
  • queries with GROUP BY clauses
  • cascading DELETE operations
  • table or index storage distribution BY EXPRESSION
  • table or index storage distribution BY LIST
  • data distributions from UPDATE STATISTICS operations.