In the case that we had configured a case insensitive collation, we must be aware of many cases require a case sensitive query, for example a user/key value
There are four ways for achieving case sensitive queries despite of the configured collation
1. We can rebuild our databases specifying the new collation, so we get the change at server level
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName
For more information, see msdn documentation --> MSDN: Set or Change the Server Collation
2. We can set up the collation only for our database, so we get the change at database level
To retrieve the actual collation of our database, we can launch the next query
SELECT name, collation_name
FROM sys.databases
WHERE name = 'Business'
To get a list of the available collations, we launch the next query, this query will also returns a description of the conditions for the collation
SELECT * FROM ::fn_helpcollations()
The command to change the collation of our database is the next, where _CS_ indicates case sensitive
ALTER DATABASE Business COLLATE Modern_Spanish_CS_AS
3. In the same way as prior, we can change the collation at column level
ALTER TABLE B_CUSTOMERS
ALTER COLUMN CustomerKey varchar(20)COLLATE Modern_Spanish_CS_AS NOT NULL
4. And finally, if we don't have access to release these kind of changes, we can query the column casting the varchar datatype to varbinary, so we will compare the varchar values through its binary code
CREATE PROCEDURE LOGIN_CUSTOMER
(@nick VARCHAR(20), @customerkey VARCHAR(20), @res BIT OUTPUT)
AS BEGIN
IF EXISTS
(SELECT 1 FROM B_CUSTOMERS
WHERE Nick = @nick
AND
CAST(CustomerKey AS VARBINARY(20))
=
CAST(@customerkey AS VARBINARY(20)))
SET @res = 1
ELSE
SET @res = 0
RETURN @res
END
GO
With this option, we can be sure that the query will perform correctly
<METHOD SOFTWARE © 2012>
