Case-Sensitive Collation on a select query in SQL Server

Question

Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS

I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set to case insensitive at the server level. My requirement is that I need to treat each value as different based on the case sensitivity for one particular query. I'm using COLLATE SQL_Latin1_General_CP1_CS_AS in the WHERE clause to enforce case sensitivity. My question is: will this collation be applied only to this query or will it end up changing this setting at the column or table level for all queries that use this column?

Create Table #tmpTest 
(
id int primary key identity(1,1),
name varchar(100)
)

Insert Into  #tmpTest  values ('TestQuery'),('TESTQUERY'),('TEstquerY')

Select * from #tmpTest
 Where name = 'TestQuery'

Select * from #tmpTest
 Where name = 'TestQuery'
 Collate SQL_Latin1_General_CP1_CS_AS

Answer 1

I'm using Collate SQL_Latin1_General_Cp1_CS_AS in the where clause to enforce case sensitivity. Will this collation be applied only to this query or will it end up changing this setting at the column or table level for all queries that use this column?

The COLLATE keyword/option when specified in a query applies only to the predicate or expression that it is attached to. Meaning, not only is this just for the query, it's only for one part of the query, not the entire query. So, if you added more predicates to your WHERE clause, they would not be affected by your current use of COLLATE. For example:

WHERE name = 'TestQuery' COLLATE SQL_Latin1_General_CP1_CS_AS
AND other_column = 'other value' -- uses collation of "other_column" column
AND another_column = 'another value' COLLATE Latin1_General_100_BIN2
AND @some_variable = 'literal' -- uses default collation of database

The only way to change the collation of a column (there is no such thing as table-level collation in SQL Server) is to use the ALTER TABLE ... ALTER COLUMN statement.

Answer 2

In this case it will only append to your query.