How to force SQL Server Query to be Case-Senstive

By default, SQL Server instances are non-case sensitive. In order to force a case sensitive comparison to a literal value you need to change the collation. To find out the valid collation values, use the following query:

SELECT *
FROM ::fn_helpcollations()

Here is an example of getting a query to be case sensitive:

select count(*)
from tsfile
where left(digitalphotoloc,2) = 'g:'
collate SQL_Latin1_General_CP1_CS_AS