Intro

Data types define the characteristics of the data that can be stored in a table’s column. It defines the possible set of values that are accepted — this allows for data within the column to be stored in a consistent and understood format. By knowing the data type within a column, it allows users to know which calculations and formulations can be applied to values within the column. Furthermore, it allows the database to sort the data in a way that is logical, increasing efficiency.

Data types affect storage and performance.

Different data types require different space allotments — some take up a lot of space, others not so much. This space allotment directly impacts the processing speed of the database — by appropriately defining the type of data used, the less time the database has to infer values or convert them, effectively increasing performance value.

For smaller databases, this may not seem too important, however, when dealing with larger databases with thousands of users, these small degrees of difference summate to large performance losses or gains.

The process of ‘right-sizing’ goes further into this concept and aims to appropriately set the data type for a given field based on business questions and the organization’s future direction — essentially answering the question: is this data type the appropriate type for the needs of this application/business? In order to provide the right answer, we need to know what datatypes are available and their unique characteristics.

String Data Types

CHAR (byte size) — Character Data Type

Example:

CHAR(5) → will set whatever value is placed within the field to a size of 5 bytes, even if the value doesn’t require the full 5 bytes. Conversely, it will only allow values that require 5 bytes or less.

‘James’ → has a length of 5 and therefore takes up 5 bytes of size and will be accepted as a field value.

‘Bob’ → has a length of 3 and requires 3 bytes of size, however it will be set to take up 5 bytes of size.

‘Timothy’ → has a length of 7 and exceeds the 5 bytes of size allotted to the field and therefore will not be stored.

  • CHAR can store a maximum value of 255 bytes and tends to be faster than VARCHAR, but less responsive.

VARCHAR (byte size) — Variable Character Data Type

Example:

VARCHAR (5) → will set a maximum byte size to 5 bytes and will accept any value within that limit.

‘Angie’ → has a size of 5 bytes and will be accepted as a field value taking up 5 bytes of space.

‘Beth’ → has a size of 4 bytes and will be accepted as a field value taking up 4 bytes of space.

‘Serena’ → has a size of 6 bytes and will not be accepted as a field.

  • VARCHAR can store a maximum value of 65,535 bytes and tends to be more responsive to data insertion but slower on retrieval.

NVARCHAR (byte size) — Variable Character Data Type for UNICODE

ENUM (pre-set val-1, pre-set val-2, … pre-set val-n) — Enumerate Data Type

Example:

Lets say a user is filling out a gender column and can opt for ‘male’, ‘female’ or ‘neutral’.

ENUM (‘M’, ‘F’, ‘N’) → will only allow for “M’, ‘F’, ’N’ as possible values. Any other value will throw an error.

Integer Data Types

Signed: implies negative values are included.

Unsigned: implies that negative values are excluded.

*queries are default set to signed

*We’ll get to decimals in the next section on Fixed and Floating-point Datatypes.

TINYINT

  • Signed range: -128 to 127
  • Unsigned range: 0 to 255

SMALLINT

  • Signed range: -32,768 to 32,767
  • Unsigned range: 0 to 65,535

MEDIUMINT

  • Signed range: -8,388,608 to 8,388,607
  • Unsigned range: 0 to 16,777,215

INT

  • Signed range: -2,147,483,648 to 8,388,607
  • Unsigned range: 0 to 16,777,215

BIGINT

  • Signed range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • Unsigned range: 0 to 18,446,744,073,709,551,615

Fixed and Floating-point Data Types

Precision: refers to the number of digits within the number (including after the decimal).

Scale: refers to the number of digits within the number after the decimal.

DECIMAL (precision, scale) or NUMERIC (precision, scale)

Example:

DECIMAL (5,3) → Entered Value: 10.5 → will return → 10.500

  • Since precision is set to 5, the total number of digits must amount to 5, and similarly since scale is set to 3, two zeros are added to the end of the entered value in order to fulfill this condition.

DECIMAL (5,3) → Entered Value: 10.57869 → will return → 10.579

  • In this instance, the entered value exceeds the allotted precision and scale, therefore the value will be rounded to a number that fits both conditions.

*when only a single value is entered in the parenthesis, it will treat the scale value as zero → DECIMAL (5) = DECIMAL (5,0)

FLOAT (precision, scale) and DOUBLE (precision, scale)

FLOAT:

  • Precision: single (based off size)
  • Maximum # of digits: 23

DOUBLE :

  • Precision: double (based off size)
  • Maximum # of digits: 53

Time Data Types

  • Used to represent a date in the format YYY-MM-DD
  • Time is not included in this representation
  • Range of DATE:
  • 1st of January 1000–31st of December 9999

DATETIME

  • Used to represent date + time
  • YYY-MM-DD HH:MM:SS
  • These data points represent date + time precisely

TIMESTAMP

  • Used for a well-defined, exact point in time
  • Range of TIMESTAMP:
  • 1st of January 1970 UTC — 19th of January 2038, 3:14:07 UTC (Coordinated Universal Time)
  • Timestamp records the moment in time as the number of seconds passed after the 1st of January 1970 00:00
  • Representing a moment in time as a number allows you to easily obtain the difference between two TIMESTAMP values
  • So if you have a start time and end time, you can find the difference to find the duration of the event
  • TIMESTAMP is appropriate if you need to handle time zones

*Time data types must be writing within quotes “”

Special Mention: BLOB

The BLOB data type is very helpful for saving files in your database, to be used later on in your application, like a profile picture or music file.

Such files include:

  • .doc
  • .xlsx
  • .XML
  • .jpg
  • .wav

All these data types might seem like a jumble of ‘things’ but the sooner you start to use them within your applications, the sooner you’ll start to see their role within the design of your database and the efficiency/capability of your backend as a whole. Hope this overview of some common SQL data types helps you out in your next app and growth.

Just another one of those dreamers with a sparkle in his eyes.