SQL Data Types

In SQL, data types help to define the type of value that can be stored in a database cell, or, more specifically, in a column. There are many kinds of data types in SQL and in this database programming and administration tutorial, we will look at each one and learn their syntax, how to use them, when to use them, their use case, and how they differ in specific relation database vendors, such as MySQL, Oracle, PostgreSQL and MS SQL Server.

Before we begin, if you prefer to learn in a classroom or online environment, we have a list of the Top Online Courses to Learn SQL to help you get started.

What Data Types Does SQL Have?

Data types in SQL – and its offshoots – define the type, size, and range of information that can be stored in a database object. In SQL, objects that can have a data type include columns, expressions, parameters, and variables.

A column in a table runs up and down or vertically and consists of cells that hold data that are related to one another (hence SQL being a relational database language). A variable can be thought of as a box that hold data; a database programmer can look inside the box to see that data, replace it with another piece of data, or change the data – so long as it shares the same type. More on that in a moment.

An expression can be made up of one or more values, operators, and functions that a programmer can use to evaluate to obtain a piece of data. Think of it as a mathematic formula, though expressions do not only perform math or work on numeric values.

Parameters are used to facilitate the exchange of data in what are known as SQL functions and stored procedures.

SQL has three main types of data types, including built-in, user-defined alias, and user-defined common language runtime (CLR). These can be further broken down into categories like numeric, string, date and time, spatial, and miscellaneous. Within each of those categories, there are sub-categories as well, which we will discuss in-depth in the following section.

Read: SQL Best Practices

SQL Numeric Data Types

Numeric data types in SQL represent number values as either INT (integer or whole numbers) or FLOATS (decimal point or floating-point numbers). Within these two categories, there are subtypes as well that database programmers can use to represent versions of these two numeric data types. Here is a list of all numeric data type in SQL:

BIT: Used to represent numbers of a single byte, ranging from 0 – 1
TINYINT: Used to represent small integer values ranging from 0 – 255
SMALLINT: Used to store integer values ranging from -32,768 – 32,767
INT: Used to represent integer or whole number values ranging from -2,147,483,648 – 2,147,483,647
BIGINT: Used to represent integer values ranging from -9,223,372,036,854,775,808 – 9,223,372,036,854,775,807
DECIMAL: Used for decimal points numbers ranging from -10^38 + 1 – 10^38 -1
NUMERIC: Used for numbers ranging from -10^38 + 1 – 10^38 -1
FLOAT: Used for decimal numbers ranging from -1.79E+308 – 1.79E+308
REAL: Used for approximate floating-point numbers ranging from -3.40E + 38 – 3.40E + 38
SMALLMONEY: Used for monetary values ranging from -214,748.3648 – 214,748.3647. Specific to MS SQL Server
MONEY: Used for monetary values ranging from -922,337,203,685,477.5808 – 922,337,203,685,477.5807. Specific to MS SQL Server

SQL String and Character Data Types

Character and text data types in SQL consists of text-based values, which can include the upper and lowercase version of any letter ranging from a-z, A-Z, and special characters like !@#$%^&*(). SQL has two types of character and string data types: character and strings and Unicode characters and strings.

Without getting too deep into the subject (it is beyond the scope of this tutorial), Unicode is an encoding standard used to assign a unique numeric value to a letter, digit, or symbol, to ensure it works regardless of language or script being used. For instance, the Unicode for an uppercase letter A is U+0041, while the Unicode for a lowercase letter a is U+0061.

Below is a list of the Character and String data types in SQL:

CHAR: Used for fixed length text with a maximum length of 8,000 characters
VARCHAR: Used for variable-length text with a maximum length of 8,000 characters
VARCHAR(max): Used for variable-length text with provided max characters. Note that this is not supported in MySQL flavor of SQL
TEXT: Variable-length text values with a maximum size of 2GB worth of data

Here is a list of the different Unicode Character and String data types allowed in SQL. It should be noted that these data types are not supported in MySQL:

NCHAR: Used for fixed length text with a maximum length of 4,000 characters
NVARCHAR: Used for variable-length text with a maximum length of 4,000 characters
NVARCHAR(max): Used for variable-length storage with max characters provided
NTEXT: Used for variable-length text with a maximum storage capacity of 1GB

Learn more abut different flavors of SQL in our tutorials: What is MySQL and What is PostgreSQL.

Date and Time Data Types in SQL

As you might have guessed, date and time data types are used for date and time values. Here is a list of the supported date and time data types supported in SQL:

DATE: Stores date values in the YYYY-MM-DD format
TIME Stores time values in the HH:MI:SS format
DATETIME: Stores both date and time values in the YYYY-MM-DD HH:MI:SS format
TIMESTAMP: Used to store the number of seconds that have passed since the Unix epoch, or 1970-01-01 00:00:00 UTC
YEAR: Used to store a year value in either a 2-digit or 4-digit format, ranging from 1901 – 2155 in 4-digit format, or 70 – 69 in a 2-digit format, which represents the time frame of 1970 – 2069

Binary Data Types in SQL

Binary data is data that is represented by 1s and 0s; in SQL, binary data types are usually used for binary data such as images or text files. SQL supports the following types of binary data types:

BINARY: Used when the size of the data is consistent; used for fixed-length binary data with a maximum length of 8,000 bytes
VARBINARY: Used for variable-length binary data up to 8,000 bytes
VARBINARY(max): Used for variable-length binary data with a provided maximum byte size
IMAGE: Used for variable-length binary data with 2GB maximum size

Other Data Types in SQL

SQL supports other data types not listed in this tutorial (at the time of this writing) – the data types contained herein are the most commonly used. In addition, it should be noted that vendor-specific relational databases – or other flavor of SQL – like MySQL, T-SQL, Oracle, and MS SQL Server have their own variations and syntax for the data types in this database programming tutorial. We will cover those in a future tutorial, which will be linked here, so be sure to check back often.

Before we wrap up this tutorial, however, let’s discuss four more commonly used SQL data types:

BLOB: Used for storing large binary objects
CLOB: Used for storing large character objects up to 2GB in size
JSON: Used to store JSON data and JSON documents
XML: Used to store XML schema data

Read: How to Comment in SQL

The post SQL Data Types appeared first on Database Journal.

Categories