AppDividend
Latest Code Tutorials

SQL Datatypes Tutorial | Datatypes In SQL Explained

0

SQL Datatypes Tutorial With Example | Datatypes In SQL is today’s topic. Data types are used to represent a nature of a data that can be stored in the database. The data type is the set of representable values. It is also known as the attribute that specifies a type of data of the object. Each column, variable, and expression has the related data type in the SQL while creating the table. In the last tutorial, we have seen the SQL Date Time Functions. Now, let’s start our SQL Datatypes Tutorial.

#SQL Datatypes important points

  1. Relational database vendors support not all data types. For example, the Oracle database doesn’t support a DATETIME, and MySQL doesn’t support a CLOB data type. So while designing the database schema and writing the SQL queries, make sure to check if the data types are supported or not.
  2. Datatypes listed here doesn’t include all the data types; these are the most popularly used data types. Some relational database vendors have their data types that might be not listed here. For example, the Microsoft SQL Server has money and smallmoney data types, but since other favorite database vendors do not support it, it’s not listed here.
  3. Every relational database vendor has its maximum size limit for the different data types, and you don’t need to remember a limit. The idea is to know what data type to be used in a specific scenario.

SQL Datatypes Tutorial

The SQL developer must decide what type of the data that will be stored inside each column when creating the table. The data type is the guideline for SQL to understand what kind of data is expected inside of each column, and it also identifies how the SQL will interact with the stored data.

SQL Datatypes mainly classified into six categories for every database.

  1. String Datatypes
  2. Numeric Datatypes
  3. Date and time Datatypes
  4. Binary data types like binary, varbinary, etc.
  5. Unicode character string datatypes such as nchar, nvarchar, ntext, etc.
  6. Miscellaneous data types such as clob, blob, XML, cursor, table, etc.

#SQL Numeric Data Types

DATATYPE FROM 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
numeric -10^38 +1 10^38 -1
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

#SQL Date and Time Data Types

DATATYPE DESCRIPTION
DATE Stores date in the format YYYY-MM-DD
TIME Stores time in the format HH:MI:SS
DATETIME Stores date and time information in the format YYYY-MM-DD HH:MI:SS
TIMESTAMP Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC)
YEAR Stores year in 2 digits or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069.

#SQL Character and String Data Types

DATATYPE DESCRIPTION
CHAR Fixed length with a maximum length of 8,000 characters
VARCHAR Variable length storage with a maximum length of 8,000 characters
VARCHAR(max) Variable length storage with provided max characters, not supported in the MySQL
TEXT The variable length storage with a maximum size of 2GB data

Note that all the above data types are for the character stream; they should not be used with a Unicode data.

#SQL Unicode Character and String Data Types

DATATYPE DESCRIPTION
NCHAR Fixed length with a maximum length of 4,000 characters
NVARCHAR Variable length storage with a maximum length of 4,000 characters
NVARCHAR(max) Variable length storage with provided max characters
NTEXT Variable length storage with a maximum size of 1GB data

Note that above data types are not supported in the MySQL database.

#SQL Binary Data Types

DATATYPE DESCRIPTION
BINARY Fixed length with a maximum length of 8,000 bytes
VARBINARY Variable length storage with a maximum length of 8,000 bytes
VARBINARY(max) Variable length storage with provided max bytes
IMAGE Variable length storage with a maximum size of 2GB binary data

#SQL Miscellaneous Data Types

DATATYPE DESCRIPTION
CLOB Character large objects that can hold up to 2GB
BLOB For large binary objects
XML for storing XML data
JSON for storing JSON data

#MySQL String Datatypes

Varchar(size) It is used for specifying a variable length string that can contain numbers, letters, and special characters. Its size limit is 0 to 65535 characters.
Char(size)

It is used for specifying a fixed length string that can contain numbers, letters, and special characters. By default, it can hold 1 character. Its size limit is 0 to 255 characters.

VARBINARY(size)

It is as similar as VARCHAR(), and the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes.

Binary(size) It is used for storing binary byte strings. The default value is 1, and its size parameter specifies the column length in bytes.
TINYTEXT It holds a string with a max value of 255 characters.
TEXT(size) It is used for storing a string with a max length of 255 characters which is similar to CHAR().
LONGTEXT It holds the string with a max value of 4,294,967,295 characters.
 

MEDIUMTEXT

It holds the string with a max value of 16,777,215 characters which is quite larger than VARCHAR()
ENUM(val1,val2,….)

It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65535 values in the ENUM list. If a value is inserted, that is not in a list, and the empty value will be inserted. The values are sorted in order at the time of entering.

SET(val1,val2,……)

It is used to specify the string that can has 0 or more values, chosen from a list of possible values. At one time, 64 values can be listed.

BLOB(size)

It is used for large binary objects which can hold up to 65535 bytes.

 

#MySQL NUMERIC DATATYPES

BIT (size)

Used for a bit value type. Size is used for specifying the number of bits. The range is from 1-64. By default, value is 1.

INT (size)

Used for the integer value. The range is from -2147483648-2147483647. The size parameter specifies the max display width of 255.

INTEGER (size)

It is similar to INT (size).

FLOAT (size,d)

Used for floating point number. The size parameter specifies the total number of digits. d is used for setting the number of several digits after the decimal point.

Float(p)

Used for a floating point as well as double type. If the value of p is from 0-24, then the data becomes float, and if the value of p is from 25-53, then the data becomes double.

DOUBLE (size,d)

It is similar to FLOAT(size,d).

DECIMAL(size,d)

Used for specifying a fixed-point number. The maximum value size can hold 65, and by default, its value will be 10 and d can hold a maximum value of 30, and by default, value is 0.

BOOL

Used for specifying Boolean values. Zero is considered as false and remaining non-zero values as true.

 

#MySQL DATE AND TIME DATATYPES

DATE

Used for specifying the date format. In MySQL the format is YYYY-MM-DD. The range is from ‘1000-01-01’ to ‘9999-12-31’.

DATETIME(fsp)

Used for specifying date and time combination. The format is YYYY-MM-DD hh:mm:ss. Range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

TIMESTAMP(fsp)

Used for specifying the timestamp. The format is YYYY-MM-DD hh:mm:ss. Its supported range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.

TIME(fsp)

Used for specifying the time format. The format is hh:mm:ss. The range is from ‘-838:59:59’ to ‘838:59:59’.

YEAR

Used for specifying the year in four-digit format. The range is from 1901 to 2155 and 0000.

 

The above datatypes, as discussed above, are used for creating tables for beginners.

Let me show you where this datatype is used.

Suppose we want to create a table: (STUDENT)

The following attributes are:

ID, NAME, CITY, DOJ

SYNTAX:

Create table table_name (column1 datatype, column2 datatype,…………);

So, the query will be following.

Create table student (ID integer, NAME varchar (25), CITY char (10), DOJ DATE);

Now, Integer, varchar (25), char (10), DATE this all are datatypes which describes what kind of data is to be stored.

Now let’s discuss some server data types which are used in MySQL.

#SQL Server String Data Type

   Char(n)

It is a fixed width character string data type. A range is 8000 characters.

varchar(n) It is a variable width character string data type. A range is 8000 characters.
varchar(max)

It is a variable width character string data type. Its size can be up to 1,073,741,824 characters.

text It is a variable width character string data type. Its size can be up to 2GB of text data.
nchar It is a fixed width Unicode string data type. Its size can be up to 4000 characters.
nvarchar It is a variable width Unicode string data type. Its size can be up to 4000 characters.
ntext It is a variable width Unicode string data type. Its size can be up to 2GB.
binary(n)

It is a fixed width Binary string data type. Its size can be up to 8000 bytes.

varbinary It is a variable width Binary string data type. Its size can be up to 8000 bytes.
image It is also a variable width Binary string data type. Its size can be up to 2GB.

 

#SQL Server Numeric Data Types

Bit It is an integer that can be 0, 1, or null.
Tinyint It allows whole numbers from 0 to 255.
Smallint It allows whole numbers in the range -32,768 and 32,767.
Int

It allows whole numbers between -2,147,483,648 and 2,147,483,647.

bigint

It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

float(n)

It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. The default value of n is 53.

real

It is a floating precision number data from -3.40E+38 to 3.40E+38.

money

It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

 

#SQL Server Date and Time Data Type

datetime

It is used to specify the date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.

datetime2

It is used to specify the date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.

date

It is used to store the date only. It supports range from January 1, 0001 to December 31, 9999

Time

It stores time only to an accuracy of 100 nanoseconds.

timestamp

It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only a one-time stamp variable.

 

#SQL Server Other Data Types( Miscellaneous Data Types)

Sql_variant

It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.

XML It stores XML formatted data. Maximum 2GB.
cursor

It stores a reference to a cursor used for database operations.

Table It stores result set for later processing.
Uniqueidentifier

It stores GUID (Globally unique identifier).

 

#Oracle Datatypes

#Oracle String data types

CHAR(size) It is used to store character data. It can be stored up to 2000 bytes.
NCHAR(size)

It is used to store national character data within the predefined length. It can be stored up to 2000 bytes.

VARCHAR2(size)

It is used to store variable string data within the predefined length. It can be stored up to 4000 bytes.

VARCHAR(SIZE)

It is the same as VARCHAR2(size).

NVARCHAR2(size)

It is used to store Unicode string data within the predefined length. We have to must specify the size of an NVARCHAR2 data type. It can be stored up to 4000 bytes.

 

#Oracle Numeric Data Types

NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127.
FLOAT(p)

It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.

BINARY_FLOAT It is used for binary precision ( 32-bit). It requires 5 bytes, including length byte.
BINARY_DOUBLE

It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

 

#Oracle Date and Time Data Types

DATE

It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD.

TIMESTAMP

It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

 

#Oracle Large Object Data Types (LOB Types)

BLOB

Used for specifying unstructured binary data. Its range goes up to 232-1 bytes or 4 GB.

BFILE

Used for storing binary data in an external file. Its range is from 232-1 bytes or 4 GB.

CLOB It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB.
NCLOB

It is used to specify a single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB.

RAW(size)

It is used to specify the variable length of raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be determined.

LONG RAW

It is used to specify the variable length of raw binary data. Its range up to 231-1 bytes or 2 GB, per row.

 

We have not only written SQL Datatypes, but also, MySQL Datatypes, Oracle Datatypes, and SQL Server Datatypes in this tutorial.

Finally, SQL Datatypes Tutorial | Datatypes In SQL Explained is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.