SQL - Data Types (MySQL/SQL Server/SQLite)

Overview

This page maps common conceptual types to each engine. SQLite uses type affinity and is flexible.

Text

• MySQL: VARCHAR(n), TEXT
• SQL Server: NVARCHAR(n), VARCHAR(n), TEXT (deprecated) → use VARCHAR(MAX)/NVARCHAR(MAX)
• SQLite: TEXT

Integers & Identity

• MySQL: INT, BIGINT, identity via AUTO_INCREMENT
• SQL Server: INT, BIGINT, identity via IDENTITY(1,1) or sequences
• SQLite: INTEGER primary key auto-increments when declared INTEGER PRIMARY KEY

Date/Time

• MySQL: DATE, TIME, DATETIME, TIMESTAMP
• SQL Server: DATE, TIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET
• SQLite: store as TEXT (ISO8601), REAL (Julian day), or INTEGER (Unix time)

Booleans

• MySQL: BOOLEAN alias for TINYINT(1)
• SQL Server: BIT
• SQLite: no native boolean; use INTEGER 0/1

Example table with types

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  salary DECIMAL(10,2),
  hired_at DATETIME
);

CREATE TABLE employees (
  id INT IDENTITY(1,1) PRIMARY KEY,
  first_name NVARCHAR(50) NOT NULL,
  active BIT NOT NULL DEFAULT 1,
  salary DECIMAL(10,2),
  hired_at DATETIME2
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1,
  salary NUMERIC,
  hired_at TEXT -- ISO8601
);