SQLite VARCHAR default size

Question

In SQLite, when I say:

CREATE TABLE my_table 
(
    my_column VARCHAR 
);

without specifying the size of VARCHAR, what is the default size that is used?

I can't seem to find it at sqlite.org or figure out from my database file.

I know that SQLite doesn't enforce size when trying to stuff varchar(500) into varchar(30), but are there tradeoffs in initial size specification and what are best practices?

score 54 · Accepted Answer · edited Aug 28 '15 at 16:51

54

It doesn't matter.

SQLite does not use sizes when declaring the VARCHAR type. In fact, it almost doesn't use the type either.

You can store TEXT in an INT column in SQLite. And SQLite will never truncate a TEXT column.

edited Aug 28 '15 at 16:51

Alexander Farber

21,519
75
241
416

answered Jan 25 '11 at 00:19

Larry Lustig

49,320
14
110
160

Wow does it mean SQLite column stores a pointer to the actual data instead? – lulalala Feb 24 '13 at 07:11
1

I don't know the exact storage mechanism that SQLite uses (one aspect of the "SQL contract" is that you assume nothing about the underlying storage mechanism) but I expect it uses variable length rows with the TEXT data stored in the row. – Larry Lustig Feb 24 '13 at 13:33

score 21 · Answer 2 · edited Aug 28 '15 at 16:51

21

From what I can gather, VARCHAR is the same as TEXT

http://www.sqlite.org/datatype3.html

Also see http://www.sqlite.org/different.html#flex

edited Aug 28 '15 at 16:51

Alexander Farber

21,519
75
241
416

answered Jan 24 '11 at 23:49

hlindset

440
2
7

score 11 · Answer 3 · edited Aug 28 '15 at 16:51

11

In SQLite, Text is the parent of VARCHAR and in the default installation is no different http://www.sqlite.org/datatype3.html.

edited Aug 28 '15 at 16:51

Alexander Farber

21,519
75
241
416

answered Jul 24 '11 at 00:59

htmldrum

2,431
20
20

SQLite VARCHAR default size

3 Answers3

Linked