SQL Server Data Types
The following sections describe the data types supported by SQL Server.
SQL Server Data Types
The data types supported by the Microsoft SQL Server can be grouped into three main categories: string, numeric and date/time data types.
String Data Types
String data types are normally used to store names, addresses, descriptions or any value that contains letters and numbers including binary data, like image or audio files.
| Data type | Description |
|---|---|
char(n) |
Stores fixed-length character string. Maximum length 8,000 characters. |
varchar(n) |
Stores variable-length character string. Maximum length 8,000 characters. |
varchar(max) |
Stores variable-length character string. Here, max indicates that the maximum storage size is 2 GB. |
text |
Stores variable-length character string. Maximum storage size is 2 GB. |
nchar |
Stores fixed-length Unicode string. Maximum length 4,000 characters. |
nvarchar |
Stores variable-length Unicode string. Maximum length 4,000 characters. |
nvarchar(max) |
Stores variable-length Unicode string. Here, max indicates that the maximum storage size is 2 GB. |
ntext |
Stores variable-length Unicode string. Maximum storage size is 2 GB. |
binary(n) |
Stores fixed-length binary data. Maximum storage size is 8,000 bytes. |
varbinary(n) |
Stores variable-length binary data. Maximum storage size is 8,000 bytes. |
varbinary(max) |
Stores variable-length binary data. Here, max indicates that the maximum storage size is 2 GB. |
image |
Stores variable-length binary data. Maximum storage size is 8,000 bytes. |
Numeric Data Types
Numeric data types are normally used to store data like price, salary etc.
| Data type | Description |
|---|---|
bit |
Allows you to store a value 1, 0, or NULL. |
tinyint |
Stores integer values in the range from 0 to 255. |
smallint |
Stores integer values in the range from -32,768 to 32,767. |
int |
Stores integer values in the range from -2,147,483,648 to 2,147,483,647. |
bigint |
Stores integer values in the range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. |
decimal(p,s) |
Stores fixed precision and scale numbers. Valid values are from 10^38 +1 through 10^38 - 1. See below for more details. |
numeric(p,s) |
The numeric datatype is functionally equivalent to decimal. |
smallmoney |
Allows you to store monetary or currency values accurately in the range from -214,748.3648 to 214,748.3647. |
money |
Allows you to store monetary or currency values accurately in the range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. |
float(n) |
Stores floating point numeric values. Valid values are from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308. |
real |
Stores floating point numeric values. Valid values are from -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. |
When declaring a or decimal or numeric column, the precision and scale can be specified, like decimal(p,s) or numeric(p,s), where the p or precision indicates the maximum total number of 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 to 38. The default precision is 18.
Whereas, the s or scale indicates the maximum number of digits that can be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 to p. The default scale is 0.
For example, the price decimal(6,2) column can store any value with six digits and two decimals i.e. in the range from -9999.99 to 9999.99.
Date and Time Data Types
Date and Time data types are normally used to store data like date of birth, hiring date, date and time when a record is created or updated inside table, etc.
| Data type | Description |
|---|---|
date |
Stores a date value in the range from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999). |
time |
Stores time of a day with the accuracy of 100 nanoseconds. Valid values are from 00:00:00.0000000 to 23:59:59.9999999. |
datetime |
Stores a combined date and time value with an accuracy of 3.33 milliseconds. Valid date range for datetime is from 1753-01-01 (January 1, 1753) to 9999-12-31 (December 31, 9999). |
datetime2 |
The datetime2 is an extension of the datetime data type that has a larger date range. Valid date range for datetime2 is from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999). |
smalldatetime |
Stores a combined date and time value with an accuracy of 1 minute. Valid date range for smalldatetime is from 1900-01-01 (January 1, 1900) to 2079-06-06 (June 6, 2079). |
datetimeoffset |
Same as datetime2 with the addition of a time zone offset. Default format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. Valid range for time zone offset is from -14:00 to +14:00. |
timestamp |
In SQL Server timestamp is the synonym for the rowversion data type which automatically generate, unique binary numbers within a database. The rowversion is generally used for version-stamping table rows. |
Note: Every time that a row with a rowversion column is inserted or modified inside a table, the incremented database rowversion value is inserted in the rowversion column. A table can have only one rowversion column.
