Case Sensitive Search on a Case Insensitive SQL Server

Most SQL Server installations are installed with the default collation which is case insensitive.  This means that SQL Server ignores the case of the characters and treats the string 'ram' equal to the string 'RAM'.  If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?

SolutionOne option is to specify the collation for the query to use a case sensitive configuration.  Let's show an example of a case sensitive search on a case insensitive SQL Server

SELECT * FROM (

SELECT 'RAM' a

UNION ALL SELECT 'Ram' a

UNION ALL SELECT 'ram' a

)RAMWHERE

SELECT * FROM (

                  SELECT 'RAM' a

                  UNION ALL

                  SELECT 'Ram' a

                  UNION ALL

                  SELECT 'ram' a

)RAM WHERE

a LIKE '%Ram%' Collate SQL_Latin1_General_CP1_CS_AS