Case sensitivity in SQL Server

By default, when you create a new database in SQL Server, the collation is set to SQL_Latin1_General_CP1_CI_AS. The "CI" here stands for case-insensitive, meaning that SQL Server databases are case-insensitive by default.

However, you can specify a different collation at the query level, allowing you to adjust case sensitivity when needed.

For example:

SELECT * FROM Employees  
WHERE [FirstName] = 'john doe'  
COLLATE SQL_Latin1_General_CP1_CI_AS

If the database is using a case-sensitive collation, and you have John Doe stored in the database (with different case), the query above will still return the record because the COLLATE SQL_Latin1_General_CP1_CI_AS part ensures a case-insensitive search.

This article will cover how to check a database’s collation settings and how using a case-sensitive collation can sometimes lead to errors in queries when column names don't match the case exactly.

Check the Database’s Collation Settings

Collation is typically set at the database level but can be overridden at the column level. It's uncommon to change the collation at the column level, but it is possible. If no column-level collation is specified, it will default to the database-level collation.

To check the collation of your database, you can run the following query, passing in the database name:

SELECT DATABASEPROPERTYEX('EmployeeDB', 'collation');

This would return a collation name like:

SQL_Latin1_General_CP1_CS_AS

If you’d like to get more information about the collation, you can execute a query like this to fetch its description:

SELECT description FROM sys.fn_helpcollations()  
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'

The output will look like this:

Latin1-General, case-sensitive, accent-sensitive, kana-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

Notice that the "CS" in the collation name stands for case-sensitive. If you're ever unsure, you can always check the collation description for confirmation.

Case-Sensitive Column Names

Suppose you're working with a database that uses a case-sensitive collation. In that case, you’ll need to ensure that the casing of your column names in queries matches exactly with what’s defined in the table schema.

For example:

CREATE TABLE [dbo].[Employees](
    ```

With a case-sensitive collation, the query must match the casing of the column name:

```sql
SELECT [firstname]  
FROM [EmployeeDB].[dbo].[Employees]

This will throw an error like:

Invalid column name 'firstname'.

If you're not aware that your database uses a case-sensitive collation, this can be a frustrating issue. A query with firstname would work in a case-insensitive environment (which is the default), but it will fail in a case-sensitive environment where column names are case-sensitive.

To avoid this issue, always ensure that your queries use the exact casing for column names as they appear in the database definition. This way, your queries will run smoothly in both case-sensitive and case-insensitive environments.