'Datatype' is a attributes or format of the type of data that a column can hold in a table. It is related to objects as well.
The 'Datatypes' can be of type integer data, character data, monetary data, date and time data, binary types, unicode strings etc.
When a particular column is defined as a particular datatype, it means that it will only accept data that follows certain characteristics
pertaining to that datatype. In SQL Server, The datatypes are classified into the following:
bit |
0 |
1 |
1 to 8 bit columns in same table= 1 byte, 9 to 16 bits = 2 bytes,etc.. |
|
tinyint |
0 |
255 |
1 byte |
Whole numbers between the given range |
smallint |
-32,768 |
to 32,767 |
2 bytes |
Whole numbers between the given range |
int |
-2,147, 483,648 |
2,147,483,647 |
4 bytes |
Whole numbers between the given range |
bigint |
-9,223,372, 036, 854,775,808 |
9,223,372,036,854, 775,807 |
8 bytes |
Whole numbers between the given range |
decimal (precision, scale type) |
|
|
5-17 bytes |
The maximum total number of decimal digits that can be stored, both to the left and to the right
of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision value is 18
The precision expression in the decimal indicates the maximum length of digits that can be stored (both to the left and to the right of the decimal point). Precision has a default value of 18 and ranges from 1 to 38.
The scale expression in the decimal indicates the maximum length of digits stored to the right of the decimal point. Scale has a default value of 0 and ranges from 0 to precision's value. |
numeric (precision, scale type) |
|
|
5-17 bytes |
The maximum total number of decimal digits that can be stored, both to the left and to the right
of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision value is 18
The precision expression in the decimal indicates the maximum length of digits that can be stored (both to the left and to the right of the decimal point). Precision has a default value of 18 and ranges from 1 to 38.
The scale expression in the decimal indicates the maximum length of digits stored to the right of the decimal point. Scale has a default value of 0 and ranges from 0 to precision's value. |
smallmoney |
-214,748.3648 |
214,748.3647 |
4 bytes |
|
money |
-922,337,203,685, 477.5808 |
922,337,203,685, 477.5807 |
8 bytes |
|
float |
-1.79E + 308 |
1.79E + 308 |
4 bytes when precision is < 25 and 8 bytes when precision > 25. |
Precision is specified from 1 to 53 |
real |
- 3.40E + 38 |
3.40E + 38 |
4 bytes |
Precision is specified from 1 to 53 |
char |
0 characters |
8000 characters |
storage size is the defined length. |
Character string defined as char (n) where n is the string length |
nchar |
0 characters |
4000 characters |
storage size is the defined length. |
Unicode string defined as nchar(n) where n is the string length |
varchar |
0 characters |
8000 characters |
storage size is the actual length of the data entered + 2 bytes |
Character string defined as varchar( n ) where n is the string length |
varchar(max) |
0 characters |
8000 characters |
storage size is 2GB |
Character string defined as varchar( max ). To be used when the size of the column data varies considerably and the size might exceed 8,000 bytes |
nvarchar |
0 characters |
4000 characters |
storage size is two times the actual length of the data entered + 2 bytes |
Unicode string defined as nvarchar( n ) where n is the string length |
nvarchar(max) |
0 characters |
4000 characters |
storage size is 2GB |
Character string defined as nvarchar( max ). To be used when the size of the column data varies considerably and the size might exceed 8,000 bytes |
text |
0 characters |
2,147,483,647 characters |
storage size is number of characters + 4 bytes |
Do not use this as it is getting phased out in future versions of SQL Server. Use varchar(max) instead |
ntext |
0 characters |
1,073,741,823 characters |
storage size is 2 times the string length entered |
Do not use this as it is getting phased out in future versions of SQL Server. Use nvarchar(max) instead |
image |
0 bytes |
2,147,483,647 bytes |
|
Do not use this as it is getting phased out in future versions of SQL Server. Use varbinary(max) instead |
binary |
0 bytes |
8000 bytes |
Storage size is the length defined |
Defined as binary( n ) where n is the length defined in bytes. |
varbinary |
0 bytes |
8000 bytes |
Storage size is the length defined |
Defined as binary( n ) where n is the length defined in bytes. |
Smalldatetime |
1900-01-01 00:00 |
2079-06-06 23:59 |
4 bytes |
Accuracy is one minute |
datetime |
January 1, 1753 00:00:00 |
December 31, 9999 23:59:59 |
8 bytes |
Accuracy is 007 seconds |
datetime2 |
0001-01-01 00:00:00 |
9999-12-31 23:59:59 |
6 bytes |
Accuracy is 100 nanoseconds |
date |
0001-01-01 |
9999-12-31 |
3 bytes |
Accuracy is one day |
time |
00:00:00.0000000 |
23:59:59.9999999 |
5 bytes |
Accuracy is 100 nanoseconds |
datetimeoffset |
0001-01-01 00:00:00 |
9999-12-31 23:59:59 |
10 bytes |
Time zone offset range ia -14:00 through +14:00. Accuracy is 100 nanoseconds. |
cursor |
|
|
|
contains a reference to a cursor |
sql_variant |
|
|
|
various SQL Server-supported data types values can be stored in this data type |
HierarchyId |
|
|
|
can store values that represent nodes in a hierarchy tree. |
uniqueidentifier |
|
|
16 bytes |
It is a unique identifier column holding a GUID string of 32 random characters in blocks separated by hyphens. |
xml |
|
|
2 GB |
it helps SQL Server hold XML data in a column. |
table |
|
|
|
It can store the resultset of T-SQL statements for processing to be done later. It is used in user defined functions(UDF). |
geometry |
|
|
|
It allows you to store planar data about physical locations (distances, vectors, etc.. |
geography |
|
|
|
It allows you to store round earth data such as latitude and longitude coordinates. |