40

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?

Mike Baxter
  • 6,868
  • 17
  • 67
  • 115
  • 2
    You do not need to specify string column lengths, but you may (see the [syntax](http://www.sqlite.org/syntaxdiagrams.html#type-name)). However, unlike other DB implementations, fixed-size areas are not allocated for columns, but rather rows are constructed sort of like lists of objects. So there is no "penalty" for having a wide column that is mostly used for narrow values. – Hot Licks Jul 22 '13 at 10:53

2 Answers2

41

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).

dan04
  • 87,747
  • 23
  • 163
  • 198
  • it's good way to store my whole JSON in string format in to SQLite? – Arbaz.in Nov 28 '18 at 06:22
  • @Arbaz.in I am using that technique to store offline data on my mobile app. Although I am using sqflite (Flutter package for sqlite). But I am splitting the string into multiple shorter ones since the sqlite cursor seems to be having certain limitations when it comes to max read size. Currently I am splitting json into strings of 4096 characters, but I am testing with longer ones at the moment. – miloskarakas Dec 26 '19 at 08:21
  • What are the feedback please of this method @miloskarakas ? – lupaulus Sep 27 '21 at 18:16
  • `...it doesn't affect the amount of space the string takes up on disk` how can this be? If I insert a book's worth of text into a table's column, will the database not take up more space on disk than if I inserted a single word? – Lukas Apr 21 '22 at 21:15
  • 3
    @Lukas: Of course it will. What I mean is that *declaring* a column as `VARCHAR(1000000000)` versus `VARCHAR(255)` does not make a difference in SQLite. Perhaps I should have worded it better. – dan04 Apr 22 '22 at 02:47
25

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.

gleb.kudr
  • 1,518
  • 12
  • 16