SQLite Data Types

Question

Hi all,

I am using SQLite in a Java application through Zentus. In this context I need to save and queries Java long values in my database. Coming from other RDBMS I created table as following to stor 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 => My longValues are stored as text.

I then decided to change this to INTEGER (which length is variable and can store up to 64 bits 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 looking at performances and 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 ??

Thanks in advance

Manu

Answer 1

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 2

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 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.