SQL Data Types

What are SQL Data Types?

SQL data types define the kind of data that can be stored in a column of a database table. Each data type has specific storage requirements and range of values. Choosing the correct data type is essential for optimizing storage, performance, and data integrity.

Categories of SQL Data Types

1. Numeric Data Types

These data types are used for storing numerical values.

• INT: A standard integer (e.g., INT, INTEGER). Range: -2,147,483,648 to 2,147,483,647.
• SMALLINT: A smaller range integer (e.g., SMALLINT). Range: -32,768 to 32,767.
• TINYINT: Very small integer (e.g., TINYINT). Range: 0 to 255.
• FLOAT: A floating-point number with approximate precision (e.g., FLOAT, REAL). Ideal for storing decimal values.
• DOUBLE: A double-precision floating-point number (e.g., DOUBLE, DOUBLE PRECISION).
• DECIMAL: A fixed-point number with a specified precision and scale (e.g., DECIMAL(10,2) for 10 total digits with 2 after the decimal point).

2. String Data Types

These data types are used for storing textual data.

• CHAR: A fixed-length string (e.g., CHAR(10) stores 10 characters). If fewer characters are entered, the remaining space is padded with spaces.
• VARCHAR: A variable-length string (e.g., VARCHAR(255) can store up to 255 characters without padding).
• TINYTEXT: A very small string, maximum length of 255 characters.
• TEXT: A string with a maximum length of 65,535 characters.
• MEDIUMTEXT: A string with a maximum length of 16,777,215 characters.
• LONGTEXT: A string with a maximum length of 4,294,967,295 characters.
• ENUM: A string object with a value chosen from a list of allowed values (e.g., ENUM('small', 'medium', 'large')).

3. Date and Time Data Types

These data types are used for storing dates and times.

• DATE: Stores a date in the format 'YYYY-MM-DD' (e.g., 2024-08-13).
• TIME: Stores a time in the format 'HH:MM:SS' (e.g., 12:30:00).
• DATETIME: Combines date and time (e.g., DATETIME format is 'YYYY-MM-DD HH:MM:SS').
• TIMESTAMP: A timestamp that represents the number of seconds since the Unix Epoch (e.g., TIMESTAMP records date and time automatically when a row is created or updated).
• YEAR: Stores a year in a two-digit or four-digit format (e.g., YEAR(4)).

4. Spatial Data Types

These data types are used for storing geographical and spatial data.

• GEOMETRY: Stores geometric data.
• POINT: Represents a single point in 2D space.
• LINESTRING: Represents a curve consisting of a linear path.
• POLYGON: Represents a polygon.

Examples of Using Data Types

CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    UserName VARCHAR(50) NOT NULL,
    UserEmail VARCHAR(100) UNIQUE,
    UserAge TINYINT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

Conclusion

Understanding SQL data types is crucial for designing efficient databases. Choosing the right data type ensures optimal storage, performance, and data integrity. Always consider the nature of the data you are storing when selecting data types for your tables.