2014-04-08 12:00:00

Data Types

(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:

1. There are only a few types

2. And types are dynamic

3. (But not entirely, because they have affinity)

4. 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:

• INTEGER

• REAL

• TEXT

• BLOB

The following table shows roughly how these compare to SQL Server types:

SQL Server	SQLite	Notes
tinyint smallint int bigint bit	INTEGER	In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.
real float	REAL	In SQLite, all floating point numbers are 64 bits wide.
char varchar nchar nvarchar text ntext	TEXT	In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.
binary varbinary image	BLOB	Width doesn't matter here either.
decimal numeric money smallmoney	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.)
date datetime datetime2 datetimeoffset smalldatetime time	(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.

Type affinity

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:

• INTEGER

• REAL

• TEXT

• BLOB

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

Summary

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.