SQL Data Types
In SQL DATA TYPES, each column holds value. Data types can be an integer type, character type, etc., and such data is stored in the database table.
The data type is a source for SQL columns to understand which kind of records to be added to the table columns. Suppose we have table name employee, and we store employee id then we have to declare the numeric data type.
SQL DATA TYPES are classified into three different types:
1. String Data Types.
2. Numeric Data Types.
3. Data and Time Data Types.
Now, we will discuss the types of datatypes one by one. So, Let’s start with String data type.
1 String Data Type: -
String Data types are related to character type, allowing the user to define the character data type, which can be variable length or fixed length.
(I). Char(n): -
Char(n) is a non-Unicode character. Char(n) is the Fixed-length character string data type.
(II). Varchar(n): -
Varchar(n) is non-Unicode data. Varchar(n) is a character string data type of variable length.
(III). Varchar(max): -
Varchar(max) is a non-Unicode data. Varchar(max) is character string data type of a variable length. Varchar(max) can store a max of 1,073,741,824 characters.
(IV). Text: -
Text is non-Unicode data. Text is the character string data type of a variable length. The text data type can store a maximum of 2,147,483,647 characters.
Let’s discuss Unicode Character String Data Types.
(V). nchar: -
nchar is a Unicode Character String Data Types of fixed length. nchar can store a max of 4000 characters.
(VI). nvarchar: -
nvarchar is a Unicode character String Data Types of variable length. nvarchar stores a max of 4000 characters.
(VII). nvarchar(max): -
nvarchar(max) is a Unicode character String Data Types of variable length. nvarchar(max) stores a max of 536,870,912 characters.
(VIII). ntext: -
ntext is a Unicode character String Data Types of variable length. ntext stores a max of 1,073,741,823 characters.
The data types below are binary string types containing a binary string of fixed or variable length.
(IX). Binary: -
Binary is a fixed-length binary string data type. The binary data type can store a max of 8000 bytes.
(X). VarBinary: -
VarBinary is a variable-length binary string data type. The VarBinary can store a max of 8000 bytes.
(XI). Image: -
Image is a variable-length binary string data type. Image stores a max of 2,147,483,647 bytes.
2. Numeric Data Types: -
(I). Bit: -
The Bit is an integer that can take 0, 1 and null values.
(II). Tinyint: -
In tinyint, the whole number stores whose range is from 0 to 255.
(III). Smallint: -
Smallint stores wholes number in the range given between -32,768 and 32,767.
(IV). Int: -
Int stores whole number in the range given between -2,147,483,648 and 2,147,483,647.
(V). Bigint: -
Bigint stores whole number in the range given between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
(VI). Float(n): -
The Float used to declare float precision number data start from -1.79E + 308 and ends at 1.79E + 308. The n argument in the float data types specifies the field that can hold 4 or 8 bytes. 53 is the default value of parameter n.
(VII). Real: -
The Real data type is used for floating precision data numbers from -3.40E+38 to 3.40E+38.
(VIII). Money: -
Money is used to mention monetary records from -922,337,233,685,477.5808 to 922,337,233,685,477.5807.
(IX). Small money: -
The Small money is used to specify monetary data from -214,748.3648 to 214,748.3647.
(X). Decimal (p, s): -
Decimal is used to specify fixed precision numbers and scale precision numbers.
Decimal allow numbers from -10^38 + 1 to 10^38-1. The parameter p in the decimal data type mentions the total topmost number of digits that can store. P defines a value between 1 to 38. The default value of p is 18.
The parameter s specifies the topmost number of digits stored to the right of the decimal point. S defines a value between 0 to p. Default value of s is 0.
(XI). Numeric (p, s): -
Numeric is used to specify fixed precision numbers and scale precision numbers.
Numeric allow numbers from -10^38 + 1 to 10^38-1. The parameter p in the numeric data type mentions total topmost number of digits that can store. P defines a value between 1 to 38. The default value of p is 18.
The parameter s specifies the topmost number of digits stored to the right of the decimal point. S defines a value between 0 to p. Default value of s is 0.
3. Date and Time Data Types: -
The date and time type of data is stored using the date and time data type.
(I). Datetime: -
Datetime is used to frame date and time, which support range from 1 January 1753 to 31 December 9999 with an accuracy of 3.33ms.
(II). Datetime2: -
Datetime2 is used to frame date and time, which support range from 1 January 0001 to 31 December 9999 with an accuracy of 100ns.
(III). Date: -
The Date data type is used to store data only, which support ranges from 1 January 0001 to 31 December 9999.
(IV). Time: -
Time data types use to store a time only with an accuracy of 100ns.