Case sensitive search on a case insensitive sql server

Recently I had a problem where i need to search for;

SQL Server default behavior is to do case insensitive search. so if run a query like this;

IF OBJECT_ID('tempdb..#FooTable') IS NOT NULL DROP TABLE #FooTable
SELECT x.* 
INTO #FooTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS UserName
	UNION ALL
	SELECT 2 As Id, 'foo' AS UserName
) x

I will get two records back.

To do a case sensitive search, do this;

SELECT UserName
FROM #FooTable
WHERE 1=1
AND UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

Result would be;

For join operations, let’s create another table;

--Now add a second table, say salary
IF OBJECT_ID('tempdb..#FooSalaryTable') IS NOT NULL DROP TABLE #FooSalaryTable
SELECT x.* 
INTO #FooSalaryTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS FooId, 4000 AS UserSalary
	UNION ALL
	SELECT 2 As Id, 'foo' As FoodId, 6000 AS UserSalary
) x

SELECT * FROM #FooSalaryTable

This is join query;

--example with join
SELECT 
	x.UserName, y.UserSalary
FROM #FooTable x
JOIN #FooSalaryTable y on x.UserName = y.FooId COLLATE SQL_Latin1_General_CP1_CS_AS
WHERE 1=1
AND x.UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

and results;