Comparing Case Sensitive Data in SQL server

Comparing Case Sensitive  Data in SQL server 

How to compare case sensitive data in sql server? Lets take one example if you have a password column where you are storing password, then while retrieving result should come as per case compression. If data is "AmiT#123" then it should not equal to "amit#123" because here "A" has different case.

 To compare case sensitive data we can use the COLLATE.

CREATE TABLE bn(name VARCHAR(100))

GO

 INSERT INTO bn (name ) VALUES ('Vikas'),('Choice'),('kumar')

 SELECT * FROM bn WHERE name collate latin1_general_CS_AS ='Vikas'

--