HOW TO

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

Last Published: November 17, 2020

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.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: Enable case-sensitive queries on an attribute column in ArcSDE for SQL Server. 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).

Article ID:000001035

Software:
  • ArcMap

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic