{question}

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

{question}

{answer}

Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.  NuoDB supports currently supports two types of collation: 1. (case sensitive) which is default and (2.) 8859-1U (case insensitive).

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

Create a table using case insensitive collation

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

Insert data into the table:

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 query should also return just one row:

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

More information and examples can be found here.

{answer}