SQL Server (Transact-SQL) Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL Server to understand what type of data is expected inside of each column, and it also identifies how SQL Server will interact with the stored data.
In SQL Server (Transact-SQL), the data types can be mainly classified into four categories:
- String Data Types
- Numeric Data Types
- Date and time Data Types
- Other Data Types
String Data Types
| Data type | Description |
|---|
| CHAR(size) | A fixed width character string data type. Its size can be up to 8000 characters. |
| VARCHAR(size) | A variable width character string data type. Its size can be up to 8000 characters. |
| VARCHAR(max) | A variable width character string data types. Its size can be up to 231-1 characters. |
| TEXT | A variable width character string data type. Its size can be up to 2GB of text data. |
| NCHAR(size) | A fixed width Unicode string data type. Its size can be up to 4000 characters. |
| NVARCHAR(size) | A variable width Unicode string data type. Its size can be up to 4000 characters. |
| NVARCHAR(max) | A variable width Unicode string data type. Its size can be up to 231-1 characters. |
| NTEXT | A variable width Unicode string data type. Its size can be up to 2GB of text data. |
| BINARY(size) | A fixed Binary string data type. Its size can be up to 8000 bytes. |
| VARBINARY(size) | A variable width Binary string data type. Its size can be up to 8000 bytes. |
| VARBINARY(max) | A variable width Binary string data type. Its size can be up to 231-1 characters. |
| IMAGE | A variable width Binary string data type. Its size can be up to 2GB. |
Numeric Data Types
| Data type | Description |
|---|
| BIT | An integer that can be 0, 1 or null. |
| TINYBIT | A whole numbers from 0 to 255. |
| SMALLINT | A whole numbers between -32,768 and 32,767. |
| INT | A whole numbers between -2,147,483,648 and 2,147,483,647. |
| BIGINT | A whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| FLOAT(n) | A floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53. |
| REAL | A floating precision number data from -3.40E+38 to 3.40E+38. |
| SMALLMONEY | Used to specify monetary data from - 214,748.3648 to 214,748.3647. |
| MONEY | Used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
| DECIMAL(p, s) | A Fixed precision and scale numbers. The maximum total number of decimal digits to be stored is specified by p. The number of decimal digits that are stored to the right of the decimal point is specified by s. When maximum precision is used, valid values are from - 1038 +1 through 1038 - 1. |
| DEC(p, s) | Identical to DECIMAL(p, s). |
| NUMERIC(p, s) | Identical to DECIMAL(p, s). |
Date and Time Data Type
| Data type | Description |
|---|
| DATETIME | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.mmm]'. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
| DATETIME2 | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
| DATE | A date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999. |
| TIME | A time. Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'. Used to store time only to an accuracy of 100 nanoseconds. Values range from '00:00:00.0000000' to '23:59:59.9999999'. |
| DATETIMEOFFSET | A date and time combination and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
| SMALLDATETIME | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss'. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. |
Other Data Types
| Data type | Description |
|---|
| sql_variant | Used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
| XML | Stores XML formatted data. Maximum 2GB. |
| cursor | Stores a reference to a cursor used for database operations. |
| hierarchyid | A variable length, system data type. Used to represent position in a hierarchy. |
| rowversion | Generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. |
| table | Stores result set for later processing. |
| uniqueidentifier | Stores GUID (Globally unique identifier). |
| Spatial Geometry Types | Used to represent data in a flat coordinate system. |
| Spatial Geography Types | Used to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. |
