5

Since the SQLite engine will not truncate the data you store in a text column, is there any advantage in being specific with column sizes when you define your schema? Would anyone prefer this:

CREATE TABLE contact(
 id    INTEGER PRIMARY KEY, 
 name  VARCHAR(45),
 title VARCHAR(10)
);

over this:

CREATE TABLE contact(
 id    INTEGER PRIMARY KEY, 
 name  TEXT,
 title TEXT
);

Why?

Are there advantages to not being specific?

Jannie Theunissen
  • 28,256
  • 21
  • 100
  • 127

1 Answers1

4

The advantage of using varchar(x) is that it is compatible with other database systems - if I remember correctly, TEXT isn't a standard SQL datatype.

Other than being more standards-compliant, there is indeed no difference whether you use TEXT or VARCHAR. More info at http://sqlite.org/datatype3.html

Gabriel Reid
  • 2,506
  • 18
  • 20
  • Are there advantages to not being specific. What do you normally use? – Jannie Theunissen Dec 22 '10 at 09:08
  • @JannieT Sure there are advantages to not being too specific. Let's say for some reason at a later date you need to move from SQLite to Postgres -- if your SQL (both in your DDL and DML statements) is generic, it will be that much less painful to switch to another system. – Gabriel Reid Dec 22 '10 at 10:05