1. sql
  2. /references
  3. /data-types

Data Types in SQL

Definition

SQL (Structured Query Language) is a programming language used to manage relational databases. SQL databases store data in tables, which consist of rows and columns. Each column in a table represents a specific type of data, such as a string, integer, or date, and is referred to as a "field" or "column."

Here are some common datatypes you might find in an SQL database:

  • INT or INTEGER: A whole number (no decimal point) with a range of values, typically between -2147483648 and 2147483647.
  • BIGINT: A larger whole number with a range of values, typically between -9223372036854775808 and 9223372036854775807.
  • FLOAT: A number with a decimal point, with a range of values depending on the implementation of SQL.
  • NUMERIC or DECIMAL: A fixed-point number with a decimal point, used to store values with a fixed number of decimal places.
  • CHAR or CHARACTER: A fixed-length character string, typically between 1 and 255 characters.
  • VARCHAR: A variable-length character string, with a maximum length specified by the field.
  • DATE: A date, typically in the format of YYYY-MM-DD
  • TIMESTAMP: A timestamp, which includes the date and time, typically in the format of YYYY-MM-DD HH:MI:SS

Some Database management systems also include additional data types for storing JSON data, for example MongoDB uses BSON, and MySQL has the JSON datatype

It's important to note that the specific data types and their allowed range of values can vary depending on the specific implementation of SQL that you're using, so it's always a good idea to consult the documentation for your specific database management system (DBMS) for more information.

Best Practices

Here are a few best practices for choosing data types in SQL:

  • Use the smallest data type that can accurately represent the data: For example, if a column only needs to store positive integers between 0 and 255, use the TINYINT data type instead of the INT data type.

  • Use the appropriate data type for the data: For example, use the DATE data type for date and time values and the DECIMAL data type for decimal numbers.

  • Use fixed-length data types instead of variable-length data types when possible: Fixed-length data types take up a fixed amount of space in the database, which can make the database more efficient and to take up less storage.

  • Consider using nullable columns for optional data: Allow NULL value for those column which are not necessary.

  • Use appropriate data types for joins and primary keys: When creating primary keys or joining tables, use appropriate data types such as integers for joining columns or primary keys

  • Consider using ENUM types for fields with limited set of acceptable values.

  • When converting a value to a variable length data type using VARCHAR, always specify the length.

  • If not necessary, try not to allow NULL values in a fixed-length column. NULL consumes the same space an input value would. Those NULL values will add up quickly in a large column.

  • If you must accommodate NULL values, use a variable-length column. They use less space for NULL.