How to create case-sensitive joins with data stored in SQL Server


In SQL Server, joins are case-insensitive.


Workflow #1

When creating a join based on strings that are case-sensitive, use Make Query Table with COLLATE. For example, the Expression parameter for Make Query Table should look something like the following, where dataset1.field is joined to dataset2.field:
database.dataowner.dataset1.field COLLATE SQL_Latin1_General_CP1_CS_AS = database.dataowner.dataset2.field COLLATE SQL_Latin1_General_CP1_CS_AS
The advantage to this workflow is that columns are not directly modified. However, COLLATE SQL_Latin1_General_CP1_CS_AS must be called each time the tool is executed to perform a join.

Workflow #2

There is an older knowledge base article that suggests to directly modify the column to use case-sensitive collation: HowTo:  In ArcSDE for SQL Server, enable case-sensitive queries on an attribute column This workflow may not work however, as the article is outdated. If the workflow outlined in the above article does not work, the ALTER TABLE command may be used instead using the following steps. The advantage to this workflow is that joins may be created as they normally would, without calling COLLATE and without using Make Query Table. The disadvantage is that the columns must be directly modified.  
  1. Open SQL Server Management Studio.
  2. Highlight the database.
  3. Select New Query.
  4. Execute* the following command:
    alter table [dataowner].[dataset1] alter column [field] nvarchar(50)COLLATE SQL_Latin1_General_CP1_CS_AS;
    * Do this for the fields in both tables. Note that the datatype may need to be altered, depending the length of the text field, for example, nvarchar(50).

Related Information

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