Microsoft SQL Server by default is case-insensitive which for the most part is very handy when searching and sorting data. There are times though where this isn't ideal, such as managing usernames or passwords.
If you take a look at the DDL of a table, you'll notice that most of your text-based columns including char and varchar fields use "COLLATE SQL_Latin1_General_CP1_CI_AS". If you would like certain columns to be case sensitive you will want to use "COLLATE SQL_Latin1_General_CP1_CS_AS" for your column definition. Please note the substitution of CI for CS in the collation statement.
If you don't have the option to change your column definition, there are a few other options to consider such as casting your columns to varbinary values when performing searches.