String storage size in sqlite

Question

After using lots of SQL Server and SQL Server Compact, I have recently started to use Sqlite. I'm currently creating tables in Sqlite and have noticed that there doesn't seem to be any need to input the string length limit when defining string columns. In SQL Server a 255-character string datatype is defined like: varchar(255). In SQL Server Compact it is defined like: nvarchar(255).

However, in Sqlite, it appears that the column datatype can be defined simply as text, without inputting any size limit. Is there a way to define a size limit in sqlite? Or is this not even necessary?

Answer 1

SQLite does let you write VARCHAR(255) or NVARCHAR(255), but the only relevant part of that is CHAR (which gives the column "text affinity"). The number in parentheses is allowed for compatibility with standard SQL, but is ignored.

In SQLite, there's little need for length constraints on strings, because (1) it doesn't affect the amount of space the string takes up on disk and (2) there's no arbitrary limit (except for SQLITE_MAX_LENGTH) on the length of strings that can be used in an INDEX.

If you have an actual need to place a limit on the number of characters in a column, just add a constraint like CHECK(LENGTH(TheColumn) <= 255).

Answer 2

There is a managable string or blob limit in SQLite. The default is 10^9 bytes. See documentation BTW you don't need to specify it on column declaration anyway.