COLLATE is use to search the Sensitive data from the table. I have explained collation with example.
--Example - 1:
DECLARE @A VARCHAR(10)
SELECT @A = 'a'
IF (@A = 'A')
PRINT 'Match'
ELSE
PRINT 'No Match'
SELECT @A = 'a'
IF (@A = 'A' COLLATE SQL_Latin1_General_CP1_CS_AS)
PRINT 'Match'
ELSE
PRINT 'No Match'
--Example - 2:
--CREATE TEMP TABLE
CREATE TABLE #Emp([PassWord] VARCHAR(50))
--INSERT VALUES
INSERT INTO #Emp VALUES('Password')
INSERT INTO #Emp VALUES('password')
INSERT INTO #Emp VALUES('PassWord')
INSERT INTO #Emp VALUES('PassworD')
--SELECT
SELECT * FROM #Emp WHERE [PassWord] ='password'
--USING COLLATION
SELECT * FROM #Emp WHERE [PassWord] COLLATE Latin1_General_CS_AS ='password'
--KNOW ABOUT COLLATION
SELECT * FROM fn_helpcollations()
ALTER TABLE #EMP
ALTER COLUMN [Password] VARCHAR(50)
COLLATE Latin1_General_CS_AS
Normally we have 4 type of sensitivity on SQL Server (Case, Width, Accent, kanatype)
