SQL Server Collations: Case Sensitivity and Insensitivity

Introduction

In SQL Server, collations are a set of rules that determine how data is sorted and compared. Collations can be case-sensitive (CS) or case-insensitive (CI). This article explains how collations work in SQL Server through a series of SQL queries that demonstrate the behavior of case sensitivity and insensitivity in data comparison.

Latin1_General_CS_AS is a SQL Server collation that enforces case-sensitive, accent-sensitive sorting and comparison, while Latin1_General_CI_AS enforces case-insensitive, accent-sensitive sorting and comparison.

Example Case-Insensitive

1. Create a temporary table #TBL_CaseInsensitive with a column Col_Value that uses a case-insensitive collation (Latin1_General_CI_AS).
2. Insert a single row with the value 'naveenkumar'.
3. Select the value from the table where Col_Value is equal to 'NAVEENKUMAR'. Since the column uses a case-insensitive collation, the query should return the row.
4. Perform the same selection but specify a case-sensitive collation (Latin1_General_CS_AS) for the comparison. This query will not return any rows because 'NAVEENKUMAR' does not match 'naveenkumar' when considering case sensitivity.

   DROP TABLE IF EXISTS #TBL_CaseInsensitive
   CREATE TABLE #TBL_CaseInsensitive ( Col_Value CHAR(20) COLLATE Latin1_General_CI_AS)
   INSERT INTO #TBL_CaseInsensitive VALUES('naveenkumar')
   SELECT * FROM #TBL_CaseInsensitive WHERE Col_Value = 'NAVEENKUMAR' 
   SELECT * FROM #TBL_CaseInsensitive WHERE Col_Value = 'NAVEENKUMAR' COLLATE Latin1_General_CS_AS
   

Output

Example Case-Sensitive

1. Create the temporary table #TBL_CaseSensitive again, but this time with a column Col_Value that uses a case-sensitive collation (Latin1_General_CS_AS).
2. Insert the same value 'naveenkumar' into the new table.
3. Select the value from the table where Col_Value is equal to 'NAVEENKUMAR'. Since the column uses a case-sensitive collation, the query will not return any rows because 'NAVEENKUMAR' does not match 'naveenkumar'.
4. Perform the same selection but specify a case-insensitive collation (Latin1_General_CI_AS) for the comparison. This query will return the row because the comparison is case-insensitive.

   DROP TABLE IF EXISTS #TBL_CaseSensitive
   CREATE TABLE #TBL_CaseSensitive (Col_Value CHAR(20) COLLATE Latin1_General_CS_AS)
   INSERT INTO #TBL_CaseSensitive VALUES('naveenkumar')
   SELECT Col_Value FROM #TBL_CaseSensitive WHERE Col_Value = 'NAVEENKUMAR' 
   SELECT Col_Value FROM #TBL_CaseSensitive WHERE Col_Value = 'NAVEENKUMAR' COLLATE Latin1_General_CI_AS

Output

Conclusion

The queries above illustrate how SQL Server handles case sensitivity and insensitivity using collations. By understanding and specifying the appropriate collation, you can control whether your data comparisons are case-sensitive or case-insensitive, which is crucial for ensuring the correctness and performance of your SQL queries.