On 2015-08-27 04:06 PM, Nicolas J?ger wrote: > Hi Darko, Igor and others. > > so the only reason to define datatype in sqlite is for the size on > the disk ? > > so why not just only using `BLOB` (excepted for `INTEGER PRIMARY > KEY`) ? > > being less persmissive wouldn't make querries run faster ? > for example, the comparisons would not have to try to attempt to > convert or even check and determine the type of the values.
Well yes, but because the data type is not strictly enforced does not mean SQLite is not optimizing storage or speed. SQLite checks and stores the actual type information with every value very efficiently. (It's referred to as "affinity" in SQLite). As an example, storing the value 1 needs a very small footprint, the same even (if not smaller than) the blob value 0x01. If the next row in that same column requires saving the word 'Rubberduck' it will be saved with different type information. You can always retrieve exactly what you've added and be sure of its storage being efficient. Compare this to the old CHAR(n) types where you had to provision say 80 characters when you 99% of the time only used 5, ending up with a typical data density of less than 8%. The point about comparisons is somewhat valid, usually as long as both compared values are of the same type it is not a problem, or if the column has a collation (or the index used, or the actual comparison). Sometimes though, you do end up having to convert, which eats up a few cycles, but still very little as the type information is still present. Internally though, all values in SQLite's mind are really just blobs - so your point is served.
