Size and limits in SQLite (32 or 64 Bits ?)

It is not really clear in sqlite.org what SQLite have as limits and size.

  1. 32 or 64 Bits ?
    SQLite seems to be 32 Bits (whatever that means).

2. Size and limits
How many TABLE can I add in a SQLite Data Base ?
How many Columns in a Record can I add in a SQLite TABLE ?
I created one that have 81 Columns.

I suppose that the number of Records is far above that (and depends on the number of TABLEs; and data in the Records are affected by those).

I suppose that the more TABLE(s) and Columns, the more time the data takes to be read or/and write.

I also know that a Listbox have a finite number of Columns (64 or 256); and here comes my trouble in mind.

Usually, my mouth emit strange noises when I read in a book the variables limits (Integer for example), since that value does not change from IDE to another IDE / Language and you get that information in the language reference and in all books about that IDE:(
But @ sqlite.org, this was not explicitely given (and I had the Listbox max # of column [64 or 256, depends on Xojo version] in mind and that make troubles in my mind).

All: how do I limit to 64 or 256 columns a read in sqlite ?
(to avoid errors at Listbox display time ?)

All
I have a db with 62 columns nd displaying (data for) that amount of columns in a Listbox is not easy to read.
I use three windows to fullfil the DB (that is one Cardboard A4 page) and I have three other pages to work with (mainly CheckBox / RadioButtons).
Do you have experience of this kind of amount of Columns (or more) ?

Dave / DB Schema Reader providers:
How do you deal with a 50 TABLEs or 129 Columns in a TABLE in your Tadpole / “Application” software ?
(SQLite DB Graphic Design)

SQLite can be build for 32 and 64bit.
Integers in SQLite are 64-bit.

Records is not limited. Column limit is 2000 by default, but can be raised.

see limits website

Hi Christian,

that is the location I read earlier. I do not recall where it says it is 32 bits (I read a lot of pages).

Thanks for the tips.

2000 Columns: i am dreamingÂ… how one can display that amount of columns :wink:

Yikes! That reminds me of the saying, “Just because you can, it doesn’t mean you should, but someone will.”

1 Like

With a 296" Super-Duper wide screen… No seriously it would for an end user app a single record would be edited in a form or several forms. For a db manager there’s a thing called horizontal scroll bar. For several thousand columns you would definitely want to use some type of virtualization.

Thank you Tim for the smile.

Neil:
It was nearly a joke. When I have a low number of Columns, I can display them all in a Listbox.

Actually, I have - say - 64 Columns: too many columns (IMHO) for a single Listbox, but since I have three windows full of Controls (for these 64 columns), I can think to devide the db contents / 3, and display them into three different windows/ListboxesÂ…

I actually have coded a window with two Listbox:
a. One Listbox to display the db contents sorted by letters (actually a thru z, since no name start with a number 26 “Tabs” are enough). A click in a letter populates the second Listbox.
In short: a click in the A Tab display all Names that starts with a A. And so on.

b. Another listbox that displays the peopleÂ’s first name, family name and birth date.

A double click in an entry of Listbox b will open that Record ans display its contents into the correspondant windows, so the user can watch its contents.

Of course, this actually works fine with very few Records: I do not have an idea on how this can be with say >1,000 Records in the RecordSet (the speed to get the data)Â… This is a bit like terra incognita for me.

And if the project goes to its end, I will have a bit more columns: I can manage to display portions of the number of columns “sorted” by original sheet of paper Pages. The users only have to know what part of the record they want to read.

Fortunately, once each record is written to the db, there is only a small number of reasons to access to them (statistics: more often: once a year ?). The more accessed data will come from the history db (one entry for each meeting / visit). Each entry is 6 fields (First Name, Family Name, Birth Date, Today date) plus two Service data fields and one Report text stored in a TextArea that explain what weÂ’ve talked / what to do.

PS: I am watching on TV a Ridley Scott movie from 2015 with Matt Damon (Single on Mars ? or Alone on Mars ?), so I also have a Terra Incognita there too.

If you ever find your self designing a 2000 column database table… Seriously… take a step back (heck take 3 steps back), and think about relational tables, sparse data and how a data vector of that size is going to impact performance… you most likely will come up with a MUCH better design.

[quote=410925:@Christian Schmitz]SQLite can be build for 32 and 64bit.
Integers in SQLite are 64-bit.[/quote]

On disk, integers take only the number of bytes required (see https://www.sqlite.org/datatype3.html). However in memory they will be 64 bits.

I also recall that integer values 0 and 1 take no space at all but can’t find a reference to that.

@Tim Streater

0 and 1 really DO take space.

Any information stored will use some physical space; simple mathematics.
At least one bit …

[quote=412203:@J H Timmerman]@Tim Streater

0 and 1 really DO take space.[/quote]

Well you might imagine they would take a byte. But IIRC there is already a field indicating to SQLite internally whether a particular integer column takes 1, 2, 3, 4, 6, or 8 bytes (depending on the integer’s value). That field is itself a byte and two of its values are dedicated to integer values 0 and 1.

OK I found it. See here and look at Section 2.1, Record Format. Seems that a NULL “value” also occupies no space.

well I’ll be darned… :slight_smile:

that is good news that boolean values (1 and 0) takes no space beside type.