Case-Sensitive Updates in a Case-Insensitive World

Assuming you're in a case-insensitive ("CI" in the collation name) column, this UPDATE statement:

UPDATE tablefoo
set foo = 'Z'
where foo = 'z'

will just update all values where foo = 'z' or 'Z'. SQL Server is not by default case-sensitive, be it at the server, database, or column level. Each of those levels can have their own collation set differently, and there are rules for determining how they are enforced.

For example, in the US, this is the default collation: SQL_Latin1_General_CP1_CI_AS, a case-insensitive, accent-sensitive collation.

So, given this data:

tablename: tablefoo
foo
z
Z
Z
z
Z
z

How can we update just the three records where foo = 'z' but not the 3 records where foo = 'Z'? Without ALTER'ing any objects to change their collation, obviously...

UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'

And then, to make our UPDATE statement non-repeatable for safety, we should add one more line:

UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'
and foo COLLATE SQL_Latin1_General_CP1_CS_AS <> 'Z'

More reading on collations:
http://technet.microsoft.com/en-us/library/ms180175.aspx