Datatypes are necessary as it tells the computer how the data is intended to use.
So we must specify datatypes to identify the type of a variable, for instance, INT generally for integer.
Table of Contents
In our previous post we discussed about the basics of sql and to store data as we know we need datatypes so that we know which data will be stored in the database. A datatype is a classification of the type of data that a variable/object can hold or allowed to hold.
All the four integer types take up different amounts of space and they have dissimilar ranges of acceptable values.
It comes under exact data types.
It is presented in the increasing order of sizes.
INT is the primary integer datatype in SQL.
It is used to specify an integer value and the size is optional.
Represents a signed 32-bit integer, used to store values from -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647)
The size is 4 bytes
BIGINT is used when the integer value exceeds the range supported by INT.
Represents a signed 64-bit integer which is used to store values from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807)
The size is 8 bytes
It is used to determine the small integer values
Represents a signed 16-bit integer, used to store values from -2^15 (-32,768) through 2^15 – 1 (32,767)
Size of smallint is 2 bytes
Represents a single byte and stores values from 0 to 255
Size is 1 byte
2) Bit Datatype:
It is specified as BIT or sometimes bit(x) where x is the number of bits need to be stored
Represents a single bit which can be either 0 or 1
String values TRUE (means 1) or FALSE (means 0) are usually converted to bit values.
Comes under exact data types and also termed as “Boolean datatype”
CREATE TABLE mytable (
s_tinyint INT, //integer variable with 1 byte
s_smallint SMALLINT, //integer variable with 2 bytes
s_int INT, //integer variable with 4 bytes
s_bigint BIGINT, //integer variable with 8 bytes
s_status BIT //either 0 or 1
3) Decimal & Numerci:
Decimal = Numeric i.e same thing with different names
All the values in the datatype range contains precision and scale. Hence they come under Fixed-Precision datatypes
It is denoted as NUMERIC(p,s) or DECIMAL(p,s) where p is precision and s represents the scale
Precision is number of digits in a number and scale is number of digits to the right of the decimal point in a number
For example, the number 115.91 has a precision of 5 and a scale of 2.
Example: Let’s consider, DECIMAL(5,3) be 77.111 ( first 5 indicates the total number of digits and latter indicates the number of decimal points). Similarly, DECIMAL(7,5) can be given as 77.11111.
Note:77.11 and 77.11111 are two different types in case of Numeric/Decimal
It is also a classification of exact data types.
CREATE TABLE mytable (
expense DECIMAL(20,5) //decimal number having 20 digits in total and not more than 5 digits after decimal point
//like 19876.213 or 2345678.87964
4) Float & Real:
They come under approximate data types which means it does not store the exact values
Both are floating point numeric with binary precision
Float can be denoted as:
float – it is single precision(32 bits) i.e it takes 4 bytes
float(n) – n is the number of bits used to store the mantissa of a number in scientific notation
Size of float is depends on the value of n
For example, float(6) can be either 77.11 or 77.11111
Real is similar to float but it is of 4 bytes
Note:In case of float, 77.11 and 77.11111 are same types and they are not accurate
5) Double Precision:
It is signed floating point numeric with binary precision
Its precision is greater than that of REAL
It is similar to FLOAT except that it is double precision(64 bits) i.e it takes 8 bytes
CREATE TABLE mytable (
float_var FLOAT(10), //floating point number with 10 bits
real_var REAL, //floating point number with 32 bits
double_var DOUBLE PRECISION //floating point number with 64 bits
6) Money & Smallmoney:
These are monetary units or currency values
Both data types are accurate to a ten-thousandth of the monetary units they represent.
A period is used to separate partial monetary units like paise from whole monetary units for instance,50.30 specifies 50 rupees and 30 paise
Money is preferred for exact values and requires less bytes compared to decimal
Money is 8 bytes whereas smallmoney is 4 bytes
CREATE TABLE mytable(
cost SMALLMONEY, //exact decimal digits of 4 bytes
total MONEY //exact decimal digits of 8 bytes
7) Binary datatype:
Fixed-length binary datatype
Storage size is n bytes
Range is from 0 to 8000
VARBINAR or Binary Varying:
Variable-length binary datatype
Storage size is “actual length of data” + 2 bytes
Range is same as that of binary
Similar to varchar but stores binary byte strings rather than non-binary character strings
varbinary(n) stores binary data of n bytes and maximum range is 0 to 8000
varbinary(max) stores binary data of n bytes and maximum range is upto 2GB
CREATE TABLE mytable (s1 VARBINARY(15));
INSERT INTO mytable VALUES('12345678901');
Date and Time Datatypes:
Used to specify dates in SQL
Format – YYYY-MM-DD
Size – 3 bytes
Defines time in SQL
Default format – hh:mm:ss[.nnnnnnn]
Size – 5 bytes
Specifies date combined with time of a day with fractional seconds based on a 24-hour clock.
Format – YYYY-MM-DD hh:mm:ss[.nnn]
Size – 8 bytes
An extension of the datetime type whith has larger date range and larger default fractional seconds.
Format – YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Size – 8 bytes
Defines date combined with time of a day on a 24-hour clock without fractional seconds i.e seconds always zero (hh:00)
Format – YYYY-MM-DD hh:mm:ss
Size – 4 bytes
Specifies date and time which has time zone awareness and is based on 24 hours clock.
Format – YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
Size – 10 bytes
It is a synonym for row-version means every time a row is changes the timestamp value is increased.
A mechanism for version-stamping table rows
It does not preserve any date or time
Timestamp column helps to identify the last changes made in a row value
CREATE TABLE datetable(current_date DATE,current_time TIME);
INSERT INTO datetable values('2017-07-30','08:25:44'); //date and time
CREATE TABLE mytable (key int PRIMARY KEY, timestamp); //timestamp keeps the count of changes made in the row
It is fixed-length string data and n should be specified
Allocates 1 byte of space for each character
Uses entire size of n. For example, Char(10) means length is 10 irrespective of length of the actual string
Use char when the sizes of the column data entries are consistent
nchar refers to national char where the size is doubled
It is variable-length data type and n must be specified
Occupies 1 byte of space for each character
Use varchar when the sizes of the column data entries vary considerably.
Allocates only those storage that are required to store a string. For example, varchar(10) depends on the length of the string stored in it
nvarchar is national character varying in which the size doubles
CREATE TABLE mytable (
cname CHAR(7), //character string with size 7
vname VARCHAR(7) //string with size depends on the actual length
INSERT INTO mytable VALUES('neha','nyomi'); //length(cname):7 whereas length(vname):5
Large Object datatypes ( Sizes in GB and TB ):
BFILE: Thisdatatype is used to store large binary files outside the database in the host operating file system but can be accessed from database tables
BLOB: It is similar to BFILE but stores unstructured objects which includes audio or video files
CLOB: It stores large document of character data sets into the database
image – Variable are used to store any type of data file (not just images) and store up to 2GB of data
Array – A set length and ordered collection of elements
Multiset – A variable length and unordered collection of elements
cursor – It is a datatype for variable or stored procedure OUTPUT which contains reference to a cursor. Used to manipulate data in a set on a row-by-row basis.
Knowledge is of no value unless you put it into practice!!
Do share and subscribe and comment below for any suggestion, query or opinion. Keep Coding! Happy Coding 🙂
A complete guide on Datatypes in SQL was last modified: July 30th, 2017 by Srimathi