On Fri, Sep 2, 2016 at 5:04 PM, Richard Hipp <d...@sqlite.org> wrote:
> 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 Very interesting. Thanks for explaining this Richard. I had no idea. Any chance a CHECK constraint of the form CHECK(length(col) = N) would be recognized similarly for size estimates? For example, we use Guid extensively, which are typed blob, but often add that CHECK(length(guid) = 16) constraint. I know 20 is not far off from 16 in this particular case, but more accurate is always better, no? Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
