{question}

How should "COLLATION" behave when using it in a table definition?

{question}

{answer}

Here is an example of how "COLLATION" should behave when using it in a table definition:

Create a table using case insensitive collation

create table ColTest(MyTextCI nvarchar(100) COLLATION "8859-1U");

Put some data there

insert into ColTest(MyTextCI) values ('AbCd');
insert into ColTest(MyTextCI) values ('ABCD');
insert into ColTest(MyTextCI) values ('abcd');

This query should return one row and count = 3 for insensitive collation

SQL> select MyTextCI,count(*) from ColTest group by MyTextCI;
MYTEXTCI COUNT
--------- ------
AbCd 3

This should also return just one row

SQL> select distinct MyTextCI from ColTest;
MYTEXTCI
---------
AbCd

More information and examples can be found here.

{answer}