SQL Data Types: A Comprehensive Guide

SQL Data Types

Data types in SQL define the kind of data a column in a database table can hold. They are fundamental to structuring data effectively and ensuring its integrity. For instance, if you want a table column to store textual data, you’ll declare a string data type for it. SQL data types are primarily categorized into three groups:

• String Data Types
• Numeric Data Types
• Date and Time Data Types

Below, we explore these categories with details specific to popular database systems: MySQL, SQL Server, and Oracle.

Complete Python Course with Advance topics:-Click here

MySQL Data Types

MySQL provides a wide array of data types to handle various data formats. The examples here are based on MySQL 8.0.

MySQL String Data Types

• CHAR(size): Fixed-length strings, holding up to 255 characters. Default size is 1.
• VARCHAR(size): Variable-length strings, supporting up to 65,535 characters.
• BINARY(size): Fixed-length binary byte strings, similar to CHAR.
• VARBINARY(size): Variable-length binary byte strings, similar to VARCHAR.
• TEXT: Holds strings up to 65,535 bytes. Variants include:
  • TINYTEXT (up to 255 characters)
  • MEDIUMTEXT (up to 16,777,215 characters)
  • LONGTEXT (up to 4,294,967,295 characters).
• ENUM(val1, val2, …): Allows storing a single value from a predefined list. Maximum of 65,535 values.
• SET(val1, val2, …): Allows storing multiple values from a predefined list, with up to 64 options.

MySQL Numeric Data Types

• BIT(size): A bit-value type, storing 1–64 bits.
• INT(size)/INTEGER(size): Standard integer type. Signed range: -2,147,483,648 to 2,147,483,647.
• FLOAT(size, d): Floating-point numbers with customizable precision.
• DOUBLE(size, d): Larger floating-point numbers with extended precision.
• DECIMAL(size, d): Fixed-point numbers with a maximum size of 65 and a precision of 30.
• BOOL: Boolean values (true or false).

MySQL Date and Time Data Types

• DATE: Stores dates in the format YYYY-MM-DD.
• DATETIME(fsp): Date and time combination (YYYY-MM-DD hh:mm:ss).
• TIMESTAMP(fsp): Stores timestamps, based on Unix epoch time.
• TIME(fsp): Stores time in hh:mm:ss format.
• YEAR: Stores four-digit year values (e.g., 1901 to 2155).

SQL Server Data Types

SQL Server supports a robust set of data types tailored for modern applications.

SQL Server String Data Types

• char(n): Fixed-width strings, up to 8,000 characters.
• varchar(n): Variable-width strings, up to 8,000 characters.
• varchar(max): Variable-width strings, up to 1,073,741,824 characters.
• nchar: Fixed-width Unicode strings, up to 4,000 characters.
• nvarchar: Variable-width Unicode strings, up to 4,000 characters.
• text: Legacy data type for large text blocks (up to 2GB).

SQL Server Numeric Data Types

• bit: Holds binary values (0, 1, or NULL).
• tinyint: Whole numbers from 0 to 255.
• smallint: Whole numbers from -32,768 to 32,767.
• int: Whole numbers from -2,147,483,648 to 2,147,483,647.
• bigint: Whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• float(n): Floating-point numbers with high precision.
• money: Stores monetary values with up to four decimal places.

SQL Server Date and Time Data Types

• datetime: Date-time combination with an accuracy of 3.33 milliseconds.
• datetime2: Extended precision for date-time, accurate to 100 nanoseconds.
• date: Date-only format.
• time: Time-only format with 100-nanosecond accuracy.

Oracle Data Types

Oracle offers a rich set of data types designed to handle a variety of data storage requirements.

Oracle String Data Types

• CHAR(size): Fixed-length strings, up to 2,000 bytes.
• VARCHAR2(size): Variable-length strings, up to 4,000 bytes.
• NCHAR(size): Fixed-length national character strings.
• NVARCHAR2(size): Variable-length Unicode strings.

Oracle Numeric Data Types

• NUMBER(p, s): Precision (p) up to 38 digits, scale (s) ranging from -84 to 127.
• FLOAT(p): Floating-point numbers, with precision up to 126.
• BINARY_FLOAT/BINARY_DOUBLE: Optimized floating-point numbers with binary precision.

Oracle Date and Time Data Types

• DATE: Date and time combination with a range from January 1, 4712 BC, to December 31, 9999 AD.
• TIMESTAMP: Extended date-time precision with optional time zones.

Oracle Large Object Data Types (LOB Types)

• BLOB: Binary large objects, up to 4GB.
• CLOB/NCLOB: Large character objects, supporting up to 4GB.
• BFILE: Binary data stored externally, up to 4GB.

Choosing the Right Data Type

Selecting the right data type is critical for efficient storage, faster queries, and ensuring data consistency. Consider the following factors:

• The nature of data (e.g., textual, numerical, or temporal).
• Storage size and performance.
• The database system’s specific features and limitations.

Download New Real Time Projects :-Click here
PHP PROJECT:- CLICK HERE

sql data types with examples
sql data types pdf
mysql data types
oracle sql data types
postgresql data types
data types in sql w3schools
sql data types with examples pdf
number data type in sql
sql data types in dbms
sql constraints