What does the COLLATE keyword do when creating a sqlite index?

Question

According to the sqlite3 documentation,

The COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

What does a collating sequence do, and what is a BINARY collating sequence?

Solution 1

It is the way that the sql engine orders data internally. Binary Collation does what it suggests, it does a binary comparison. Generally its the fastest collation though I have never quantified it, as it checks bit patterns, which means it is case and accent sensitive.

Solution 2

Binary collation compares your string byte by byte, as in an unicode table. For example: A,B,a,b. A case insensitive (NOCASE) order would be: a,A,b,B.

The advantage of binary collation is its speed, as string comparison is very simple/fast. In the general case, indexes with binary might not produce expected results for sort; however, for exact matches they can be useful.

COLLATE NOCASE also affects case sensitive queries.

If you have a column with these values: 'aa', 'aA'

select * from table where col = 'aa'

If you have created your column with COLLATE NOCASE it will return both 'aa' and 'aA'. Otherwise, if you didn't specify it, it will return only 'aa'.

You can also specify it in a query (this is slower then if you had created your column with COLLATE NOCASE)

select * from table where col = 'aa' COLLATE NOCASE