In SQL Server, the collation property controls case sensitivity. Case sensitivity affects sorting and queries (even the column names must match exactly if you’re using a case-sensitive collation at the database-level). The default collation when you create a database in SQL Server is SQL_Latin1_General_CP1_CI_AS. The CI stands for case-insensitive, which means SQL Server databases are case-insensitive by default.
You can override the collation at the query-level, allowing you specify the case sensitivity like this:
SELECT * FROM People
WHERE [Name] = 'Keanu Reeves'
COLLATE SQL_Latin1_General_CP1_CI_AS
Code language: SQL (Structured Query Language) (sql)If the database were using a case-sensitive collation, and you had ‘keanu reeves’ in the database, then the above query would actually return the record, because COLLATE SQL_Latin1_General_CP1_CI_AS makes the query do a case-insensitive search.
In the rest of this article, I’ll explain how to check the database’s collation settings and how a case-sensitive collation can result in queries giving errors if the column names don’t match.
Check the database’s collation settings
Collation is defined at the database-level and can be overridden at the column-level. I haven’t encountered anyone overriding collation at the column-level, but it’s possible. When you don’t specify the collation for an individual column, it will use the database-level collation.
To check the database’s collation setting, execute the following query, passing in the database name:
SELECT DATABASEPROPERTYEX('MakolyteCS','collation');
Code language: SQL (Structured Query Language) (sql)This returns the following collation name:
SQL_Latin1_General_CP1_CS_ASCode language: plaintext (plaintext)If you want more details about the collation, you can check its description by executing the following query:
SELECT description FROM sys.fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'
Code language: SQL (Structured Query Language) (sql)This returns the following description:
Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode DataCode language: plaintext (plaintext)As you can see, the “CS” in the name stands for “case-sensitive”. When in doubt, check the collation description.
Case-sensitive column names
Let’s say you’re using a case-sensitive collation at the database-level and you have the following table:
CREATE TABLE [dbo].[People](
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)When using a case-sensitive collation, the column names in your queries must match the casing in the table definition. So let’s say you execute the following query with the incorrectly-cased Name column:
SELECT [name]
FROM [MakolyteCS].[dbo].[People]
Code language: SQL (Structured Query Language) (sql)This will result in the following error:
Invalid column name 'name'.Code language: plaintext (plaintext)If you don’t know you’re using a case-sensitive collation, then this is a surprising error. This situation can arise if you are deploying your software into an environment where you aren’t in control of the database. Your query with ‘name’ would work in any environment that uses a case-insensitive collation (which is the default), but would fail in any environment that uses a case-sensitive collation.
The simplest solution here is to make sure your queries specify column names with the exact casing they were defined with. That way your query will work in both case-sensitive and case-insensitive environments.
