CODEWITHSUNDEEP
sqlite
Boppity Bop
Boppity Bop

Reputation: 10463

SQLite dynamic typing details

Since SQLite can store any data in any column, my question - how SQLite engine knows which is which?

Lets say a table has column A and there are 2 values 123 and '#$%'. How these two are stored (and I would like to know byte-by-byte layout to understand)?

What happens with these values if I do select * from table where a > 0 ?

Obviously the engine needs to figure out each value type in column A.. How exactly does it do?

Does it store a type marker with each value so it examines marker on select? or does it evaluates type every time it retrieves a value?

Upvotes: 0

Views: 101

Answers (1)

Philippe Banwarth
Philippe Banwarth

Reputation: 17725

There is a type marker called serial type. It is a variable length integer encoding both the type and the length of the value.

NULL, 0, 1, and '' (empty text) need 1 byte (1 byte for the serial type, no value)

Integers needs 2 to 9 bytes (1 byte serial type, 1 to 8 byte(s) value), so 123 needs 2 bytes.

For text values, the serial type may expand to multiple bytes. Assuming UTF-8, '#$%' needs 4 bytes (1 byte serial type because the encoded length is <= 57)

There is more details in the Database File Format.

Upvotes: 1

Related Questions