List of Data Types Available for Columns with SQL

List of Data Types Available for Columns

Character Types

These types are used to store text or character-based data.

CHAR(n)

• Description: Fixed-length character string.
• Storage: Always uses the specified length n. If the input string is shorter, it is padded with spaces.
• Example: CHAR(10) will store strings of exactly 10 characters.

VARCHAR(n)

• Description: Variable-length character string.
• Storage: Stores up to n characters. Only the required space for the string is used.
• Example: VARCHAR(50) can store up to 50 characters but only uses as much space as needed.

TEXT

• Description: Variable-length text data. Ideal for large text fields.
• Storage: Can store very large amounts of text data.
• Example: Used for storing long descriptions, articles, etc.

NCHAR(n) and NVARCHAR(n)

• Description: Unicode character types for storing international characters.
• Storage: Uses two bytes per character for Unicode. NCHAR is fixed length, while NVARCHAR is variable length.
• Example: NVARCHAR(100) can store up to 100 Unicode characters.

Numeric Types

These types are used for storing numeric values.

INT (or INTEGER)

• Description: Standard integer type.
• Storage: Typically uses 4 bytes.
• Example: INT can store values from -2,147,483,648 to 2,147,483,647.

BIGINT

• Description: Larger integer type.
• Storage: Typically uses 8 bytes.
• Example: BIGINT can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

SMALLINT

• Description: Smaller integer type.
• Storage: Typically uses 2 bytes.
• Example: SMALLINT can store values from -32,768 to 32,767.

TINYINT

• Description: Very small integer type.
• Storage: Typically uses 1 byte.
• Example: TINYINT can store values from 0 to 255 (or -128 to 127, depending on the DBMS).

FLOAT

• Description: Single-precision floating-point number.
• Storage: Typically uses 4 bytes.
• Example: Can store numbers with decimal points, but with limited precision.

DOUBLE

• Description: Double-precision floating-point number.
• Storage: Typically uses 8 bytes.
• Example: Offers greater precision than FLOAT.

DECIMAL(p, s)

• Description: Fixed-point number with precision p and scale s.
• Storage: Precision and scale determine the storage space.
• Example: DECIMAL(10, 2) can store numbers with up to 10 digits, 2 of which are after the decimal point.

Date and Time Types

These types are used for storing date and time values.

DATE

• Description: Stores date values.
• Storage: Typically uses 4 bytes.
• Example: Format: YYYY-MM-DD.

TIME

• Description: Stores time values.
• Storage: Typically uses 3 to 6 bytes.
• Example: Format: HH:MM:SS.

DATETIME

• Description: Stores date and time values.
• Storage: Typically uses 8 bytes.
• Example: Format: YYYY-MM-DD HH:MM:SS.

TIMESTAMP

• Description: Stores date and time values, often used for tracking changes.
• Storage: Typically uses 4 to 8 bytes.
• Example: Format: YYYY-MM-DD HH:MM:SS (may also include fractional seconds).

INTERVAL

• Description: Stores a period of time.
• Storage: Varies by DBMS.
• Example: Used for storing durations such as 1 year 2 months 3 days.

Large Object Types

These types are used for storing large amounts of binary or text data.

BLOB (Binary Large Object)

• Description: Stores large binary data, such as images, audio, or video.
• Storage: Can store very large objects, often with sizes up to several gigabytes.
• Example: Suitable for storing file contents.

CLOB (Character Large Object)

• Description: Stores large amounts of text data.
• Storage: Can store very large text data, often with sizes up to several gigabytes.
• Example: Suitable for storing large documents.

Boolean Type

BOOLEAN

• Description: Stores TRUE or FALSE values.
• Storage: Typically uses 1 byte or 1 bit.
• Example: Useful for flags or binary states.

Special Types

Some DBMSs have special types for specific needs:

UUID (Universally Unique Identifier)

• Description: Stores a unique identifier.
• Storage: Typically uses 16 bytes.
• Example: Used for generating unique keys.

JSON / JSONB

• Description: Stores JSON data (the JSONB type is a binary format in some DBMSs).
• Storage: Varies by DBMS.
• Example: Used for storing and querying JSON documents.

Best Practices

• Choose the Right Data Type: Select the most appropriate data type for each column to balance performance, storage, and data integrity.
• Consider Precision and Scale: For numeric types, ensure the precision and scale meet the requirements of your application.
• Use Large Objects Wisely: Use BLOB and CLOB types for large data but be aware of performance implications.
• Optimize Storage: Use variable-length types like VARCHAR instead of fixed-length types like CHAR when the length of data varies.

Conclusion

Selecting the appropriate data types for your columns is crucial for efficient data storage and retrieval. By understanding the different data types available and their characteristics, you can design a database schema that best suits your application’s needs.