Code has been added to clipboard!

SQL Data Types With a Quick Reference to Different Databases

Reading time 3 min
Published Aug 9, 2017
Updated Oct 3, 2019

SQL data types make it easy to specify data in a particular column of the table more accurately. You can set a column to store only dates, only characters, etc.

The data type that you set enables SQL to identify how to interact with that part of data. Data types in SQL vary due to different database systems and slight differences in the syntax used. You should always check with the specific database documentation to see whether the data type you're planning to set is supported.

SQL Data Types: Main Tips

  • A data type describes the type of value to store in a column.
  • Each database column must have data type and name specified – this needs to be done before an SQL table can be created.
  • Data types help SQL interact with column data properly.
  • Different databases offer different data type definitions.
  • Data types might not act the same when used on different databases, regardless of their names being the same.

General Data Types

Data type Description
CHARACTER(n) Fixed-length n. Character string.
CHARACTER VARYING(n) or VARCHAR(n) Character string. Maximum length is n. Variable length.
BINARY(n) Fixed-length n. Binary string.
BOOLEAN Stores FALSE or TRUE values.
BINARY VARYING(n) or VARBINARY(n) Binary string. Maximum length n. Variable length.
INTEGER(p) Precision p. No decimal (integer numerica).
SMALLINT Precision 5. No decimal (integer numerica).
INTEGER Precision 10. No decimal (integer numerica).
BIGINT Precision 19. No decimal (integer numerica).
DECIMAL(p,s) Exact numerical, scale s., precision p, scale s. Example: decimal(6,2) is a number that has 4 digits before the decimal and 3 digits after the decimal.
NUMERIC(p,s) Same as DECIMAL. Exact numerical, scale s., precision p, scale s.
FLOAT(p) Approximate numerical, mantissa precision p. The size argument consists of the minimum precision number.
REAL Approximate numerical, mantissa precision 7.
FLOAT Approximate numerical, mantissa precision 16.
DOUBLE PRECISION Approximate numerical, mantissa precision 16.
DATE Stores day, month, and year values.
TIME Stores second, minute, and hour values.
TIMESTAMP Stores second, minute, hour, day, month, year values.
INTERVAL Composed of a number of integer fields, representing a period of time, depending on the interval type.
ARRAY An ordered collection of elements and a set-length.
MULTISET An unordered collection of elements and a variable-length.
XML XML data is storage.
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid certificates of completion
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

Quick Reference to SQL Data Types

In this table, you see some of the most popular data type names in different database platforms:

Data type Access SQLServer Oracle MySQL PostgreSQL
boolean Yes/No Bit Byte N/A Boolean
integer Number (integer) Int Number Int
Integer
Int
Integer
float Number (single) Float
Real
Number Float Numeric
currency Currency Money N/A N/A Money
string (fixed) N/A Char Char Char Char
string (variable) Text (<256)
Memo (65k+)
Varchar Varchar
Varchar2
Varchar Varchar
binary object OLE Object Memo Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)
Long
Raw
Blob
Text
Binary
Varbinary

SQL Data Types: Summary

  • Data types describe the type of data stored in a particular column.
  • Prior to creating an SQL table, you must specify each column data type and name.
  • Data types make it very easy to interact with data in certain columns.
  • There are different data type definitions for different databases.
  • Despite having identical names, data types might work differently on different databases.

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 03/03/2021
3176 People Used
Only 97 Left
Rating
5.0