(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:
|INTEGER||In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.|
|REAL||In SQLite, all floating point numbers are 64 bits wide.|
|TEXT||In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.|
|BLOB||Width doesn't matter here either.|
|INTEGER ?||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.)|
|(your choice)||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 signed integer.
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 container."
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 string.
sqlite> SELECT a, typeof(a) FROM foo; 3|integer 3.14|real pi|text
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] ( [a] varchar(50), [b] char(5), [c] nchar, [d] nvarchar(5), [e] nvarchar(max), [f] text );
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] ( [a] characters, [b] textish, [c] charbroiled, [d] context );
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 Soldier").
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.