In ArcSDE for SQL Server, enable case-sensitive queries on an attribute column

Summary

Beginning with ArcSDE 9.2, case-sensitive collation is not supported at SQL Server instance and database level, which means the database collation must be case insensitive. But this does not mean case-sensitive queries cannot be performed against attribute data stored in ArcSDE databases.

This article is written to assist users who need to enable case-sensitive queries on individual character data columns.

Procedure

ArcSDE geodatabases can support case-sensitive column collations on user-defined datasets. This means the character data can be stored using a case-sensitive collation and that all queries against that data will use case-sensitive string comparisons.

1. Open SQL Server Management Studio or Enterprise Manager, navigate to the feature class or table's business table that is to be modified, right-click it, and click Design.
2. In the table design window, highlight the column to be queried in ArcGIS. On the Column Properties panel click Table Designer > Collation field, type in or click the ellipsis (...) to select a supported case-sensitive collation (e.g., SQL_Latin1_General_CP1_CS_AS). Close the table design window and save the changes.

This alters the collation of the modified column. If the table with the altered column is subsequently registered as versioned, the corresponding column on the Adds table will have the same collation. If the column to be modified is on a table that has already been registered as versioned, manually change the collation on both the business table and the related Adds table.

For the detailed description, refer to the technical paper listed in the Related Information section below.