SQLite Forum

Numeric vs integer
Login

Numeric vs integer

(1) By John Dennis (jdennis) on 2021-08-15 05:34:49 [link] [source]

I imported a database from another RDBMS, which has many columns defined as "i2". SQLite3 doesn't recognise this as "int2" so the column was created with affinity of Numeric and type as NUM. The loaded data is all integers, so a typeof() returns integer.

I find however that I frequently seem to need to cast these columns as integer in order for maths to work correctly.

Question 1: How much space is taken up in the physical database by a NUM/Numeric field which is storing the integer value 3? If converted to Integer it will be 8 bytes - if the current definition is also 8 bytes then the space requirements will not change.

Question 2: Am I correct to assume that a value defined as integer will be more efficient in artihmetic functions (eg, sum(..)) than having the integer value stored in a Numeric field? Although I would expect it to be very marginal...

(2) By Warren Young (wyoung) on 2021-08-15 05:49:34 in reply to 1 [link] [source]

Q1 is answered in section 2 here.

As for Q2, the difference will depend on the CPU, the data, the compiler, the C standard library… it’s a matter for benchmarking, not for vague speculation on Internet discussion fora.

(3) By John Dennis (jdennis) on 2021-08-15 06:32:22 in reply to 2 [source]

Thank you. I soon as I opened the link I realised I had read the document before, but I think my brain got fixated on the 8-byte integer that is used internally when processing integers.

Q2 was more of a "for interest" question - I have absolutely no concerns about the performance of SQLite.

I shall rebuild my database with all of the i2 columns defined as integer.

(4) By Keith Medcalf (kmedcalf) on 2021-08-15 17:08:48 in reply to 1 [link] [source]

Question 2: Am I correct to assume that a value defined as integer will be more efficient in artihmetic functions (eg, sum(..)) than having the integer value stored in a Numeric field? Although I would expect it to be very marginal...

An integral value that fits in a 64-bit two's-complement integer field is stored as an VARINT (1 to 8 byte integer) when stored.

A floating point value (that has a non-zero fractional part) is stored as a 8-byte IEEE-754 float.

An INTEGER affinity means that the value will be stored as an INTEGER, if it can.
A REAL affinity means that the value will be stored as a IEEE-754 float, if it can.
A NUMERIC affinity means that stuff that looks like a number will be stored as an INTEGER if possible, but if the number has a fractional part or is larger than will fit in an INTEGER then as REAL.

Question 2: Am I correct to assume that a value defined as integer will be more efficient in artihmetic functions (eg, sum(..)) than having the integer value stored in a Numeric field? Although I would expect it to be very marginal...

No.

An INTEGER is an INTEGER, a FLOAT is a FLOAT, a NUMERIC is whichever is appropriate in the circumstance.

I find however that I frequently seem to need to cast these columns as integer in order for maths to work correctly.

This I do not understand.

There is no difference between a NUMERIC value which is an integer and an INTEGER value which is an INTEGER -- they are both INTEGER and behave exactly the same.

(5) By John Dennis (jdennis) on 2021-08-16 04:28:00 in reply to 4 [link] [source]

I find however that I frequently seem to need to cast these columns as integer in order for maths to work correctly.

There is no difference between a NUMERIC value which is an integer and an INTEGER value which is an INTEGER -- they are both INTEGER and behave exactly the same.

Checking through my SQL scripts I find virtually all of the CAST functions are for a single column, which for historical reasons contains an integer value or an empty string. This database comes from a Dataflex application of the 1980s, which because I don't want to rewrite in something more modern is still in use today. The SQLite database is used for our web site, and ad-hoc reporting, and is refreshed after the Dataflex DB is updated.

The issue is with comparisons like this:

select ... where field>=50

where the condition evaluates to true when the field is empty. It should, of course, be null but I am not certain what affect that change might have in other places.

Yes, I am a dinosaur. Both a Dataflex and Ingres RDBMS user.