Datatypes in SQL

SQL

Datatypes define the nature of the data a column can hold, ensuring that the information stored is accurate and fits the intended format. Choosing the appropriate datatype helps optimize storage space, a critical factor in database performance. By enforcing constraints and rules through datatypes, SQL ensures the integrity of the stored data, preventing inconsistencies and errors.

Common SQL Datatypes

1. INT (Integer)

The INT datatype is a fundamental building block in SQL, primarily used for representing whole numbers without any decimal places.

Purpose: Suitable for storing information like user IDs, quantities, or any numeric data that doesn’t require decimal precision.

Example:

CREATE TABLE Employees ( EmployeeID INT, EmployeeName VARCHAR(50), 
-- Other columns...
);

2. VARCHAR and CHAR

These datatypes are employed for managing character strings, but they differ in their approach to storage.

VARCHAR (Variable Character)

The VARCHAR datatype is flexible, allowing variable-length character strings.

Purpose: Ideal for storing data where the length may vary, such as names or addresses.

Example:

CREATE TABLE Customers ( CustomerID INT, CustomerName VARCHAR(100),
 -- Other columns...
);

CHAR (Character)

The CHAR datatype, on the other hand, maintains a fixed length for character strings.

Purpose: Suitable for storing data with consistent lengths, like postal codes.

Example:

CREATE TABLE Products ( ProductID INT, ProductCode CHAR(10), 
-- Other columns...
);

3. DATE and TIME

These datatypes cater to managing temporal data, providing a structured way to handle dates and times.

DATE

The DATE datatype specifically deals with calendar dates.

Purpose: Used for storing information like birthdates or event dates.

Example:

CREATE TABLE Events ( EventID INT, EventName VARCHAR(100), EventDate DATE, 
-- Other columns...
);

TIME

The TIME datatype is employed to handle time values.

Purpose: Useful for scenarios where only the time component is relevant, such as tracking activities.
Example:

CREATE TABLE Appointments (
    AppointmentID INT,
    StartTime TIME,
    EndTime TIME,
    -- Other columns...
);

Other useful Datatype

DECIMAL:

Purpose: Ideal for situations requiring decimal precision, like handling monetary values.
Example:

CREATE TABLE Invoices (
    InvoiceID INT,
    TotalAmount DECIMAL(10, 2),
    -- Other columns...
);

FLOAT

The FLOAT datatype accommodates floating-point numbers, offering flexibility for a wide range of numeric values.

Purpose: Useful when precision is required for numbers with decimal places, but not a fixed number of decimal digits.
Example:

CREATE TABLE Measurements (
    MeasurementID INT,
    Value FLOAT,
    -- Other columns...
);

DATETIME:

The DATETIME datatype combines date and time information.

Purpose: Suitable for scenarios requiring both date and time details, like logging events.
Example:

CREATE TABLE LogEntries (
    LogID INT,
    EventDescription VARCHAR(255),
    LogTimestamp DATETIME,
    -- Other columns...
);

Other Specialized Datatypes

BLOB (Binary Large Object)

The BLOB datatype in SQL is designed for handling binary data, such as images, audio files, or any other binary large objects.

Purpose: Ideal for storing and managing large binary files.
Example:

CREATE TABLE Images (
    ImageID INT,
    ImageData BLOB,
    -- Other columns...
);

CLOB (Character Large Object)

The CLOB datatype is specifically used for handling large character data.

Purpose: Suited for storing extensive text data, such as lengthy documents or articles.

Example:

CREATE TABLE Documents (
    DocumentID INT,
    DocumentText CLOB,
    -- Other columns...
);

All the Datatypes of SQL

Datatype	Description
Numeric Types
INT	Integer values
SMALLINT	Small integer values
BIGINT	Large integer values
TINYINT	Tiny integer values
DECIMAL(p, s)	Fixed-point decimal numbers
NUMERIC(p, s)	Numeric values with precision and scale
FLOAT§	Floating-point numbers (single precision)
DOUBLE§	Floating-point numbers (double precision)
Character Types
CHAR(n)	Fixed-length character string
VARCHAR(n)	Variable-length character string
TEXT	Variable-length character string with large maximum length
Date and Time Types
DATE	Date values
TIME	Time values
DATETIME	Combination of date and time values
TIMESTAMP	Date and time, including fractional seconds
Boolean Type
BOOLEAN	Represents true or false values
Binary Large Objects
BLOB	Binary Large Object, for storing binary data (e.g., images)
VARBINARY(n)	Variable-length binary data
Character Large Objects
CLOB	Character Large Object, for storing large text data
Interval Types
INTERVAL	Represents a time span or duration
Enumerated Types
ENUM	Represents a set of predefined values
JSON Types
JSON	Represents JSON data
UUID Type
UUID	Universally Unique Identifier
XML Type
XML	Represents XML data
Geospatial Types
GEOMETRY	Represents geometric objects
GEOGRAPHY	Represents geographical objects