I'm working with an SQLite DB where all columns are of NVARCHAR data type.
Coming from MS SQL background I know that NVARCHAR has additional baggage associated with it, my first impulse is to refactor most column types to have concrete string lengths enforced (most are under 50 chars long).
But at the same time I know that SQLite treats things a bit differently.
So my question is should I change/refactor the column types? And by doing so is there anything to gain in terms of disk space or performance in SQLite?
DB runs on Android/iOS devices.
Thanks!
So instead of digging through critic documentation I did a bit of experimenting with column types.
Database I'm working with has well over 1 million records, with most columns as NVARCHAR, so any change on column datatypes was easily seen in file size deltas.
Here are the results I found in effort to reduce DB size:
NVARCHAR: Biggest savings came from switching column types where possible from NVARCHAR to plain INT or FLOAT. On a DB file of 80MB savings were in Megabytes, very noticable. With some additional refactoring I dropped the size down to 47MB.
NVARCHAR vs. VARCHAR: Made very little difference, perhaps a few KBs on a DB of a size of 80MBs
NVARCHAR vs. OTHER String Types: Switching between various string based types made almost no difference, as documentation points out all string types are stored all the same in SQLite, as TEXT
INT vs OTHER numerics No Difference here, SQLite stores all as NUMBER in the end.
Indexes based on NVARCHAR columns also took up more space, once re-indexed on INT columns I shedded a few MBs