On Fri, 02 Sep 2016 15:10 +0100, Tim Streater <t...@clothears.org.uk> wrote: > On 02 Sep 2016 at 15:05, Dave Blake <davebl...@kodi.tv> wrote: > >> Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I >> know I only want 20 chars or less, will result in a smaller database? > > Makes no difference whatever.
Mr. Streater's answer to Mr. Blake's question is correct: TEXT vs VARCHAR(20) make no difference at all in the file format. But for completeness, let me just point out that there is a difference between these two declarations from the point of view of the query planner. When the query planner sees VARCHAR(20) versus (say) VARCHAR(2000) it assumes that the second field will usually store about 100 times as much text as the first. In other words, it uses the argument to VARCHAR as an estimate for the average size of the field. This goes into building an estimate for the average size of each row. When trying to decide between two indexes, if all else is equal, SQLite will choose the index with the smaller estimated row size. So, while the declared type makes very little difference, and though it makes no difference at all with regard to how content is stored on disk, the declared type is not totally ignored by SQLite and does have some small influence on query planning. Note that the difference is quite small and it takes a subtle experiment (and a subtle test case) to verify that the size estimation logic is working. For size estimating purposes, SQLite treats "TEXT" as if it were "VARCHAR(20)". https://www.sqlite.org/src/artifact/c2ccfcd?ln=1195 -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
