Sql Server - Case Sensitive Searches
By default, Sql Server is not case sensitive; for exmple if you search for 'dog' you will get 'dog', 'DOG', and 'Dog'.
This is great because on the most part that is what you want.
The case-sensitiveness of Sql Server is controlled by the Collation setting which is more than just a case sensitive setting, but let's keep it simple for now.
The default case-sensitive collation seems to be SQL_Latin1_General_CP1_CI_AS. The CI means "case insensitive".
The Collation is set for the server when the server instance is installed and in recent versions can be set for the database and for a specific column.
If you want to query a column in a case sensitive way, as in validating a password, the best thing to do, in my opinion, is to set the collation for that singular column to be case sensitive.
You can do that like this:
ALTER TABLE
TableName
ALTER COLUMN
ColumnName DataType COLLATE SQL_Latin1_General_CP1_CS_AS
Note thate the CS in the above stands for "case sensitive".
Of course, you can also change this setting in design view of a table inside the Management Studio.
There you will find Collation in the Column Properties.
Alternately, you can leave the column as case insensitive and then do something like this example in your Sql:
WHERE Password
COLLATE SQL_Latin1_General_CP1_CS_AS = 'someCasesEnsItivePaSsworD'