dutyDBA.com

This is one of the most frequently asked questions on SQL Server forums – “How to perform a case senstive search on my SQL Server database?” and its often asked by people moving to SQL Server from other database platforns like Sybase, Oracle, PostgreSQL etc.

The case sensitiveness of SQL Server and its databases is controlled by the Collation property (formerly known as ‘sort order’). When you install your SQL Server, you choose a collation at the server level. Any databases or tables created susequently will inherit this server level collation. SQL Server collation is case insensitive by default and hence many developers and DBAs ask, how to perform a case sensitive search for columns that store things like passwords.

Changing the collation of an SQL Server instance is not trvial – it requires master database rebuild

The good news is, collation can be defined at server level as well as databaase and individual column level. If you have a case insensitive installation of SQL Server, you can still have case sensitiveness at specific database or column level. You can even perform case sensitive searches at run-time using the COLLATE statement, without actually changing the collation of a database or column.

You can run your SQL Server with a case sensitive collation like Latin1_General_BIN (binary collation) or Latin1_General_CS_AS (case sensitive collation) to get case sensitive searching to work, but running these collations at server or database level will force everything to be case sensitive, which is not always desirable, when you just want a password column to be case sensitive.

I am going to discuss two of my preferred methods for case sensitive searching im this article:

Here are some examples to show how you can perform case sensiteve searches. Let’s create a table named dbo.Logins with columns UserName and UserPassword:

CREATE TABLE dbo.Logins
(
	UserName nvarchar(32) PRIMARY KEY CLUSTERED,
	UserPassword nvarchar(16)
)
GO

--The password has mixed case with M and P being UPPER case 
--and the rest in lower case
INSERT INTO dbo.Logins (UserName, UserPassword) 
VALUES ('User1', 'MyPassword123455')
GO

CREATE TABLE dbo.Logins
(
	UserName nvarchar(32) PRIMARY KEY CLUSTERED,
	UserPassword nvarchar(16)
)
GO

--The password has mixed case with M and P being UPPER case 
--and the rest in lower case
INSERT INTO dbo.Logins (UserName, UserPassword) 
VALUES ('User1', 'MyPassword123455')
GO

Let’s perform a search for the user name and password – notice the password is in all lower case in the WHERE clause, although the actual stored password is of mixed case.

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	--Password in all lower case
	AND UserPassword = 'mypassword123455'
GO

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	--Password in all lower case
	AND UserPassword = 'mypassword123455'
GO

On a case insensitive server, the above query finds the row, even though the password is in all lowerr case – this is not the desirable behaviour, especially when case sensitivity needs to be enforced, for example, on password columns.

Let’s alter our query by adding the COLLATE statement to specify a case sensitive collation and force SQL Server to perfom a case sensitive search:

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND UserPassword COLLATE Latin1_General_BIN = 'mypassword123455'
GO

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND UserPassword COLLATE Latin1_General_BIN = 'mypassword123455'
GO

The above query enforces case sensitivity using the Latin1_General_BIN collation (binary collation) – as a result, it won’t find the row, as the password is specified in all lower case, but the actual password is of mixed case.

Similarly, the below query also enforces case sensitivity, but using a different case sensitive collaiton (Latin1_General_CS_AS). Result is the same as above, the row can’t be found, which is what we wanted:

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND UserPassword COLLATE Latin1_General_CS_AS = 'mypassword123455'
GO

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND UserPassword COLLATE Latin1_General_CS_AS = 'mypassword123455'
GO

Let’s go one step further and change the collation of the password column to enforce case sensitivity permanently:

--Change the collation of the password column permanently
ALTER TABLE dbo.Logins 
ALTER COLUMN UserPassword nvarchar(16) COLLATE Latin1_General_CS_AS
GO

--Change the collation of the password column permanently
ALTER TABLE dbo.Logins 
ALTER COLUMN UserPassword nvarchar(16) COLLATE Latin1_General_CS_AS
GO

The above will make sure, all comparisons against the UserPassword column enforce case sensitivity.

A column collation change will fail if the column has any indexes/constraints defined on it. Drop those indexes/constraints first, alter the collation and recreate the indexes/constraints on the column.

Yet another variation on the above solution is to retain the UserPassword column as is, without any changes, but add a case sensitive computed column that is derived from the UserPassword column, as shown below:

ALTER TABLE dbo.Logins 
ADD UserPassword_CS AS (UserPassword COLLATE Latin1_General_CS_AS)
GO

ALTER TABLE dbo.Logins 
ADD UserPassword_CS AS (UserPassword COLLATE Latin1_General_CS_AS)
GO

With the new computed column UserPassword_CS in place, you now have the flexibility of running your searches as case insensitive (by querying the original UserPassword column) or case sensitive (by querying the UserPassword_CS column).

Remember, you can create indexes on top of computed columns. If relevant for your requirement, index your case sensitive computed column to speed up your case sensitive searches.

Tip 1:

By default when you use a COLLATE statement in your WHERE clause, even if you have an index on that column, SQL Server optimiser won’t be able to create a plan to seek the index – your query will end up with an index scan instead of index seek. By actually changing the column collation to case sensitive, or by adding a case sensitive computed column with an index, you can make your query plan perform an index seek. Index seek would result in faster execution time compared to the slow index scan.

Tip 2:

You can make use of the COLLATE statement in your WHERE clause to avoid SQL Server error 468 (cannot resolve collation conflict). This point is not directly related to case sensitive search topic, but a general tip to avoid collation conflicts where user databases and tempdb use different collations.

Please note, the reverse situation (performig a case insensitive search on a case sensitive server) can also make use of the above techniques.

How to use the SQL Server 2025 Regular Expressions to perform case sensitive searches?

SQL Server 2025 has introduced new regular expressions for performing complex searches. One of the regular expression functions is REGEXP_LIKE, which can take the parameter ‘c’ for case sensitive searches.

The below example shows how to perform a case sensitive search using the new SQL Server 2025 REGEXP_LIKE function:

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND REGEXP_LIKE (UserPassword, 'MyPassword123455', 'c')
GO

SELECT 
	UserName, 
	UserPassword
FROM dbo.Logins 
WHERE 
	UserName = 'User1' 
	AND REGEXP_LIKE (UserPassword, 'MyPassword123455', 'c')
GO

If you replace the parameter ‘c’ with ‘i’, the REGEXP_LIKE function will perform a case insensitive search.

Hope you found the above infirmation useful – as always, feel free to share your thoughts by leaving a comment below.!