SQL Server Like Query not case sensitive

Question

Query

SELECT  * 
from Table_2  
WHERE  name like ('Joe');

Output

1   100 Joe
2   200 JOE
3   300 jOE
4   400 joe

Why is it not case sensitive?

Answer 1

Problem:

Query not case sensitive

Cause: Column 'Name' has a case-insensitive (CI) collation.

Solution: You have to use a CS collation: SELECT * FROM fn_helpcollations() WHERE description LIKE N'%case-sensitive%'.

Note: There is a database collation and column level collation. And, there is, also, a server level collation.

SELECT  DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation
/*
-- Sample output (my database)
DatabaseCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

SELECT  col.collation_name AS ColumnCollation
FROM    sys.columns col
WHERE   col.object_id = OBJECT_ID(N'dbo.Table_2') 
AND     col.name = N'Name'
/*
-- Sample output (my database)
ColumnCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

Simply changing database collation will NOT change the collation for existing user tables and columns:

This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

Source

After changing database collation, the output of above queries will be:

/*
DatabaseCollation -- changed
----------------------------
SQL_Latin1_General_CP1_CS_AS
*/

/*
ColumnCollation -- no change
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

and, as you can see the collation of column Name remains CI.

More, changing database collation will affect only the new created tables and columns. Thus, changing database collation could generate strange results (in my opinion) because some [N][VAR]CHAR columns will be CI and the new columns will be CS.

Detailed solution #1: if just some queries for column Name need to be CS then I will rewrite WHERE clause of these queries thus:

SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe' AND Name LIKE 'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS

This will give a change to SQL Server to do an Index Seek on column Name (in there is an index on column Name). Also, the execution plan will include an implicit conversion (see Predicate property for Index Seek) because of following predicate Name = N'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS.

Detailed solution #2: if all queries for column Name need to be CS then I will change the collation only for column Name thus:

-- Drop all objects that depends on this column (ex. indexes, constraints, defaults)
DROP INDEX IX_Table_2_Name ON dbo.Table_2

-- Change column's collation
ALTER TABLE dbo.Table_2
ALTER COLUMN Name VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
-- Replace VARCHAR(50) with proper data type and max. length
-- Replace COLLATE SQL_Latin1_General_CP1_CS_AS with the right CS collation

-- Recreate all objects that depends on column Name (ex. indexes, constraints, defaults)
CREATE INDEX IX_Table_2_Name ON dbo.Table_2 (Name)

-- Test query
SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe'

Answer 2

If you want your query to be case sensitive on few occasions only, then you can try below query:

SELECT *
FROM TableName
where Col1 = 'abcdEfhG' COLLATE SQL_Latin1_General_CP1_CS_AS

Just add "COLLATE SQL_Latin1_General_CP1_CS_AS" in front of the query.

Answer 3

That is your DB is configured to be case insensitive.

In order to change this:

• Open SSMSE.
• Right Click on your DB and select Properties
• Select Options in the left pane.
• Change the value under Collation to the same value you are currently using, just with CS (Case sensitive) instead of CI (Case Insensitive)

For example, If you are using:

• SQL_Latin1_General_CP1_CI_AS

Change it to:

• SQL_Latin1_General_CP1_CS_AS

If you want to understand a bit more about collations, take a look at the accepted answer under this thread.

Update:
Please Note that as @BogdanSahlean has noted this solution will work for newly created objects but not for existing tables and columns.
From MSDN:

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.