How to Create Case-Sensitive Joins with Data Stored in SQL Server (2024)

Summary

In SQL Server, joins are case-insensitive. Case-sensitive collations are not supported with ArcGIS.

Procedure

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.fieldCOLLATE SQL_Latin1_General_CP1_CS_AS = database.dataowner.dataset2.field COLLATE SQL_Latin1_General_CP1_CS_AS

The advantageto this workflow is that columns are not directly modified. However,COLLATE SQL_Latin1_General_CP1_CS_ASmust 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: Enable case-sensitive queries on an attribute column in ArcSDE for SQL Server. This workflow maynot work however, as the article is outdated.If the workflow outlined in the above articledoes 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 columnsmust 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).