SQL Data Types: A Comprehensive Guide

SQL Data Types

Data types in SQL define the kind of data that a column in a database table can store. They are essential for organizing data properly and maintaining data integrity. For example, when a column needs to store textual information, an appropriate string data type can be assigned to 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 for popular database systems, including MySQL, SQL Server, and Oracle.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

MySQL Data Types

MySQL provides a wide range of data types for handling different kinds of data. The following information is based on MySQL 8.0.

MySQL String Data Types

• CHAR(size): Fixed-length strings that can hold up to 255 characters. The 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: Stores strings up to 65,535 bytes. Its variants include:
  • TINYTEXT: Stores up to 255 characters.
  • MEDIUMTEXT: Stores up to 16,777,215 characters.
  • LONGTEXT: Stores up to 4,294,967,295 characters.
• ENUM(val1, val2, …): Stores one value from a predefined list, with a maximum of 65,535 values.
• SET(val1, val2, …): Stores multiple values from a predefined list, with up to 64 options.

MySQL Numeric Data Types

• BIT(size): A bit-value type used for storing bit values.
• INT(size)/INTEGER(size): Standard integer type. The signed range is -2,147,483,648 to 2,147,483,647.
• FLOAT(size, d): Stores floating-point numbers with customizable precision.
• DOUBLE(size, d): Stores floating-point numbers with greater precision.
• DECIMAL(size, d): Stores fixed-point numbers with a maximum precision of 65 digits and a scale of up to 30.
• BOOL: Represents Boolean values such as true or false.

MySQL Date and Time Data Types

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

SQL Server Data Types

SQL Server provides a broad range of data types designed to support different application and database requirements.

SQL Server String Data Types

• char(n): Fixed-width strings with a maximum length of 8,000 characters.
• varchar(n): Variable-width strings with a maximum length of 8,000 characters.
• varchar(max): Variable-width strings supporting large text values.
• nchar: Fixed-width Unicode strings with a maximum length of 4,000 characters.
• nvarchar: Variable-width Unicode strings with a maximum length of 4,000 characters.
• text: A legacy data type used for storing large text blocks.

SQL Server Numeric Data Types

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

SQL Server Date and Time Data Types

• datetime: Stores date and time values with an accuracy of approximately 3.33 milliseconds.
• datetime2: Provides extended date and time precision, accurate to 100 nanoseconds.
• date: Stores date values without a time component.
• time: Stores time values with 100-nanosecond accuracy.

Oracle Data Types

Oracle provides a wide range of data types to support different data storage requirements.

Oracle String Data Types

• CHAR(size): Fixed-length strings supporting up to 2,000 bytes.
• VARCHAR2(size): Variable-length strings supporting 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): Supports precision up to 38 digits, with a scale ranging from -84 to 127.
• FLOAT(p): Floating-point numbers with precision up to 126.
• BINARY_FLOAT/BINARY_DOUBLE: Floating-point data types that use binary precision.

Oracle Date and Time Data Types

• DATE: Stores date and time values.
• TIMESTAMP: Stores date and time values with extended precision and optional time zone support.

Oracle Large Object Data Types (LOB Types)

• BLOB: Stores binary large objects.
• CLOB/NCLOB: Stores large character objects.
• BFILE: Stores binary data externally.

Choosing the Right Data Type

Choosing the appropriate data type is important for efficient storage, query performance, and data consistency. Consider the following factors when selecting a data type:

• The nature of the data, such as textual, numerical, or temporal information.
• Storage requirements and performance.
• The features and limitations of the database system being used.

Download New Real Time Projects :- 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