SQL data types define the type of data that a column can store in a database. When creating a table, each column must have a defined data type, which helps ensure that the data stored in that column is consistent and accurate. The available data types can vary slightly depending on the specific SQL database management system (DBMS) you are using (e.g., MySQL, PostgreSQL, SQL Server, Oracle), but the following are the common SQL data types.

1. Numeric Data Types

Numeric data types are used to store numbers, both integers and floating-point values.

• INT / INTEGER: Used to store integer values (whole numbers).

  • Example: INT, INTEGER
  • Range: Varies by DBMS (e.g., -2,147,483,648 to 2,147,483,647 in MySQL).

• TINYINT: Stores very small integer values.

  • Example: TINYINT
  • Range: -128 to 127 (signed), or 0 to 255 (unsigned).

• SMALLINT: Stores small integer values.

  • Example: SMALLINT
  • Range: -32,768 to 32,767 (signed).

• BIGINT: Stores large integer values.

  • Example: BIGINT
  • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

• DECIMAL / NUMERIC: Used for fixed-point numbers with a specified precision and scale. Precision determines the total number of digits, and scale determines the number of digits to the right of the decimal point.

  • Example: DECIMAL(10, 2) or NUMERIC(10, 2)
  • Meaning: 10 total digits with 2 digits after the decimal point (e.g., 12345678.90).

• FLOAT: Used for floating-point numbers, which are approximate numbers.

  • Example: FLOAT(10, 2)
  • Range: Depends on the DBMS, typically for scientific or engineering calculations.

• DOUBLE: Stores double-precision floating-point numbers, similar to FLOAT but with higher precision.

  • Example: DOUBLE

2. Character String Data Types

Character string data types store text and string values.

• CHAR: Stores fixed-length strings. If the string is shorter than the specified length, it will be padded with spaces.

  • Example: CHAR(10)
  • Fixed length of 10 characters, e.g., abcdef (padded with spaces).

• VARCHAR: Stores variable-length strings. The length can vary up to a specified limit, and no padding is added if the string is shorter.

  • Example: VARCHAR(255)
  • Can store up to 255 characters.

• TEXT: Stores long text strings. The maximum length can be very large, depending on the DBMS.

  • Example: TEXT
  • Typically used for storing large amounts of text (e.g., blog posts or descriptions).

• CLOB (Character Large Object): Similar to TEXT, used for very large text data. Typically used when the size of text exceeds the storage limits of the TEXT type.

  • Example: CLOB

3. Date and Time Data Types

These data types are used to store date and time-related information.

• DATE: Stores a date (year, month, day) without a time.

  • Example: DATE
  • Format: YYYY-MM-DD (e.g., 2024-12-15).

• TIME: Stores a time (hours, minutes, seconds) without a date.

  • Example: TIME
  • Format: HH:MM:SS (e.g., 14:30:00).

• DATETIME: Stores both a date and a time.

  • Example: DATETIME
  • Format: YYYY-MM-DD HH:MM:SS (e.g., 2024-12-15 14:30:00).

• TIMESTAMP: Similar to DATETIME, but typically includes timezone information and may automatically update when records are created or modified.

  • Example: TIMESTAMP
  • Format: YYYY-MM-DD HH:MM:SS (e.g., 2024-12-15 14:30:00).

• YEAR: Stores a year in a 4-digit format.

  • Example: YEAR
  • Format: YYYY (e.g., 2024).

4. Binary Data Types

These data types store binary data, such as images, files, or any other non-textual information.

• BINARY: Stores fixed-length binary data.

  • Example: BINARY(16)
  • Fixed-length binary data of 16 bytes.

• VARBINARY: Stores variable-length binary data.

  • Example: VARBINARY(255)
  • Variable-length binary data up to 255 bytes.

• BLOB (Binary Large Object): Used to store large binary objects, such as images or multimedia files.

  • Example: BLOB
  • Typically used for large binary data.

5. Boolean Data Type

• BOOLEAN: Stores boolean values, representing true/false or 1/0.
  • Example: BOOLEAN
  • Values: TRUE or FALSE, or 1 (true) and 0 (false).

6. Other Specialized Data Types

These are additional types for specific use cases.

• ENUM: A string object with a predefined set of values. The value stored must be one of the defined options.

  • Example: ENUM('small', 'medium', 'large')
  • Used for storing a limited set of values (e.g., product sizes or colors).

• SET: Similar to ENUM, but allows multiple values to be selected from the predefined set.

  • Example: SET('red', 'green', 'blue')
  • Useful for storing multiple options or tags.

• UUID (Universally Unique Identifier): Stores a 128-bit unique identifier.

  • Example: UUID
  • Used to store globally unique identifiers.

7. JSON Data Type

Many modern databases (e.g., PostgreSQL, MySQL, and SQL Server) support storing JSON (JavaScript Object Notation) data directly in a column. It is used for storing semi-structured data, typically used for NoSQL-like functionality within a relational database.

• JSON: Stores JSON-formatted data.
  • Example: JSON
  • Can store objects and arrays in the format {"key": "value"}.