How to set collation for a connection in SQL Server?

How can i set the collation SQL Server will use for the duration of that connection?

Not until i connect to SQL Server do i know what collation i want to use.

e.g. a browser with language fr-IT has connected to the web-site. Any queries i run on that connection i want to follow the French language, Italy variant collation.

i envision a hypothetical connection level property, simlar to SET ANSI_NULLS OFF, but for collation¹:

SET COLLATION_ORDER 'French_CI_AS'

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

and later

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '%l''ecole%'

and later

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = 'Cour de l''école'

At the same time, when a chinese customer connects:

SET COLLATION_ORDER Chinese_PRC_CI_AI_KS_WS

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

or

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '學校'

or

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = '學校的操場'

---

Now i could alter every SELECT statement in the system to allow me to pass in a collation:

SELECT TOP 100 FROM Orders
WHERE CustomerID = 3278
ORDER BY ProjectName COLLATE French_CI_AS

But you cannot pass a collation order as a parameter to a stored procedure:

CREATE PROCEDURE dbo.GetCommonOrders 
   @CustomerID int, @CollationOrder varchar(50)
AS

SELECT TOP 100 FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY ProjectName COLLATE @CollationOrder

And the COLLATE clause can't help me when performing an UPDATE or a SELECT.

Note: All string columns in the database all are already nchar, nvarchar or ntext. i am not talking about the default collation applied to a server, database, table, or column for non-unicode columns (i.e. char, varchar, text). i am talking about the collation used by SQL Server when comparing and sorting strings.

---

How can i specify per-connection collation?

See also

• Similar question, but for ADO.net and connection strings
• Similar question, but for ASP.net MVC2 and MySQL

¹ hypothetical sql that exhibits locale issues

As marc_s commented, the collation is a property of a database or a column, and not of a connection.

However, you can override the collation on statement level using the COLLATE keyword.

Using your examples:

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName COLLATE Chinese_PRC_CI_AI_KS_WS LIKE N'學校'

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName COLLATE Chinese_PRC_CI_AI_KS_WS = N'學校的操場'

Still, I cannot find a statement on using COLLATE with a dynamic collation name, leaving as only possible solution dynamic SQL and EXEC. See this social.MSDN entry for an example.