{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}