(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
Different types of, er, types
At the SQL language level, the biggest difference with SQLite is the way it deals with data types. There are three main differences to be aware of:
There are only a few types
And types are dynamic
(But not entirely, because they have affinity)
And type declarations are weird
Okay, so actually that's FOUR things, not three. But the third one doesn't
really count, so I'm not feeling terribly obligated to cursor all the way back
up to the top just to fix the word "three". Let's keep moving.
Only a few types
SQLite values can be one of the following types:
The following table shows roughly how these compare to SQL Server types:
In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.
In SQLite, all floating point numbers are 64 bits wide.
In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.
Width doesn't matter here either.
These are problematic, as SQLite 3 does not have a fixed point type.
(In Zumero, we handle synchronization of these by mapping them to INTEGER
and handling the scaling.)
SQLite has no data types for dates or times. However, it does have a rich
set of built-in functions for manipulating date/time values represented as
text (ISO-8601 format), integer (unix time) or real (Julian day).
Types are dynamic
In SQL Server, the columns in a table are strictly typed. If you define a
column to be of type smallint, then every value in that column must be a 16 bit
In contrast, SQLite's approach might be called "dynamic
typing". Quoting from its own documentation: "In SQLite, the
datatype of a value is associated with the value itself, not with its
For example, the following code will fail on SQL Server:
CREATE TABLE [foo] (a smallint);
INSERT INTO [foo] (a) VALUES (3);
INSERT INTO [foo] (a) VALUES (3.14);
INSERT INTO [foo] (a) VALUES ('pi');
But on SQLite, it will succeed. The value in the first row is an INTEGER.
The value in the second row is a REAL. The value in the third row is a TEXT
sqlite> SELECT a, typeof(a) FROM foo;
The column [a] is a container that simply doesn't care what you place in it.
Well, actually, it does care. A little.
A SQLite column does not have a
type requirement, but it does have a type preference, called an affinity. I'm
not going to reiterate the type affinity rules from the SQLite
website here. Suffice it to say that sometimes SQLite will change the
type of a value to fit match the affinity of the column, but you probably
don't need to know this, because:
If you declare of column of type TEXT and always insert TEXT into it, nothing weird will happen.
If you declare of column of type INTEGER and always insert INTEGER into it, nothing weird will happen.
If you declare of column of type REAL and always insert REAL into it, nothing weird will happen.
In other words, just store values of the type that matches the column. This
is the way you usually do things anyway.
Type declarations are weird
In a column declaration, SQLite has a rather funky set of rules for how it parses the type. It uses these rules to try its very best to Do The Right Thing when somebody ports SQL code from another database.
For example, all of the columns in the following table end up with TEXT
affinity, which is probably what was intended:
CREATE TABLE [foo]
But in some cases, the rules are funky. Here are more declarations which all
end up with TEXT affinity, even though none of them look right:
CREATE TABLE [foo]
And if you want to be absurd, SQLite will let you. Here's an example of a declaration of a column with INTEGER affinity:
CREATE TABLE [foo]
[d] My wife and I went to Copenhagen a couple weeks ago
to celebrate our wedding anniversary
and I also attended SQL Saturday while I there
and by the way we saw
Captain America The Winter Soldier
there as well which means I got to see it
before all my friends back here in Illinois
and the main reason this blog entry is late is
because I spent most of the following week gloating
SQLite will accept nearly anything as a type name. Column [d] ends up
being an INTEGER because its ridiculously long type name contains the characters "INT" (in "Winter
Perhaps we can agree that this "feature" could be easily abused.
There are only four types anyway. Pick a name for each type and stick to it.
Once again, the official names are:
(If you want a little more latitude, you can use INT for INTEGER. Or VARCHAR
for TEXT. But don't stray very far, mkay?)
Pretend like these are the only four things that SQLite will allow, and then it
will never surprise you.
SQLite handles types very differently from SQL Server, but its approach is mostly a superset of your
existing habits. The
differences explained above might look like a big deal, but in practice, they probably won't affect you
all that much.