1

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.

4
  • 3
    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 ?
    – Kin Shah
    May 13, 2015 at 15:01
  • Collation is a static type system thing. It does not affect the value of strings at all. A COLLATE cast does nothing at runtime. There is no point in having different collations for each row and it is conceptually an invalid idea.
    – usr
    May 13, 2015 at 19:38
  • 1
    @usr Collation can affect the value of data. Try this example at SQLFiddle : sqlfiddle.com/#!6/e3aab/1 May 14, 2015 at 22:07
  • @MisterMagoo that is amazing! I did not know that. At first I suspected that collation simply affected the behavior of casts and ascii as 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.
    – usr
    May 15, 2015 at 9:15

2 Answers 2

4

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,
  ...
);
5
  • No need to store strings in different collations - they would be the same. Collation does not affect the value of strings. It is merely an input parameter for string processing functions. I can see the usefulness of computed columns, though.
    – usr
    May 13, 2015 at 19:39
  • @usr To be fair, we don't know what the data in the columns is being used for, and there are certainly scenarios where having it in the right collation at rest can help avoid implicit conversions (joins to other columns or parameters defined with a non-default collation), messy code (adding COLLATE everywhere is not fun), and redundant data (or runtime computations). Never mind errors (think about unions). Anyway, I don't think this is a real requirement, just an argument over a beer. May 13, 2015 at 19:54
  • As I understand it there is no collation at rest. It's the same bytes. Collation is simply a runtime interpretation. Collation casts do nothing computational.
    – usr
    May 13, 2015 at 20:08
  • Tested a bit and mixing the collation in the case does not work for me. Cannot resolve the collation conflict between "Chinese_Taiwan_Bopomofo_90_BIN2" and "Albanian_BIN" in the CASE operation. I guess the result of the case needs one collation same as it will always evaluate to only one datatype. May 14, 2015 at 15:30
  • 1
    @Mikael Thanks, I did add the caveat "it is possible"... :-) May 14, 2015 at 15:49
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; 
2
  • Well, yeah, this can work, but... May 13, 2015 at 19:51
  • ...but don't... May 13, 2015 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.