No, you cannot tell SQL Server to collate different rows differently. Collation applies to the column, database, or instance.
Within a query, it is possible that you could apply different collation rules, e.g. for comparisons, using a
CASE expression. Assuming you can add a column to indicate what collation should be used for that row.
SELECT pk FROM dbo.basetable
WHEN collation = 1 THEN name COLLATE Albanian_BIN
WHEN collation = 2 THEN name COLLATE Chinese_Taiwan_Bopomofo_90_BIN2
WHEN collation = 3 THEN name COLLATE Norwegian_100_CS_AS_WS
END = @parameter;
But this will not allow you to store data in different collations in the same column. If this is an actual need and not just some bet, you could consider some kind of EAV design, like:
CREATE TABLE dbo.eav
pk INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.basetable,
-- maybe collation # to use is specified here, or maybe in base table
alb NVARCHAR(255) COLLATE Albanian_BIN,
chtw NVARCHAR(255) COLLATE Chinese_Taiwan_Bopomofo_90_BIN2,
nor NVARCHAR(255) COLLATE Norwegian_100_CS_AS_WS,
Is it possible in SQL Server to set collation on individual rows in a table?NO.
If not, perhaps does anyone know if this is a planned feature for SQL Server 2016?NO. What benefit do you see in doing this ?
asciias well but I ran a test on that. I converted back to LATIN1_GENERAL_CI_AS and the value did not change back. That's proof you're right.