SQL Data Types

SQL Data type defines the values that a column can accept, for example if a column of the table has an int data type, then it can only accept integer values. You can specify the data type of the columns while creating the table.

SQL Data Types classification

Data Types in SQL are classified in six different categories as follows:

1. Numeric Data Types
2. Approximate Numeric Data Types
3. Date and Time Data Types
4. Strings Data Types
5. Unicode Character Strings Data Types
6. Binary Data Types

1. Numeric Data Types

DATA TYPE	Range	Description
int	From -2,147,483,648 to 2,147,483,647	integer numbers
bigint	From -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	integer numbers just like int data type but with large range.
smallint	From -32,768 to 32,767	integer numbers but with smaller range than int data type.
tinyint	From 0 to 255	intger numbers but with smaller range than the smallint data type.
bit	0 or 1	True is converted to 1 and False is converted to 0
money	From -922,337,203,685,477.5808 to +922,337,203,685,477.5807	This data type is used for monetary units.

2. Approximate Numeric Data Types

DATA TYPE	Range	Description
float	From -1.79E + 308 to 1.79E + 308	Used for floating numbers, number that contain decimal points and fraction values.
real	From -3.40E + 38 to 3.40E + 38	Similar to float but with bigger range than float data type.

3. Date and Time Data Type

DATA TYPE	Range	Description
date	From January 1, 0001 to December 31, 9999	Stores a date like July 12, 2022
time	Stores a time like 1:30 PM	Stores time with an accuracy of 100 nanoseconds.
datetime	From Jan 1, 1753 to Dec 31, 9999	Used for storing data and time data. It has wider range than smalldatetime data type
smalldatetime	From Jan 1, 1900 to Jun 6, 2079	Similar to datetime but with smaller range.

4. Strings Data Types

DATA TYPE	Range	Description
char	Max size 8000 characters.	It is used to specify a fixed length string that can contain numbers, letters, and special characters.
varchar	Max size 8000 characters.	It is used to specify a variable length string that can contain numbers, letters, and special characters.
varchar(max)	Max size 1,073,741,824 characters	Similar to varchar, this is also used to specify a variable length string that can contain numbers, letters, and special characters.
text	Max size 2GB of text data.	It specifies variable width character string.

5. Unicode Character Strings Data Types

DATA TYPE	Range	Description
nchar	Max size 4000 characters.	It is used to specify a fixed length unicode string.
nvarchar	Max size 4000 characters.	It is used to specify a variable length unicode string.
nvarchar(max)	Max size 536,870,912 characters	Similar to varchar, this is also used to specify a variable length unicode string.
ntext	Max size 2GB of text data.	It specifies variable width unicode character string.

6. Binary Data Types

DATA TYPE	Range	Description
binary	Max size 8000 bytes.	It is used to specify a fixed length binary string.
varbinary	Max size 800 bytes.	It is used to specify a variable length binary string.
varbinary(max)	Max size 2GB	Similar to varchar, this is also used to specify a variable length binary string.
image	Max size 2GB.	It specifies variable width binary string.