99 times out of 100, I've wanted case-insensitive strings in SQL Server. It's good for manually querying things, it's good for searchable data like names or email addresses, and it's generally accepted as the standard.
However, once in awhile, you are going to want something case-sensitive. This would be for things like storing base-64 encoded values or URL shortener codes. Here's how to do that.
For more info on collation, check out the docs