SQL Data Types

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

  • When establishing a table, the SQL Data Type property determines the type of data that will be stored inside each column. The data type serves as a guideline for SQL to comprehend what type of data is expected within each column and identify how SQL will interact with the stored data.
  • Integers, floating-point numbers, characters, strings, and arrays are examples of common data types. For more specific SQL uses, Dates, timestamps, boolean values, and varchar (variable character) types of formats.

What are Data Types in SQL?

A data type is a property that describes the sort of data that an object can store, such as integer data, character data, monetary data, date and time data, binary strings, and so on.

Let's look at a simple sign-up page for a website application. First Name, Last Name, and Contact Number are the three entry areas.

sign-up page

  • The First/Last Name field will always be alphabetical.
  • The Contact field will always be a phone number (i.e., numeric).

It is worth specifying "First/Last Name" as a character and "Contact" as an integer because a name can't be a number and a contact number can't be a character.  It is obvious that all fields in any application include one or more types of data. For example, numeric, alphabetic, date, and many others.

Also, different data types have varied memory requirements. As a result, it makes it more logical to declare the column or variable with the data type that it will carry to make better use of memory.

Let us take another scenario to understand more about data types in SQL. Consider the following sample records from a sales table:

sample records

The following are the attributes of this table:

  • customer id: an integer value that increases by one for each new customer.
  • sales_id: an integer value that increases by one for each new sale.
  • currency: always use the three-character currency code.
  • purchase_time: the time of the sale.
  • device: text, with values such as desktop, mobile app, and mobile web.
  • has_discount: a boolean variable with entries that can be TRUE or FALSE.

The data type (integers, text, real numbers, etc...) and allowable value ranges (0 to 1,000; any 3 characters; etc...) match specific database data types.

Each column, local variable, expression, and parameter in SQL Server has a corresponding data type. SQL Server has a collection of system data types that specify all of the data types that can be utilized with SQL Server.

For any database, data types are primarily categorized into three categories:

  • String Datatypes.
  • Numeric Datatypes.
  • Date and time Data types.

MySQL Data Types

MySQL String Data Types

Data TypeDescriptionSize
CHAR(size)A FIXED length string containing letters, numbers, and special characters. The size parameter determines the length of the column in characters. The default value is 1.0 - 255 bytes
VARCHAR(size)A VARIABLE length string containing letters, numbers, and special characters. The size option sets the maximum character length for a column.0 - 65535 bytes
BINARY(size)It is a type of function that stores binary byte strings. The size argument indicates the length of the column in bytes. The default value is 1.0 - 255 bytes
VARBINARY(size)It is a type of function that stores binary byte strings. The size argument sets the maximum length of a column in bytes.0 - 65535 bytes
TINYBLOBFor use with BLOBs (Binary Large Objects).255 bytes
BLOG(size)For use with BLOBs (Binary Large Objects).65,535 bytes
MEDIUMBLOBFor use with BLOBs (Binary Large Objects).16,777,215 bytes
LONGBLOGFor use with BLOBs (Binary Large Objects).4,294,967,295 bytes
TINYTEXT(size)It stores a string.255 characters
TEXT(size)It stores a string.65,535 characters
MEDIUMTEXT(size)It stores a string.16,777,215 characters
LONGTEXT(size)It stores a string.4,294,967,295 characters
ENUM(val1, val2, val3, ...)It is used when a string object has only one value, which is selected from a list of possible values. If you enter a value that does not exist in the list, a blank value will be entered.65535 values
SET(val1, val2, val3, ...)It is used to describe a string with 0 or more values from a list of available values.64 values

MySQL Numeric Data Types

Data TypeDescriptionSize
BOOL/ BOOLEANSynonym for TINYINT(1). Nonzero values are deemed true, while zero values are considered false.
BIT(size)A type of bit-value. Size specifies the number of bits per value. The size parameter accepts values ranging from 1 to 64. The default value is 1.
TINYINT(size)An extremely tiny integer. The signed range is -128 to 127. The unsigned range extends from 0 to 255. The size parameter determines the maximum width of the display (which is 255).1 byte
SMALLINT(size)A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. The size parameter determines the maximum width of the display (which is 255).2 bytes
MEDIUMINT(size)A medium integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. The size parameter sets the maximum display width (which is 255).3 bytes
INT(size)An integer value. The signed range is -2147483648 to -2147483647. The unsigned range is 0 to 4294967295. The size parameter determines the maximum width of the display (which is 255).4 bytes
INTEGER(size)As same as to INT(size).4 bytes
BIGINT(size)A large integer. The signed range is -9223372036854775808 to 9223372036854775807. Unsigned numbers range from 0 to 18446744073709551615. The size parameter determines the maximum width of the display (which is 255).8 bytes
FLOAT(size, d)A number with a decimal value. Size specifies the total number of digits. The d parameter specifies the number of digits after the decimal point. It has been deprecated in MySQL 8.0.17 and will be removed in future releases.4 bytes
FLOAT( p)A number with a decimal value. MySQL utilizes the p value to determine if the final data type is FLOAT or DOUBLE. If p is between 0 and 24, the data type is changed to FLOAT (). If p is between 25 and 53, the data type is changed to DOUBLE ().4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
DOUBLE(size, d)A standard-size floating-point number. Size specifies the total number of digits. The d option specifies the number of digits after the decimal point.8 bytes
DOUBLE PRECISION(size, d)As same as DOUBLE(size, d)8 bytes
REAL(size, d)As same as DOUBLE(size, d)8 bytes
DECIMAL(size, d)A precise fixed-point number. Size specifies the total number of digits. The d option specifies the number of digits after the decimal point. The size has a maximum of 65 characters. D can have a maximum value of 30. Size is set to 10 by default.Varies
DEC(size, d)As same as DECIMAL(size, d)Varies
NUMERIC(size, d)As same as DECIMAL(size, d)Varies
FIXED(size, d)As same as DECIMAL(size, d)Varies

Note: All numeric data types may have an additional option: UNSIGNED or ZEROFILL. If the UNSIGNED option is specified, MySQL does not allow negative values for the column. When the ZEROFILL option is specified, MySQL adds the UNSIGNED property to the column by default.

MySQL Date and Time Data Types

Data TypeDescriptionFormat
DATEIt's a date. The permitted value range is '1000-01-01' to '9999-12-31'.YYYY-MM-DD
DATETIME(fsp)A combination of date and time. The range supported is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. By including DEFAULT and ON UPDATE in the column definition, automatic initialization and updating to the current date and time is achieved.YYYY-MM-DD hh:mm
TIMESTAMP(fsp)The number of seconds is represented in TIMESTAMP values. The acceptable time range is '1970-01-01 00:00:01 UTC' to '2038-01-09 03:14:07 UTC'. Automatic initialization and updating to the current date and time can be defined in the column definition by using DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP.YYYY-MM-DD hh:mm
TIME(fsp)A time. The acceptable time range is '-838:59:59' to '838:59:59'.hh:mm
YEARA year with four digits. The following four-digit values are permitted: 1901 to 2155, and 0000. Year in two-digit format is no longer supported by MySQL since version 8.0.

SQL Server Data Types

SQL Server String Data Type

Data TypeDescriptionSize
CHAR(size)A character string data type with a fixed width.8000 characters
VARCHAR(size)A character string data type with configurable width.8000 characters
VARCHAR(max)A Character string data type with configurable width.1,073,741,824 characters
TEXTA character string data type with configurable width.2 GB of text data
NCHAR(size)A Unicode string data type with a fixed width.4000 characters
NVARCHAR(size)A Unicode string data type with variable width.4000 characters
NVARCHAR(max)A Unicode string data type with variable width.536,870,912 characters
NTEXTA Unicode string data type with variable width.2 GB of text data
BINARY(size)A binary string data type that is fixed.8000 bytes
VARBINARY(size)A varying width Data type for binary strings.8000 bytes
VARBINARY(max)A Binary string data type with configurable width.1,073,741,824 characters
IMAGEA Binary string data type with configurable width.2 GB

SQL Server Numeric Data Types

Data TypeDescriptionSize
BITIt is an integer that can have a value of 0 (zero), 1 (one), or null.
TINYBITA set of whole numbers ranging from 0 to 255.1 byte
SMALLINTA set of whole numbers ranging from -32,768 to 32,767.2 bytes
INTA set of whole numbers ranging from -2,147,483,648 to 2,147,483,647.4 bytes
BIGINTA set of whole numbers ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.8 bytes
FLOAT(n)Data with floating precision ranging from -1.79E+308 to 1.79E+308. The n option specifies whether the field should carry four or eight bytes. n has a default value of 53.4 or 8 bytes
REALData with a floating precision ranging from -3.40E+38 to 3.40E+38.4 bytes
SMALLMONEYSpecifies monetary data ranging from -214,748.3648 to 214,748.3647.4 bytes
MONEYSpecifies monetary data ranging from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.8 bytes
DECIMAL(p, s)A set of Numbers with fixed precision and scale p specifies the maximum number of decimal digits that can be saved. s specifies the number of decimal digits stored to the right of the decimal point. When using the highest precision, acceptable numbers range from - 1038 +1 to 1035-17 bytes
DEC(p, s)As same as DECIMAL(p, s).5-17 bytes
NUMERIC(p,s)As same as DECIMAL(p, s).5-17 bytes

SQL Server Date and Time Data Type

Data TypeDescriptionFormat
DATETIMEA combination of date and time. With an accuracy of 3.33 milliseconds, it ranges from January 1, 1753 to December 31, 9999.YYYY-MM-DD hh:mm.mmm
DATETIME2A combination of date and time. With an accuracy of 100 nanoseconds, it supports a range of January 1, 0001 to December 31, 9999.YYYY-MM-DD hh:mm.fractional seconds
DATEDenotes a date. It is compatible with dates ranging from January 1, 0001 through December 31, 9999.YYYY-MM-DD
TIMEDenotes a time. Time was only stored to an accuracy of 100 nanoseconds. The range varies from '00:00:00.0000000' to '23:59:59.9999999'.YYYY-MM-DD hh:mm.nnnnnnn
DATETIMEOFFSETA combination of date and time that incorporates time zone awareness based on UTC. With a precision of 100 nanoseconds, it supports a range of January 1, 0001 to December 31, 9999.YYYY-MM-DD hh:mm.nnnnnnn [{+,-}hh]
SMALLDATETIMEA combination of time and date. It has a range of January 1, 1900 to June 6, 2079 with a precision of 1 minute.YYYY-MM-DD hh:mm
TIMESTAMPWhen a new row is added or changed, it stores a unique number. The timestamp value is based on an internal clock and does not correlate to real-time. Each table can only have one timestamp variable.

Oracle Data Types

Oracle String Data Types

Data TypeDescriptionSize
CHAR(size)It's used to keep character data inside a specific length limit2000 bytes
NCHAR(size)It's used to keep national character data within a specific length limit2000 bytes
VARCHAR2(size)It's used to keep variable string data inside a specific length limit.4000 bytes
VARCHAR(size)It is equivalent to VARCHAR2 (size). VARCHAR(size) can also be used, although VARCHAR2 (size) is recommended.4000 bytes
NVARCHAR2(size)It is used to hold Unicode string data that is less than a certain length. We must define the size of the NVARCHAR2 data type,4000 bytes

Oracle Numeric Data Types

Data TypeDescriptionRange
NUMBER(p, s)It is a data type that includes Precision p and scale s.1<=p<=38 -84<=s<=127
FLOAT ( p )It is a data type that is a subtype of the NUMBER data type.1<=p<=126
BINARY_FLOATIt is used to achieve binary precision ( 32-bit). It takes 5 bytes, including the length byte.
BINARY_DOUBLEIt is used to achieve double binary precision (64-bit). It takes 9 bytes, including the length byte.

Oracle Date and Time Data Types

Data TypeDescriptionFormat
DATETIMEIt is used to store a fixed-length valid date-time format. Its time-span fluctuates from January 1, 4712 BC, and December 31, 9999 AD.
TIMESTAMPIt is used to hold the valid date and time.YYYY-MM-DD hh:mm.

Oracle Large Object Data Types (LOB Types)

Data TypeDescriptionSize
BLOBIt is used to specify binary data that is unstructured.4,294,967,295 bytes
BFILEIt is used to save binary data in a separate external file.4,294,967,295 bytes
CLOBIt is used for character data that is only a single byte long.4,294,967,295 bytes
NCLOBIt specifies single-byte or fixed-length multibyte national character set (NCHAR) data.4,294,967,295 bytes
RAW(size)It is used to specify raw binary data with variable length. It must have a maximum size defined.2000 bytes
LONGRAWIt is used to specify raw binary data with variable length.2 GB

MS Access Data Types

Microsoft Access provides various data types that can be used to define fields in a table. The following are commonly used data types in MS Access:

  • Text: Used for storing alphanumeric characters, such as names, addresses, and other text-based data.
  • Number: Used for storing numerical values, such as integers, decimals, and fractions.
  • Date/Time: Used for storing dates and times in a specific format.
  • Currency: Used for storing monetary values with two decimal places.
  • Yes/No: Used for storing Boolean values, which can be either True or False.
  • Memo: Used for storing large amounts of text data, such as notes or comments.
  • Attachment: Used for storing files, such as documents, images, or multimedia files, directly in a table.
  • Hyperlink: Used for storing web addresses or links to files or other data sources.
  • Lookup Wizard: Used for creating a list of values that users can choose from.
  • OLE Object: Used for storing objects created in other applications, such as Microsoft Word or Excel, directly in a table.

Conclusion

  • In an SQL server, a Data Type is defined as the type of data that any column or variable can store. It is a type of data that an object stores, such as an integer, character, string, and so on.
  • The type of data that a column or variable can store must be defined in advance. In addition, determining data type prevents the user from inputting any unexpected or erroneous data.
  • You can make better use of memory by assigning an appropriate data type to a variable or column, which will allocate only the amount of system memory required for the data in that column. I hope this tutorial has improved your understanding of data types in SQL.