Data Types in SQL

Introduction

In an SQL Server table, each parameter, column, local expression, and local variable has a corresponding data type to be stored. A data type is an attribute that describes the sort of data that an object can carry, such as integers, characters, money, dates and times, binary strings, and so on.

In simple words, the type of value that can be stored in a table column is defined by SQL Data Types. 
For example: Consider if we want a column to contain only float values, then we can assign its data type as a float.

Recommended topics, Coalesce in SQL and  Tcl Commands in SQL

SQL Data Types

In a database table, each column is required to have a name and a data type. A data type belonging to a column determines what type of value it can store: integer, character, binary, date and time, and so forth. The data type is a guideline for SQL to comprehend what type of data should be anticipated in each column, as well as how SQL should interact with the stored data.

When an operator combines two expressions with distinct data types, collations, precision, scale, or length, the following character traits of the result are determined:

1. The result's data type is decided by applying the data type precedence rules to the input expressions' data types.
2. When the result data type is char, nchar, nvarchar, varchar, text, or ntext, the collation is determined by the rules of collation precedence.
3. The precision, size, and length of the output expressions are determined by the input expressions' precision, scale, and length.

The following categories can be used to classify SQL data types:

 

1. Exact-Numeric data types such as bigint, tinyint, smallint, etc.
2. Approximate-Numeric data types such as float and real.
3. Date and Time data which includes Date, Time, smalldatetime, etc.
4. Character Strings data types such as char, varchar, text, etc.
5. Unicode character Strings data types such as nchar, nvarchar, ntext, etc.
6. Binary Data types such as binary, varbinary, image.
7. Miscellaneous data types - sql_variant, xml, cursor, etc.

 

Exact Numeric Data Type

These are values when precision and scale must be maintained INTEGER, BIGINT, DECIMAL, NUMERIC, NUMBER, and MONEY are the exact numeric kinds.

Data Type	Description	Lower limit	Upper limit	Memory
bigint	It saves whole numbers in the specified range.	−2^63 (−9,223,372, 036,854,775,808)	2^63−1 (−9,223,372, 036,854,775,807)	8 bytes
int	It saves whole numbers in the specified range.	−2^31 (−2,147, 483,648)	2^31−1 (−2,147, 483,647)	4 bytes
smallint	It saves whole numbers in the specified range.	−2^15 (−32,767)	2^15 (−32,768)	2 bytes
tinyint	It saves whole numbers in the specified range.	0	255	1 byte
bit	It can take 0, 1, or NULL values.	0	1	1 byte/8bit column
decimal	Scale and fixed precision numbers are used.	−10^38+1	10^381−1	5 to 17 bytes
numeric	Scale and fixed precision numbers are used.	−10^38+1	10^381−1	5 to 17 bytes
money	Used monetary data	−922,337, 203, 685,477.5808	+922,337, 203, 685,477.5807	8 bytes
smallmoney	Used monetary data	−214,478.3648	+214,478.3647	4 bytes

 

 

Example:

DECLARE @Exact_Data_type_Decimal DECIMAL (3,2) = 2.31 PRINT @Exact_Data_type_Decimal

Output:

2.31

 

Approximate-Numeric Data Type

These are values where precision must be maintained but the scale can be arbitrary DOUBLE PRECISION, FLOAT, and REAL are the approximate numeric types.

Data Type	Description	Lower limit	Upper limit	Memory	Precision
float	A floating precision number is represented by this symbol.	−1.79E+308	1.79E+308	Depends on the value of n	7 Digit
real	A floating precision number is represented by this symbol.	−3.40E+38	3.40E+38	4 bytes	15 Digit

 

Note: The n parameter shows out whether the field should hold 4 or 8 bytes. A 4-byte field is stored in float(24), and an 8-byte field is stored in float(53).  And the default value of n is set to be 53.

 

Example :

DECLARE @approx_Data_type_Float FLOAT(24) = 22.1234 PRINT @approx_Data_type_Float

Output:

22.1234

 

Date & Time Data Type

This section covers the data types that Snowflake supports for maintaining dates, times, and timestamps (combined date + time). It also explains the supported formats for string constants used in date, time, and timestamp manipulation.

Data Type	Description	Storage size	Accuracy	Lower Range	Upper Range
DateTime	From January 1, 1753 to December 31, 9999, this format is used to specify a date and time. It has a 3.33 millisecond precision.	8 bytes	Rounded to increments of .000, .003, .007	1753-01-01	9999-12-31
smalldatetime	From January 1, 0001 through December 31, 9999, this value is used to represent a date and time. It is accurate to within 100 nanoseconds.	4 bytes, fixed	1 minute	1900-01-01	2079-06-06
date	Only dates from January 1, 0001 to December 31, 9999 were stored.	3 bytes, fixed	1 day	0001-01-01	9999-12-31
time	Only time values with a precision of 100 nanoseconds are stored.	5 bytes	100 nanoseconds	00:00:00.0000000	23:59:59.9999999
datetimeoffset	It's similar to datetime, but with a  time zone offset.	10 bytes	100 nanoseconds	0001-01-01	9999-12-31
datetime2	From January 1, 0001 through December 31, 9999, this number is used to denote a date and time.	6 bytes	100 nanoseconds	0001-01-01	9999-12-31

 

Example:

DECLARE @date_Data_type_Date DATE = '2030-01-01' PRINT @date_Data_type_Date

Output:

‘2030-01-01’

Also see, SQL EXCEPT

Character Strings Data Type

Strings of letters, numbers, and symbols are stored. Character string types are made up of the data types CHARACTER (CHAR) and CHARACTER VARYING (VARCHAR), and the values of character string types are called character strings.

Fixed-length or variable-length strings can be used to hold character data. Variable-length strings are not extended; fixed-length strings are right-extended with spaces on output.

In SQL statements, string literals must be enclosed in single quotes.

Data Type	Description	Lower limit	Upper limit	Memory
char	It's a character string with a predetermined length. It can hold up to 8,000 characters.	0 chars	8000 chars	n bytes
varchar	This is a variable-width character string.	0 chars	8000 chars	n bytes + 2 bytes
varchar (max)	This is a variable-width character string. It can hold up to 1,073,741,824 characters.	0 chars	2^31 chars	n bytes + 2 bytes
text	This is a variable-width character string. It can hold up to 2GB of text data.	0 chars	2,147,483,647 chars	n bytes + 4 bytes

 

Example  :

DECLARE @char_Data_type_Char VARCHAR(30) = 'This is Character Datatype' PRINT @char_Data_type_Char

Output:

This is Character Datatype

 

Unicode Character Strings Data Type

The nchar and nvarchar Unicode data types are used to store Unicode data in UTF-16 format. They behave in the same way that char and varchar do. The strings are encoded as single-byte or multibyte forms dependent on the value of the II CHARSETxx variable in the case of char and varchar. The char and varchar strings are encoded as UTF-8 strings if II CHARSETxx is set to UTF8. Unicode data is represented by UTF-8 and UTF-16 encoded strings.

Data Type	Description	Lower limit	Upper limit	Memory
nchar	It's a fixed-width Unicode string.	0 chars	4000 chars	2 times n bytes
nvarchar	It's a variable-length unicode string.	0 chars	4000 chars	2 times n bytes + 2 bytes
ntext	It's a variable-length unicode string.	0 chars	1,073,741,823 char	2 times the string length

 

 

Example :

DECLARE @unic_Data_type_nChar VARCHAR(30) = 'This is nCharacter Datatype' PRINT @unic_Data_type_nChar

Output:

This is a nCharacter Datatype

 

Binary Data Type

Up to 65000 bytes of raw byte data, such as IP addresses, can be stored. Types of data Binary string types include BINARY and BINARY VARYING (VARBINARY), and binary string values are known as binary strings. A binary string is an octet or byte sequence.

Data Type	Description	Lower limit	Upper limit	Memory
binary	It's a binary string with a fixed length. It has a maximum storage capacity of 8,000 bytes.	0 bytes	8000 bytes	n bytes
varbinary	This is a variable-length binary string. It can hold up to 8,000 bytes.	0 bytes	8000 bytes	The actual length of data entered + 2 bytes
image	This is a variable-length binary string. It has a maximum storage capacity of 2GB.	0 bytes	2,147,483,647 bytes

 

Example :

DECLARE @binary_Data_type BINARY(2) = 12; PRINT @binary_Data_type

Output:

0x000C

 

Misc Data Type

Different data types that do not fall into the categories of string data types, binary data types, date and time, or numeric data types are referred to as miscellaneous or other data types in SQL.

Data Type	Description
Cursor	It produces an sp cursor list and sp describe cursor column as output.It returns the cursor variable's name.
Row version	It stamps table rows in this version.
Hierarchyid	This data type represents a level in the Conversion
Uniqueidentifier	From a character expression, convert.
Sql_variant	It saves the values of Datatypes that are supported by SQL Server.
XML	It uses a column to hold XML data.
Spatial Geometry type	It uses a flat coordinate system to represent data.
Spatial Geography type	It is a coordinate system that represents data in the round-earth coordinate system.
table	It saves a set of results for later use.

 

Some points to remember

Some data types in SQL Server are classified into the following classes based on their storage characteristics:

• Large value data types: nvarchar(max) and varchar(max)
• Large object data types: varbinary(max), text, ntext, image, and xml
   

Note: sp_help returns -1 as the length for the xml and large-value data types.

Must Read SQL Clauses