SQLite data types

Question

I am using SQLite in a Java application through Zentus. In this context I need to save and query Java long values in my database. Coming from other RDBMS I created the table as following to store long values:

CREATE TABLE myTable (id INTEGER PRIMARY_KEY, longValue LONG)

This solution produces the excepted behavior but after reading the SQLite documentation on data types I understood that my LONG type has the same effect than using TEXT => longValue is stored as text.

I then decided to change this to INTEGER (which length is variable and can store up to 64 bit integers which is the length of Java long) in order to have cleaner code and may be to save some disk space and to increase performances because my longValues are inserted and queried as long.

After comparing the performances and the size of the created databases I am not able to see any difference between:

CREATE TABLE myTable (id INTEGER PRIMARY_KEY, longValue LONG)

and

CREATE TABLE myTable (id INTEGER PRIMARY_KEY, longValue INTEGER)

Any comments, experiences or feelings on the subject?

Answer 1

In SQLite, data types are per-value, not per-column. So when you insert integers, they're stored as integers regardless of the column type.

Answer 2

SQLite chooses automatically the right size. From http://www.sqlite.org/datatype3.html:

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

SQLite uses dynamic types and is schema free.

Answer 3

After looking at performances and size of the created databases I am not able to see any difference between:

There isn't any difference. INTEGER has integer affinity and LONG has numeric affinity. And, http://www.sqlite.org/datatype3.html says:

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.