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 collation1:
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?
1 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.