This tutorial covers SQL Data Types in detail.
Computer programs deal with data. Data has various forms. For example,
Name – Text string
Age – Numeric
Date Of Birth – Date
Data Type is a classification of data to inform compilers/interpreters how the data is intended to be used within the program. Programming languages deal with different types of data.
Similarly, database software also supports various types of data.The data type of every column within a database table need to be specified at the time of table creation so that each field store only a specific type of data.
Table of Contents
Generic SQL Data Types
SQL Data types define what type of data a column can contain. Each variable and column in SQL have a related data type such as,
In this section of SQL tutorial, we will look into various SQL data types.The following lists explain various data types available in SQL.
This is a list of general SQL data types which may not be completely supported by every SQL bases database.
Numeric Data Types in SQL
SQL Numeric Data Types are the attributes for data types in sql that are related to numerical values. Data types that store numerical values are Real, Numeric, Decimal, Float and Integer.
General functions associated with this data type include SUM, MAX, AVG, COUNT and MIN.
|SQL Data type||Description|
|integer||Integer with default Precision 10|
|integer(p)||Integer with Precision p|
|decimal(p,s)||The decimal data type can store exact numerical data with precision p and scale s. Example: DECIMAL(6,3) – a number with total 6 digits. 3 digits before the decimal point and 3 digits after the decimal point.|
|numeric(p,s)||Same as decimal, numeric is to store numeric number with fixed precision and scale. Decimal and numeric data types can be used interchangeably.|
|float(p)||float data types is known as an approximate data type.Approximate numeric data types store an extremely close approximation of the value not the exact value.p is the matissa precision.|
|float||float is approximate numerical datatype with mantissa(coefficient) precision 16|
|real||real is an approximate numerical data type with mantissa precision 7|
|double precision||double precision also an approximate numerical with precision 16|
Since float and real are approximate values, don’t use float or real type columns in search commands with the comparison operators = and <>. But it is good to use float and real columns to > or < comparisons.
See below the ranges of values each numeric data type can store,
|SQL Data Type||Ranging From||Ranging To|
|decimal||-10^38 +1||10^38 -1|
|float||-1.79E + 308||1.79E + 308|
|real||-3.40E + 38||3.40E + 38|
|numeric||-10^38 +1||10^38 -1|
Character Data Types in SQL
The SQL Character Data Types are for storing character strings of fixed or variable length,
Certain Character Data Types support Unicode character also.
You need to specify the length of the character string in datatype declaration statement.
Example: char(n),n signifies the chosen length of the string character .
Listed the various character string data types below,
|SQL Data Type||Description|
|char||Fix-length up to 8,000 characters,,non-Unicode data|
|varchar||Max 8,000 characters, variable-length, non-Unicode.|
|varchar(max)||max 231 characters, variable-length non-Unicode data|
|text||Variable-length non-Unicode data of up to 2,147,483,647 characters.|
|Unicode Character Data Types|
|nchar||Fix-length , up to 4,000 characters, Unicode data allowed|
|nvarchar||Variable-length Unicode data up to 4,000 characters.|
|nvarchar (max)||Variable-length Unicode data maximum 231 characters|
|ntext||Variable-length Unicode data up to 1,073,741,823 characters.|
Binary Data Types
SQL Binary data types are to store binary data of either fixed length or variable length.
Use binary data type in SQL when the sizes of the column data entries are fixed.
Use varbinary data type in SQL when the size of data to store in the columns vary significantly.
Use varbinary(max) type in SQL when size of the column data exceed 8,000 bytes.
|binary||For storing Fixed-length binary data. The maximum length of 8,000 bytes.|
|varbinary||For storing variable length binary data.Max length is 8,000 bytes.|
|varbinary(max)||For storing variable length binary data.Maximum length to be set.|
|image||To store images as variable length binary data.Maximum length of 2,147,483,647 bytes.|
Date-Time Data Types in SQL
For storing date values. Format: yyyy-mm-dd
For storing time values. Format: hh:mi:ss
To store values which are combinations of date and time . Format: yyyy-mm-dd hh:mi:ss[.nnn]
To store combinations of date and time in format YYYY-MM-DD hh: mm: ss
This is a kind of extension to DateTime type. Datetime2 is a date type joined with a time of day that is based on 24-hour clock.
|Data type||Format||Range From||Range To|
|datetime2||YYYY-MM-DD hh:mm:ss[.nnnnnnn]||0001-01-01 00:00:00.0000000||9999-12-31 23:59:59.9999999|
MONEY and SMALLMONEY Data Types in SQL
MONEY and SMALLMONEY data types are suitable for storing money and do the operation such as in accounting.The money and smallmoney are accurate to a ten-thousandth of the monetary units that they represent.Use a period(.) to separate monetary units, like cents, from a whole monetary unit.For example, 3.75 specifies 3 dollars and 75 cents.
Note: while we can specify monetary values preceded by a currency symbol, the database only stores the numeric data, It doesn’t store the currency information associated with the symbol. Any one of the following currency symbols can be used with these data types.
Image Credit : https://msdn.microsoft.com
Money data type range is from 922,337,203,685,477.5808 to +922,337,203,685,477.5807
Smallmoney range is from -214,748.3648 to +214,748.3647
Other DataTypes in SQL
table Is a special SQL data type, used to store a result set for processing at a later point in time.Table type is mainly used for temporary storage of row sets returned as the result set of a table-valued function. Functions and variables can be declared as table type. Table variables can be used in batches, stored procedures, and functions,
Syntax to declare variables as data type table is, DECLARE @local_variable.
You can store xml data in a column or a variable in the database.We can use the xml data type as a column type while creating a table as a variable type, a function-return type, a parameter type, or in CAST and CONVERT.
sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. A column of type sql_variant can store rows of different data types(except text, ntext, and timestamp). For example, a column defined as sql_variant can store char, int, and binary values.
Maximum length of sql_variant is 8016 bytes, including base type information and the base type value. Actual base type value’s maximum length is 8,000 bytes.
Before involving in operations such as addition and subtraction sql_variant data type need to be cast to its base data type
The timestamp is a data type that stores a database-wide unique number that gets updated every time a row gets updated. A timestamp data type is only an incrementing number.It doesn’t reserve a date or a time. timestamp data type is generally used as a mechanism for version-stamping table rows. The storage size of timestamp data type is 8 bytes.
uniqueidentifier data type in SQL:
This data type is used to store a globally unique identifier (GUID).Normally a uniqueidentifier data type can be initialized by using the NEWID function.there are other ways also.
Cursor data type in SQL:
Reference to a cursor object. Variables created with the cursor data type are nullable. You cannot use cursor data type for a column in a Create Table statement.