SQL Data Types

SQL Data Types (Oracle, MySQL, PostgreSQL, SQL Server)

Choosing the right data type for each column is key to ensuring data integrity, optimizing storage, and improving performance. Different database systems support various data types — here’s a comprehensive overview for Oracle, MySQL, PostgreSQL, and SQL Server.

✍️ Why Data Types Matter

• Ensure correct data storage (e.g., numbers, text, dates)
• Enforce data validity and constraints
• Optimize performance and storage space
• Enable database engine to perform proper operations

⚙️ Common SQL Data Types by DBMS

🧩 Examples for Each DBMS

Oracle

CREATE TABLE employees (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(100),
  salary NUMBER(10, 2),
  hire_date DATE,
  is_active NUMBER(1) -- 0 = false, 1 = true
);

MySQL

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10, 2),
  hire_date DATE,
  is_active BOOLEAN
);

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  salary NUMERIC(10, 2),
  hire_date DATE,
  is_active BOOLEAN
);

SQL Server

CREATE TABLE employees (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10, 2),
  hire_date DATE,
  is_active BIT
);

💡 Tips for Working with Data Types

• Oracle’s NUMBER type is versatile — define precision and scale carefully for your needs.
• Use VARCHAR2 in Oracle, not VARCHAR (deprecated).
• For boolean values in Oracle, typically use NUMBER(1) or CHAR(1) with conventions (1/0 or Y/N).
• Always size string types appropriately to save space (e.g., VARCHAR2(50) instead of VARCHAR2(4000) if possible).
• Use CLOB (Oracle) or TEXT (others) for large text fields.

🧠 Quick Recap