SQL Data Types

This tutorial covers SQL Data Types in detail.

Computer programs deal with data. Data has various forms. For example,
Name – Text string
Age – Numeric
Date Of Birth – Date

Data Type is a classification of data to inform compilers/interpreters how the data is intended to be used within the program. Programming languages deal with different types of data.

Similarly, database software also supports various types of data.The data type of every column within a database table need to be specified at the time of table creation so that each field store only a specific type of data.

Generic SQL Data Types

SQL Data types define what type of data a column can contain. Each variable and column in SQL have a related data type such as,

SMALLINT
INTEGER
DECIMAL
NUMERIC
REAL
FLOAT
DOUBLE PRECISION
CHARACTER
VARCHAR
BOOLEAN
DATE
TIME
TIMESTAMP etc.

In this section of SQL tutorial, we will look into various SQL data types.The following lists explain various data types available in SQL.

This is a list of general SQL data types which may not be completely supported by every SQL bases database.

Numeric Data Types in SQL

SQL Numeric Data Types are the attributes for data types in sql that are related to numerical values. Data types that store numerical values are Real, Numeric, Decimal, Float and Integer.

General functions associated with this data type include SUM, MAX, AVG, COUNT and MIN.

SQL Data type	Description
smallint	Integer,Precision 5
integer	Integer with default Precision 10
integer(p)	Integer with Precision p
decimal(p,s)	The decimal data type can store exact numerical data with precision p and scale s.  Example: DECIMAL(6,3) – a number with total 6 digits. 3 digits before the decimal point and 3 digits after the decimal point.
numeric(p,s)	Same as decimal, numeric is to store numeric number with fixed precision and scale. Decimal and numeric data types can be used interchangeably.
float(p)	float data types is known as an approximate data type.Approximate numeric data types store an extremely close approximation of the value not the exact value.p is the matissa precision.
float	float is approximate numerical datatype with mantissa(coefficient) precision 16
real	real is an approximate numerical data type with mantissa precision 7
double precision	double precision also an approximate numerical with precision 16

Since float and real are approximate values, don’t use float or real type columns in search commands with the comparison operators = and <>. But it is good to use float and real columns to > or < comparisons.

See below the ranges of values each numeric data type can store,

SQL Data Type	Ranging From	Ranging To
bit	0	1
tinyint	0	255
smallint	-32,768	32,767
int	-2,147,483,648	2,147,483,647
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
decimal	-10^38 +1	10^38 -1
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38
numeric	-10^38 +1	10^38 -1

Character Data Types in SQL

The SQL Character Data Types are for storing character strings of fixed or variable length,

Certain Character Data Types support Unicode character also.

You need to specify the length of the character string in datatype declaration statement.

Example: char(n),n signifies the chosen length of the string character .

Listed the various character string data types below,

SQL Data Type	Description
char	Fix-length up to 8,000 characters,,non-Unicode data
varchar	Max 8,000 characters, variable-length, non-Unicode.
varchar(max)	max 231 characters, variable-length non-Unicode data
text	Variable-length non-Unicode data of up to 2,147,483,647 characters.
Unicode Character Data Types
nchar	Fix-length , up to 4,000 characters, Unicode data allowed
nvarchar	Variable-length Unicode data up to 4,000 characters.
nvarchar (max)	Variable-length Unicode data maximum 231 characters
ntext	Variable-length Unicode data up to 1,073,741,823 characters.

Binary Data Types

SQL Binary data types are to store binary data of either fixed length or variable length.

Use binary data type in SQL when the sizes of the column data entries are fixed.

Use varbinary data type in SQL when the size of data to store in the columns vary significantly.

Use varbinary(max) type in SQL when size of the column data exceed 8,000 bytes.

DATA TYPE	Description
binary	For storing Fixed-length binary data. The maximum length of 8,000 bytes.
varbinary	For storing variable length binary data.Max length is 8,000 bytes.
varbinary(max)	For storing variable length binary data.Maximum length to be set.
image	To store images as variable length binary data.Maximum length of 2,147,483,647 bytes.

Date-Time Data Types in SQL

date:
For storing  date values.  Format: yyyy-mm-dd

time:
For storing time values. Format: hh:mi:ss

datetime:
To store values which are combinations of date and time . Format: yyyy-mm-dd hh:mi:ss[.nnn]

smalldatetime:
To store combinations of date and time in format  YYYY-MM-DD hh: mm: ss

datetime2:
This is a kind of extension to DateTime type. Datetime2 is a  date type joined with a time of day that is based on 24-hour clock.

Data type	Format	Range From	Range To
date	YYYY-MM-DD	0001-01-01	9999-12-31
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01	2079-06-06
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01	9999-12-31
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999

MONEY and SMALLMONEY Data Types in SQL

MONEY and SMALLMONEY data types are suitable for storing money and do the operation such as in accounting.The money and smallmoney  are accurate to a ten-thousandth of the monetary units that they represent.Use a period(.) to separate monetary units, like cents, from a whole monetary unit.For example, 3.75 specifies 3 dollars and 75 cents.

Note: while we can specify monetary values preceded by a currency symbol, the database only stores the numeric data, It doesn’t store the currency information associated with the symbol. Any one of the following currency symbols can be used with these data types.

Image Credit : https://msdn.microsoft.com

Money data type range is from  922,337,203,685,477.5808 to +922,337,203,685,477.5807
Smallmoney range  is from  -214,748.3648 to +214,748.3647

Other DataTypes in SQL

table :
table Is a special SQL data type, used to store a result set for processing at a later point in time.Table type is mainly used for temporary storage of row sets returned as the result set of a table-valued function. Functions and variables can be declared as table type. Table variables can be used in batches, stored procedures, and functions,
Syntax to declare variables as data type table is, DECLARE @local_variable.

XML:
You can store xml data in a column or a variable in the database.We can use the xml data type as a column type while creating a table as a variable type, a function-return type, a parameter type, or in CAST and CONVERT.

sql_variant : 
sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. A column of type sql_variant can store rows of different data types(except text, ntext, and timestamp). For example, a column defined as sql_variant can store char, int, and binary values.

Maximum length of sql_variant is 8016 bytes, including base type information and the base type value. Actual base type value’s maximum length is 8,000 bytes.

Before involving in operations such as addition and subtraction  sql_variant data type need to be cast to its base data type

timestamp:
The timestamp is a data type that stores a database-wide unique number that gets updated every time a row gets updated. A timestamp data type is only an incrementing number.It doesn’t reserve a date or a time. timestamp data type is generally used as a mechanism for version-stamping table rows. The storage size of timestamp data type is 8 bytes.

uniqueidentifier data type in SQL:
This data type is used to store a globally unique identifier (GUID).Normally a uniqueidentifier data type can be initialized by using the NEWID function.there are other ways also.

Cursor data type in SQL:
Reference to a cursor object. Variables created with the cursor data type are nullable. You cannot use cursor data type for a column in a Create Table statement.