You do not have to change the database or column collation in order to use different collations for specific tasks. In fact you can mix collations to match your needs.

If you have your database built with case insensitve collation but would like to do case sensitive search just use the COLLATE keyword.

For example the first select will find all records where test code is 'ABC' regardless of the case - so abc, AbC aBc and such will be found. The second one will find only the record matching the case of the specified string 'ABC' in this case. The third example will mix the case sensitive and case insensitive search in one query.

SELECT * FROM Tests

WHERE TestCode = 'ABC'

SELECT * FROM Tests

WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS

SELECT * FROM Tests

WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS

OR TestCode = 'CBS'