SQL Database Type

In this chapter we will learn what are data types and how we can use them in SQL. Based on data types, data means value in table or object and type mean categorize that data into table or for object. Let’s understand with different types of values.

For Example:

Name: John Turner (Text type value)

Birth Date: 10/10/1983 (Date type value)

Mobile Number: 9876543215 (Number type value)

Salary: 20,000 (Money type value)

Height: 160.5 (Decimal type value)

In above all fields, all values are different so here data type means what kind of values we are using for different fields. You may define data type for each field in table while you create it. It is mandatory to define data type for each column in table. Before creating your table, you need to understand what fields you are going to add into database table and based on that you need to define data type for each column in database table. Based on data type, SQL will understand easily each column and interact with server how to store data in each column.

Based on different database management system like MS Sql Server, My Sql, MS Access, Oracle use different or same way to define data type. But, it might happen that each defines in different way to store different data. Few might use same name for data type but their storage capacity is different.

Let’s understand data type in MS Sql Server, My Sql, Oracle and MS Access.

MS SQL Server Data Type

String or Character Data Type:

Data Type Name Storage Capacity Syntax Example
TEXT 2 G.B. text Store character string value
NTEXT 2 G.B. ntext(1000) Store character, string, numeric and Unicode values
CHAR 8,000 Chars char(100) Store character or numeric value
NCHAR 4,000 Chars nchar(100) Store character, numeric or Unicode value
VARCHAR 8,000 Chars - 1,073,741,824 Chars varchar(100) Store string and numeric values
NVARCHAR 4,000 Chars - 536,870,912 Chars nvarchar(100) Store string and numeric values
BINARY 8,000 Bytes binary(100) Store value with binary conversion
VARBINARY 8,000 Bytes - 2 G.B. varbinary(1000) Store value with binary conversion
IMAGE 2 G.B. image Convert image into binary data

Number and Decimal Data Type:

Data Type Name Storage Capacity Syntax Example
INTEGER -2,147,483,648 to 2,147,483,647 int(10) Store numeric value within range
BIG INTEGER -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 bigint(10) Store numeric value
SMALL INTEGER -32,768 to 32,767 smallint(10) Store numeric value
TINY INTEGER 0 to 255 tinyint(10) Store numeric value
BIT 0, 1 or NULL bit Store either 0 or 1 or NULL
NUMERIC -10^38 +1 to 10^38 -1 numeric(18,0) Store numeric value and decimal as well
DECIMAL -10^38 +1 to 10^38 -1 decimal(18,2) Store numeric value and decimal as well
FLOAT -1.79E + 308 to 1.79E + 308 float(4) Store numeric value and decimal as well
REAL -3.40E + 38 to 3.40E + 38 real Store numeric value and decimal as well
MONEY -922,337,203,685,477.5808 to +922,337,203,685,477.5807 money Store money format data.
SMALL MONEY -214,748.3648 to +214,748.3647 smallmoney Store money format data within range

Date and Time Data Type:

Data Type Name Storage Capacity Syntax Example
DATE 3 Byte date Store date only
DATETIME 8 Byte datetime Store date and time
DATETIME2 6 to 8 Byte datetime2(7) Store date and time
SMALL DATETIME 4 Byte smalldatetime Store date and time
TIME 3 to 5 Byte time(7) Store time only
TIMESPAMP 8 to 10 Byte timestamp Store time only
DATETIME OFFSET datetimeoffset(7) Store date time with time zone offset

Advanced or Complex Data Type:

Data Type Name Description
TABLE Stores a result-set for later processing
XML Any data who is structured in XML format with max 2 GB storage capacity
CURSOR Store reference of cursor in database
SQL VARIANT Store other type of data(Except text, ntext, timestamp)
UNIQUE IDENTIFIER Store GUID(Known as Globally Unique Identifier)

My Sql Data Type

String or Text type:

Data Type Name Size Description
TEXT 0 to 65,535 chars Store any string type value
TINYTEXT 0 to 255 chars Store any string type value
MEDIUMTEXT 0 to 16,777,215 chars Store any string type value
LONGTEXT 0 to 4,294,967,295 chars Store any string type value
CHAR 0 to 255 chars Store any string type value
VARCHAR 0 to 255 chars Store any string type value
BLOB 0 to 65,535 chars For Binary Large OBjects
MEDIUMBLOB 0 to 16,777,215 chars For Binary Large Objects
LONG BLOB 0 to 4,294,967,295 chars Binary Large OBjects
SET 2 GB0 to 64 List items Similar like ENUM which contain up to 64 list items and also can accept multiple choice
ENUM 0 to 65,535 list items You may store list of values.

Number or Numeric type:

Data Type Name Size Description
INT -2147483648 to 2147483647 Store number only
TINYINT -128 to 127 Store number only
SMALLINT -32768 to 32767 Store number only
MEDIUMINT -8388608 to 8388607 Store number only
BIGINT -9223372036854775808 to 9223372036854775807 Store number only
DECIMAL Define your own size Store decimal with precision and scale
FLOAT Define your own size Store decimal with precision and scale
DOUBLE Define your own size Store decimal with precision and scale

Date and Time type:

Data Type Name Description
DATETIME Store date and time with format like YYYY-MM-DD HH:MM:SS
DATE Store date value with format like YYYY-MM-DD
TIME Stores time value with format like HH:MM:SS
YEAR Store year value in two or four digit
TIMESTAMP Store UTC format date and time like ‘1970-01-01 00:00:00' UTC

Oracle Data Type:

Data Type Name Size Description
NUMBER 1 to 22 Byte Store numeric value with precision p and scale s
VARCHAR2 1 to 4 Byte Store string and numeric values
NVARCHAR2 1 to 4000 Byte Store string and numeric values
LONG 2 GB Store character data
FLOAT 1 to 22 Byte Store numeric value
DATE 7 Byte Store date value
BINARY FLOAT 4 Byte Store 32-bit floating point number.
BINARY DOUBLE 8 Byte Store 64-bit floating point number.
TIMESTAMP 11 Byte Contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND
LONG RAW 2 GB Raw binary data of variable
RAW 2000 Byte Raw binary data of length size bytes
UROWID 4000 Byte The logical address of a row of an index-organized table
BLOB 4 GB A binary large object
BFILE 4 GB Contains a locator to a large binary file stored outside the database
CHAR 2000 Byte Fixed-length character data of length size bytes or chars
NCHAR 2000 Byte Fixed-length character data of length size bytes
CLOB 4 GB A character large object containing single-byte or multi byte characters
NCLOB 4 GB A character large object containing single-byte or multi byte characters

MS Access Data Type:

Data Type Name Size Description
INTEGER 2 Byte Store numeric values from -32,768 to 32,767
BYTE 1 Byte Store numeric value, max 255 chars
SINGLE 4 Byte Store decimal values with single precision point
DOUBLE 8 Byte Store decimal value with double precision point
TEXT 0 to 255 Chars Store string or numeric value with max 255 chars
MEMO 0 to 65,536 Chars Store large text with max 65,536 chars max
LONG 4 Byte Store number with range -2,147,483,648 to 2,147,483,647
DATETIME 8 Byte Store date and time
AUTO NUMBER 4 Byte Store numeric value. It adds value automatically when new record inserted into table.
CURRENCY 8 Byte Store currency data with 18 digit and max 4 decimal places
YES/NO 1 Bit Store Yes or Not, True or False and On or Off with 1 bit size
OLE OBJECT Max 1 GB Store data in binary format(Example are picture, video etc...)
HYPERLINK Store internal or external links(Website URL)
LOOKUP WIZARD 4 Byte Provide selection from given list so user can choose what need to store in database table