How do I change SQL Server 2005 to be case sensitive?

Question

I hate case sensitivity in databases, but I'm developing for a client who uses it. How can I turn on this option on my SQL Server, so I can be sure I've gotten the case right in all my queries?

Answer 1

You don't actually need to change the collation on the entire database, if you declare it on the table or columns that need to be case-sensitive. In fact, you can actually append it to individual operations as needed.

SELECT name WHERE 'greg' = name COLLATE Latin1_GENERAL_CS_AS

I know, you said that you want this to apply throughout the database. But I mention this because in certain hosted environments, you can't control this property, which is set when the database is created.