A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters.
Here are some ways to perform case sensitive searches using different methods.
Method 1: Converting data to binary type before comparison
SELECT 1
FROM dbo.Customers
WHERE CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))
AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))
may change how indexes are used, so you might need to include the non-collated columns to promote usage of indexes.
SELECT 1
FROM dbo.Customers
WHERE CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))
AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))
AND CustID = @CustID
AND CustPassword = @CustPassword
Method 2: Using the COLLATE clause to dictate the case sensitiveness of the query
adds logic to the query.
SELECT 1
FROM dbo.Customers
WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS
may change how indexes are used, so you might need to include the non-collated columns to promote usage of indexes.
SELECT 1
FROM dbo.Customers
WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustID = @CustID
AND CustPassword = @CustPassword
Method 3: Using BINARY_CHECKSUM function
not perfect since checksums are limited in comparison.
SELECT 1
FROM dbo.Customers
WHERE BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)
AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)
SELECT 1
FROM dbo.Customers
WHERE BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)
AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)
AND CustID = @CustID
AND CustPassword = @CustPassword
Method 4: Changing the collation of the column permanently, so that all comparisons are case sensitive by default
essentially, change the table column to have the desired collation.
ALTER TABLE dbo.Customers ALTER COLUMN CustPassword varchar(15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
GO
if the column is the primary key, then you need to drop the primary key constraint first, and re-add it afterwards.
Method 5: Using computed columns
create a computed column with the desired collation. using an index will force sql server to store the computed column data on disk.
ALTER TABLE dbo.Customers ADD CustID_CS AS (CAST(CustID AS varbinary(8))),
CustPassword_CS AS (CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS)
GO
CREATE NONCLUSTERED INDEX NC_NI_Customers_CustID ON dbo.Customers(CustID_CS)
GO
SELECT 1
FROM dbo.Customers
WHERE CustID_CS = CAST(@CustID AS varbinary(8))
AND CustPassword_CS = @CustPassword
Method 6: Make use of client side languages to extend the comparison (adds a step)
check the values locally after getting the result set. it's just an option, for example you're not a db admin, or for whatever reason can't make changes.
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm