Optimize Cannot Delete: Data Types in SQL

Today, SQL is a universal tool for working with databases and a must-have not only for analysts and developers but also for a wide range of professions within and outside of IT.

Understanding data types is a fundamental aspect of working with databases. Data types can vary depending on the DBMS used, so in this article, we’ll cover the most common types in PostgreSQL, MySQL, SQL Server, SQLite, and Oracle, along with their features and applications.

Why do we need data types at all?

Data types, which define what values ​​can be stored in specific columns of a table, help us:

• Optimize data storage. Using the appropriate data type helps save database space.
• Ensure data integrity. Setting appropriate data types helps prevent input errors and ensures data conforms to the expected format.
• Speed ​​up queries. SQL servers optimize query execution based on data types, which can significantly improve performance.

So data types make working with databases more efficient, but how do you know which data type to choose?

What are the types of data?

The most popular data types in SQL can be roughly divided into five main categories:

1. Numeric data types
2. String data types
3. Date and time
4. Logical data types
5. Binary data types

Some DBMSs also use other data type categories, but they’re typically used less frequently or at a more advanced level. Be sure to consult your DBMS’s documentation for details. Now let’s look at each category separately.

Numeric data types

They are used to store numbers and allow for arithmetic operations, comparisons, and other mathematical calculations. Numeric data types can be divided into the following main categories:

1. Integer data types

Used to store numbers without decimal fractions. They vary in the range of values ​​they can hold and the amount of memory they occupy.

• TINYINT
  • The range of values ​is -128 to 127 for signed values ​​and 0 to 255 for unsigned values.
  • Size: 1 byte.
  • Application: Often used to store small numbers such as age.
  • DBMS: MySQL, SQL Server.
• SMALLINT
  • The range of values ​​is -32,768 to 32,767 for signed values ​​and 0 to 65,535 for unsigned values.
  • Size: 2 bytes.
  • Usage: Suitable for data that requires a range larger than TINYINT, but not so large that INT is needed.
  • DBMS: PostgreSQL, MySQL, SQL Server.
• MEDIUMINT
  • The range of values ​​is -8,388,608 to 8,388,607 for signed values ​​and 0 to 16,777,215 for unsigned values.
  • Size: 3 bytes.
  • Application: Used in situations where it is necessary to balance the amount of memory and the range of values.
  • DBMS: MySQL.
• INT (or INTEGER)
  • The range of values ​​is -2,147,483,648 to 2,147,483,647 for signed values ​​and 0 to 4,294,967,295 for unsigned values.
  • Size: 4 bytes.
  • Usage: One of the most commonly used data types, suitable for most integer values.
  • DBMS: PostgreSQL, MySQL, SQL Server, SQLite.
• BIGINT
  • The range of values ​​is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 for signed values ​​and 0 to 18,446,744,073,709,551,615 for unsigned values.
  • Size: 8 bytes.
  • Usage: Used to store very large numbers, such as unique identifiers or object counts in large systems.
  • DBMS: PostgreSQL, MySQL, SQL Server.

2. Numbers with fixed precision and scale

These data types are used to store numbers with decimal fractions where precision is important, such as when dealing with money.

• DECIMAL(p, s) or NUMERIC(p, s): A number with a fixed precision and scale, where p (precision) is the total number of digits and s (scale) is the number of digits after the decimal point. For example, DECIMAL(10, 2) can store numbers with up to 10 digits, of which 2 are after the decimal point.
  • Size: This data type takes up a variable number of bytes depending on the value of the p parameter.
  • Application: Often used in financial applications where high accuracy in calculations is required (e.g., prices, account balances).
  • DBMS: PostgreSQL, MySQL, SQL Server, SQLite.

3. Floating-point numbers

Floating-point data types are designed to store numbers that can have a wide range of values ​​and require some trade-off between precision and storage space.

• FLOAT
  • Description: FLOAT represents floating-point numbers that can store values ​​with a fractional part. Depending on the DBMS implementation, FLOAT may vary in precision and range of values. In some DBMSs, the precision of FLOAT can be specified using the p parameter – FLOAT(p), where p specifies the number of digits in the mantissa. For example, FLOAT(7) can store up to 7 significant digits.
  • REAL: In some DBMSs, FLOAT may be synonymous with REAL, which typically has a fixed precision and range.
  • Size: 4 or 8 bytes depending on the DBMS.
  • Application: Used when it is necessary to store numbers with a high degree of precision, such as the results of scientific calculations.
  • DBMS: PostgreSQL(Real), MySQL, SQL Server, SQLite (Real), Oracle.
• DOUBLE PRECISION or simply DOUBLE
  • Description: A floating-point number, similar to FLOAT, but with double precision.
  • Size: 8 bytes.
  • Usage: Suitable for storing numbers that require more precision than FLOAT, but with the same trade-off in rounding accuracy.
  • DBMS: PostgreSQL, MySQL, Oracle.

String data types

String data types in SQL are designed to store textual information such as names, addresses, descriptions, and any other characters.

• CHAR(n)
  • Description: The CHAR(n) data type is used to store fixed-length strings. Here, n is the number of characters to be allocated to store the string.
  • Features: If the string is shorter than the specified length, it is automatically padded with spaces to the specified length. For example, if CHAR(5) is specified and the string “SQL” is 3 characters long, it will be stored as “SQL ” (with two spaces at the end).
  • Usage: CHAR is used to store data that is always the same length, such as country codes, postal codes, or fixed-length phone numbers.
  • DBMS: PostgreSQL, MySQL, SQL Server, Oracle.
• VARCHAR(n)
  • Description: The VARCHAR(n) data type is used to store variable-length strings, where n is the maximum number of characters that can be stored.
  • Features: Unlike CHAR, VARCHAR stores only the actual number of characters in a string and does not pad it with spaces. For example, the string “SQL” in VARCHAR(5) will occupy exactly 3 characters, without padding.
  • Usage: VARCHAR is widely used to store data that can vary in length, such as names, email addresses, addresses, and descriptions.
  • DBMS: PostgreSQL, MySQL, SQL Server, Oracle.
• TEXT
  • Description: TEXT is used to store long text data such as articles, descriptions, comments, or large blocks of text.
  • Features: Unlike CHAR and VARCHAR, the TEXT type can store very long strings (usually up to several gigabytes). However, it may have some limitations depending on the DBMS.
  • Application: Used to store large amounts of text, such as product descriptions, blog articles, user comments, etc.
  • DBMS: PostgreSQL, MySQL, SQL Server, SQLite.

Date and time

SQL date and time data types are designed to handle values ​​such as dates, times of day, and time intervals. These data types enable date and time operations such as addition, subtraction, comparison, and formatting. Let’s look at the main types of these data, their features, and applications.

• • DATE
    • Description: DATE is a data type used to store dates in the YYYY-MM-DD (year, month, day) format. It stores only date information, not time.
    • Example: 2024-08-15
    • Usage: The DATE data type is used when you need to store only a date without time. For example, this could be a date of birth, a user registration date, or the date an action was performed.
    • DBMS: PostgreSQL, MySQL, SQL Server, Oracle.
  • TIME
    • Description: TIME is a data type used to store the time of day in the HH:MM:SS (hours, minutes, seconds) format. This data type can include fractions of a second (microseconds).
    • Example: 14:30:00 or 14:30:00.123456 (with fractional seconds)
    • Usage: The TIME data type is used to store time without reference to a specific date. This can be useful for recording the start time of a workday, meeting times, or schedules.
    • DBMS: PostgreSQL, MySQL, SQL Server.
    • DATETIME

Description: DATETIME is a data type that combines date and time. The storage format is YYYY-MM-DD HH:MM:SS. In some DBMSs, this may include fractions of a second.

• • • • Example: 2024-08-15 14:30:00
      • Usage: DATETIME is used when both date and time need to be stored together. This could be the date and time of a transaction, record creation, or data modification.
      • DBMS: MySQL, SQL Server.
    • TIMESTAMP
      • Description: TIMESTAMP is a data type that also stores date and time, but with a time zone. It is automatically updated whenever a record is modified, making it useful for tracking data changes. The storage format is similar to DATETIME.
      • Example: 2024-08-15 14:30:00
      • Usage: TIMESTAMP is used for automatic logging and tracking of changes to data, for example, when it is necessary to know the exact time a record was last updated.
      • DBMS: PostgreSQL, MySQL, Oracle.
    • INTERVAL
      • Description: INTERVAL is a data type used to store time intervals, such as the difference between two dates or timestamps. Depending on the SQL implementation, intervals can include days, months, years, hours, minutes, and seconds.
      • Example: INTERVAL ‘2 days 3 hours’
      • Usage: The INTERVAL data type is used to calculate the difference between two time values, for example, to count the number of days between two dates or the time elapsed since a certain action was performed.
      • DBMS: PostgreSQL, Oracle.

Split-second support

Many data types, such as TIME, DATETIME, and TIMESTAMP, support fractional-second (microsecond) storage, allowing for more precise timestamps to be stored and manipulated. For example, the TIMESTAMP(6) format allows for storing timestamps with millisecond precision.

Working with time zones

Some DBMSs support time zones for TIMESTAMP WITH TIME ZONE data types, allowing you to store dates and times with respect to the time zone. This is useful for global applications where it’s important to record the time of an event with respect to the time zone.

Logical data types

Logical data types in SQL are used to store Boolean values, that is, values ​​that can be either true (TRUE) or false (FALSE). These data types are often used in query conditions, validations, and data filtering. Although SQL does not define a logical data type as a separate type, most modern DBMSs support them or similar constructs.

• • • BOOLEAN (BOOL)
      • Description: BOOLEAN is a data type designed to store logical values. A BOOLEAN value can be TRUE, FALSE, or NULL (undefined). In some DBMSs, the BOOLEAN data type is abbreviated as BOOL.
      • Usage: Boolean data types are used to store flags or states, such as:
        • Is the user active? (is_active)
        • Was the task completed successfully? (is_completed)
        • Is the action confirmed? (is_confirmed)

Support for logical types in various DBMSs

In PostgreSQL, the BOOLEAN data type is fully supported and can take the values ​​TRUE, FALSE, or NULL. Alternative notations are `t` or `f`, `1` or `0` (where `1` is interpreted as TRUE and `0` as FALSE).

In MySQL, the BOOLEAN data type is an alias for TINYINT(1). The value 0 is interpreted as FALSE, and any non-zero value is interpreted as TRUE.

In SQLite, the boolean data type is also represented as INTEGER, where 0 means FALSE and 1 means TRUE.

In Microsoft SQL Server, the logical data type is represented as BIT. It can take the values ​​0 (FALSE), 1 (TRUE), or NULL.

Binary data types

Binary data types in SQL are designed to store data as raw bytes or bits, making them useful for storing images, files, cryptographic keys, and other types of binary data. They allow you to store and manage data that cannot be represented as plain text or numbers.

• • • BINARY
      • Description: BINARY(n) is a data type used to store a fixed number of bytes. The value must have an exact length equal to n, and if the input value is shorter, it is padded with zeros to reach the specified length.
      • Example: BINARY(5) to store 101010 will store it as 10101000 (padding zeros until it reaches 5 bytes).
      • Usage: BINARY is used when you need to store fixed-length binary data, such as encrypted passwords, identifiers, or hashes.
      • DBMS: MySQL, SQL Server.
    • BARBINARY
      • Description: VARBINARY(n) is a data type used to store variable-length binary data, where n is the maximum number of bytes that can be stored. Unlike BINARY, this data type stores only the actual number of bytes, and the length can vary.
      • Example: VARBINARY(5) to store 101010 will store exactly 101010, with no leading zeros.
      • Usage: VARBINARY is used to store variable-length data such as images, files, documents, media, or any other binary data whose length can vary.
      • DBMS: MySQL, SQL Server.
    • BLOB (Binary Large Object)
      • Description: BLOB is a data type used to store large amounts of binary data, such as images, audio, and video files, documents, and other file types. Depending on the DBMS, BLOBs can be divided into several categories, such as TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, which differ in the maximum amount of data they can store.
      • Application: BLOB is used to store large amounts of data such as photographs, audio and video recordings, documents, and other types of files that cannot be stored in text or numeric fields.
      • DBMS: MySQL, SQLite, Oracle.

Conclusion

We hope this makes it easier for you to navigate data types! Understanding them in SQL is crucial for efficient database use, maintaining data integrity, and improving query performance. Don’t forget to consult the documentation to understand exactly which data types your specific DBMS supports.