In SQL Server, the default string handling is to be case-insensitive (in other words, 'abc' = 'ABC').
If you want a case-sensitive column, you need to change the COLLATION on that column.
In my environment, the default collation for a string column is SQL_Latin1_General_CP1_CI_AS. The "CI" part of that means case-insensitive. To make the column case-sensitive, here's what you'd do during table creation:
CREATE TABLE Foo ( ... Str NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS );
Notice the "CS" instead of "CI". Now when you're using that column in a WHERE clause, it will only be a match if the case matches.