A complete guide on Datatypes in SQL

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.

Datatypes?

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.

Basic Syntax:

CREATE TABLE <tablename> (
	<column_name> <datatype> ,
	<column_name> <datatype> ,
... );

Categories of Data Types: 

• Numeric datatypes
• Date time datatypes
• Character datatypes
• Large object datatypes
• Other datatypes

Numeric Datatypes:

1) Integer:

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(SIZE): 

• 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:

• 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

SMALLINT:

• 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

TINYINT:

• 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”

Example:

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.

Example:

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

Example: 

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

Example:

CREATE TABLE mytable( 
  cost SMALLMONEY, //exact decimal digits of 4 bytes
  tax SMALLMONEY,
  mrp SMALLMONEY,
  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

Example:

CREATE TABLE mytable (s1 VARBINARY(15)); 
INSERT INTO mytable VALUES('12345678901');

Date and Time Datatypes:

1) date: 

• Used to specify dates in SQL
•  Format – YYYY-MM-DD
•  Size – 3 bytes

2) time: 

• Defines time in SQL
• Default format – hh:mm:ss[.nnnnnnn]
• Size – 5 bytes

3) datetime: 

• 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

4) datetime2:

• 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

5) smalldatetime:

• 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

6) datetimeoffset:

• 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

7) timestamp:

• 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

Example: 

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

Character Datatypes:

1) char(n): 

• 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

2) varchar(n): 

• 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

Example:

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:
  This datatype 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

Other Datatypes:

• 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 🙂

Recommended -