Collation by row, not by column

Question

Is it possible in SQL Server to set collation on individual rows in a table (as opposed to by column, database or server)?

If not, perhaps does anyone know if this is a planned feature for SQL Server 2016?

The only problem this solves is curiosity between two colleagues.

Answer 1

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
  WHERE 
  CASE 
       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,
  basecolumn SYSNAME,
  -- 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,
  ...
);

Answer 2

While it is true, that the answer to your question is definitely NO, you sort of can achieve a fudge.

use tempdb;
go

create table ct1 ( id int identity(1,10) primary key not null, name nvarchar(4000) collate Albanian_CI_AS);
create table ct2 ( id int identity(1,10) primary key not null, name nvarchar(4000) collate French_CI_AS);
create table ct3 ( id int identity(1,10) primary key not null, name nvarchar(4000) collate Estonian_CI_AS);
create table ct4 ( id int identity(1,10) primary key not null, name nvarchar(4000) collate SQL_Latin1_General_CP1_CI_AS);

go
create view ctview 
as
select id,cast( name as sql_variant ) name
from ct1 
union all
select id,cast( name as sql_variant ) name
from ct2 
union all
select id,cast( name as sql_variant ) name
from ct3 
union all
select id,cast( name as sql_variant ) name
from ct4;
go
insert ct1(name) values('Albanian');
insert ct2(name) values('French');
insert ct3(name) values('Estonian');
insert ct4(name) values('sql');
go
select id, name, sql_variant_property(name, 'collation') as collation
from ctview;
go
drop view ctview;
drop table ct1; 
drop table ct2; 
drop table ct3; 
drop table ct4;