Sql Server check case-sensitivity?

sql

collation

sql-server

database-management

byAnton Shumikhin·Dec 17, 2024

Check SQL Server's case sensitivity by querying the 'IsCaseSensitive' collation property, like so:

SELECT 
CASE WHEN COLLATIONPROPERTY('Latin1_General_CS_AS', 'IsCaseSensitive') = 1 
THEN 'Case Sensitive' ELSE 'Case Insensitive'
END;

Substitute 'Latin1_General_CS_AS' with your server's specific collation for server-wide check. Use database_default to scope it to a certain database.

One server, many collations

Verifying collation at various levels is essential. Run these commands:

Server level:

SELECT SERVERPROPERTY('COLLATION');
-- Oh servant, tell me thy collation!

Database level:

SELECT DATABASEPROPERTYEX('databasename', 'Collation');
-- In the kingdom of database, what rule art thou adhere to?

Column level:

SELECT 
    COLUMN_NAME, 
    COLLATION_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME = 'tablename';
-- Dear column, reveal your inclination, are you sensitive or not?

In your SELECT queries, utilize COLLATE to enforce case sensitivity check, a fitting tool in preserving stored procedure consistencies.

Surgical collation changes

When you need to tweak an existing system's collation, tread lightly.

Isolate tests of changes to avoid an epidemic of issues.
Should ill winds blow, revert changes tactfully.
Utilize fn_helpcollations() to navigate through collation seas.

Variables and null values: casing and questioning

When NULL bumps against NULL in the dim light of a case-insensitive context, they treat each other as equals.
If two variables look and sound the same but live in different scopes, SQL Server won't allow it because of collation. Think of it as a strict no-doppelgänger policy.

COLLATE: The overseer of comparisons

In comparison expressions, use COLLATE to guide SQL Server towards the right case sensitivity assessment. It's your compass in these foggy seas of data.

The power of character schemas

Knowing your ship's character schema in COLLATE makes for more accurate navigation in your queries.

For the perfectionists: All about schematics and amendments

Tweaking column collation

Change collation at the column level by knocking politely with an ALTER TABLE:

ALTER TABLE tablename
ALTER COLUMN columnname VARCHAR(255)
COLLATE Latin1_General_CS_AS NOT NULL;
-- Pardon me, I believe this column needs a make-over!

Enforcing COLLATE in WHERE clauses

Here's how to add an air of sophistication to your queries:

SELECT * 
FROM tablename 
WHERE columnname COLLATE Latin1_General_CS_AS = 'SomeValue';
-- Show me the records who prefer to go by 'SomeValue', old sport!

Err on the side of caution: Reverting changes

Made a boo-boo with that collation change? No worries, just retrace your steps:

ALTER TABLE tablename
ALTER COLUMN columnname VARCHAR(255)
COLLATE Previous_Collation_Name NOT NULL;
-- Let's rewind and start over, all's well that ends well!