SQL Data Types

In SQL, each column must be assigned a data type that defines the kind of data it can store, such as integers, dates, text, or binary values. Choosing the correct data type is crucial for data integrity, query performance and efficient indexing.

1. Numeric Data Types

Numeric data types are fundamental to database design and are used to store numbers, whether they are integers, decimals or floating-point numbers. These data types allow for mathematical operations like addition, subtraction, multiplication and division, which makes them essential for managing financial, scientific and analytical data.

Exact Numeric Datatype

Exact numeric types are used when precise numeric values are needed, such as for financial data, quantities, and counts. Some common exact numeric types include:

Example:

CREATE TABLE Product_Sales (
    ProductID INT PRIMARY KEY,
    Quantity SMALLINT,
    UnitPrice DECIMAL(10,2),
    TotalAmount DECIMAL(10,2)
);

Approximate Numeric Datatype

These types are used to store approximate values, such as scientific measurements or large ranges of data that don't need exact precision.

Example:

CREATE TABLE Measurements (
    SensorID INT,
    Temperature FLOAT,
    Humidity REAL
); 

2. Character and String Data Types

Character data types are used to store text or character-based data. The choice between fixed-length and variable-length data types depends on the nature of your data.

Example:

CREATE TABLE Employee_Info (
    EmpID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName CHAR(30),
    Bio TEXT
);

Unicode Character String Data Types

Unicode data types are used to store characters from any language, supporting a wider variety of characters. These are given in below table.

Example:

CREATE TABLE International_Users (
    UserID INT PRIMARY KEY,
    FullName NVARCHAR(100),
    Country NCHAR(50)
); 

3. Date and Time Data Type

SQL provides several data types for storing date and time information. They are essential for managing timestamps, events and time-based queries. These are given in the below table.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    OrderTime TIME,
    ShippedAt DATETIME
);

4. Binary Data Types in SQL

Binary data types are used to store binary data such as images, videos or other file types. These include

Example:

CREATE TABLE Product_Images (
    ImageID INT PRIMARY KEY,
    ImageName VARCHAR(100),
    ImageData VARBINARY(MAX)
);

5. Boolean Data Type in SQL

The BOOLEAN data types are used to store logical values, typically TRUE or FALSE. It is commonly used for flag fields or binary conditions.

Example:

CREATE TABLE User_Status (
    UserID INT PRIMARY KEY,
    IsActive BIT,
    IsVerified BIT
);

6. Special Data Types

SQL also supports some specialized data types for advanced use cases:

• XML Data Type: Used to store XML data and manipulate XML structures in the database

Example:

CREATE TABLE XML_Records (
    RecordID INT PRIMARY KEY,
    ConfigData XML
);

• Spatial Data Type (Geometry): stores planar spatial data, such as points, lines, and polygons, in a database table.

Example:

CREATE TABLE Locations (
    LocationID INT PRIMARY KEY,
    Area GEOMETRY
);