Dev : MSSQL Server Case Sensitivity

Using database such as Oracle will insist that you use the correct case to do a select filter. But, if you use MSSQL 2000/2005, by default it is case-insensitive. You may need to choose it while in installation if I am not mistaken.

Thus, it means that:
select * from user where id = 'admin'
will always produce the same result as
select * from user where id = 'ADMIN'
if it is case_insensitive.

In this case, how would we make sure that our select statement always base on case sensitivity? Esspecially when comparing fields such as password where the case sensitivity is crucial?

Simple, just add 'COLLATE SQL_Latin1_General_CP1_CS_AS' at the end of the select statement to solve the matter.

Thus, by adding these words, it will always make sure that:
select * from user where id = 'admin' COLLATE SQL_Latin1_General_CP1_CS_AS
will always produce different result as
select * from user where id = 'ADMIN' COLLATE SQL_Latin1_General_CP1_CS_AS

If you want to ignore case sensitivity when by default is case-sensitive, then the SQL should be
select * from user where id = 'admin' COLLATE SQL_Latin1_General_CP1_CI_AS