SQL Data Types

The data type of a column can be defined as basically what type of data format in which each cell will store the data – it can be any type of integer, character, money, date and time, binary, etc. In this article, we’ll get to learn about SQL Data Types in detail.

SQL Data Types

An SQL developer must aware of what type of data will be stored inside each column while creating a table. The data type guideline for SQL is to understand what type of data is expected inside each column and it also identifies how SQL will interact with the stored data.

SQL (Structured Query Language) is a language used to interact with relational databases. SQL data types define the type of data that can be stored in a database column or variable. Here are the most common SQL data types:

Data Type	Properties
Numeric data types	These are used to store numeric values. Examples include INT, BIGINT, DECIMAL, and FLOAT.
Character data types	These are used to store character strings. Examples include CHAR, VARCHAR, and TEXT.
Date and time data types	These are used to store date and time values. Examples include DATE, TIME, and TIMESTAMP.
Binary data types	These are used to store binary data, such as images or audio files. Examples include BLOB and BYTEA.
Boolean data type	This data type is used to store logical values. The only possible values are TRUE and FALSE.
Interval data types	These are used to store intervals of time. Examples include INTERVAL YEAR, INTERVAL MONTH, and INTERVAL DAY.
Array data types	These are used to store arrays of values. Examples include ARRAY and JSON.
XML data type	This data type is used to store XML data.
Spatial data types	These are used to store geometric or geographic data. Examples include POINT, LINE, and POLYGON.

Different databases may have different variations of these data types, or they may have additional data types not listed here. Understanding SQL data types are important for creating tables and working with data in a database, as it affects how data is stored and processed.

Like in other programming languages, SQL also has certain datatypes available. A brief idea of all the datatypes is discussed below.   

Binary Datatypes

There are four subtypes of this datatype which are given below:

Data Type	Description
Binary	The maximum length of 8000 bytes(Fixed-Length binary data)
varbinary	The maximum length of 8000 bytes(Variable Length binary data)
varbinary(max)	The maximum length of 231 bytes(SQL Server 2005 only).(Variable Length binary data)
image	Maximum Length of 2,147,483,647 bytes(Variable Length binary data)

Exact Numeric Datatype

There are nine subtypes which are given below in the table. The table contains the range of data in a particular type. 

Data Type	From	To
BigInt	-2^63 (-9,223,372,036,854,775,808)	2^63-1 (9,223,372,036,854,775,807)
Int	-2^31 (-2,147,483,648)	2^31-1 (2,147,483,647)
smallint	-2^15 (-32,768)	2^15-1 (32,767)
tinyint	0	2^8-1
bit	0	1
decimal	-10^38+1	10^38+1
numeric	-10^38+1	10^38+1
money	-922,337,203,685,477,5808	922,337,203,685,477,5808
smallmoney	-2,147,748	2,147,748

Approximate Numeric Datatype

The subtypes of this datatype are given in the table with the range. 

Data Type	From	To
Float	-1.79E+308	1.79E+308
Real	-3.40E+38	3.40E+38

Character String Datatype

The subtypes are given in below table – 

Data Type	Description
char	The maximum length of 8000 characters.(Fixed-Length non-Unicode Characters)
varchar	The maximum length of 8000 characters.(Variable-Length non-Unicode Characters)
varchar(max)	The maximum length of 231 characters(SQL Server 2005 only).(Variable Length non-Unicode data)
text	The maximum length of 2,127,483,647 characters(Variable Length non-Unicode data)

Unicode Character String Datatype

The details are given in below table – 

Data Type	Description
nchar	The maximum length of 4000 characters(Fixed-Length Unicode Characters)
Nvarchar	The maximum length of 4000 characters.(Variable-Length Unicode Characters)
nvarchar(max)	The maximum length of 231 characters(SQL Server 2005 only).(Variable Length Unicode data)

Date and Time Datatype

The details are given in the below table.

Data Type	Description
DATE	A data type is used to store the data of date in a record
TIME	A data type is used to store the data of time in a record
DATETIME	A data type is used to store both the data,date, and time in the record.

XML Datatype

XML data type allows storage of XML documents and fragments in a SQL Server database

DataType	Description
XML Datatype	A Datatype used to store data in the format of XML datatype

Spatial Dataype

A datatype is used for storing planar spatial data, such as points, lines, and polygons, in a database table.

DataType	Description
Geometry	A datatype is used for storing planar spatial data, such as points, lines, and polygons, in a database table.

Array Datatype

SQL Server does not have a built-in array datatype. However, it is possible to simulate arrays using tables or XML data types.

My Personal Notes arrow_drop_up

Save