This article contains a quick reference of the system-supplied data types available in SQL Server 2017, along with some basic information such as maximum length in bytes, precision, scale, and whether the data type is nullable or not (1 means it’s nullable, 0 means it’s not nullable).
Exact numerics
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| bigint | 8 | 19 | 0 | 1 |
| bit | 1 | 1 | 0 | 1 |
| decimal | 17 | 38 | 38 | 1 |
| int | 4 | 10 | 0 | 1 |
| money | 8 | 19 | 4 | 1 |
| numeric | 17 | 38 | 38 | 1 |
| smallint | 2 | 5 | 0 | 1 |
| smallmoney | 4 | 10 | 4 | 1 |
| tinyint | 1 | 3 | 0 | 1 |
Approximate numerics
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| float | 8 | 53 | 0 | 1 |
| real | 4 | 24 | 0 | 1 |
Date and time
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| date | 3 | 10 | 0 | 1 |
| datetime2 | 8 | 27 | 7 | 1 |
| datetime | 8 | 23 | 3 | 1 |
| datetimeoffset | 10 | 34 | 7 | 1 |
| smalldatetime | 4 | 16 | 0 | 1 |
| time | 5 | 16 | 7 | 1 |
Character strings
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| char | 8000 | 0 | 0 | 1 |
| text | 16 | 0 | 0 | 1 |
| varchar | 8000 | 0 | 0 | 1 |
Unicode character strings
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| nchar | 8000 | 0 | 0 | 1 |
| ntext | 16 | 0 | 0 | 1 |
| nvarchar | 8000 | 0 | 0 | 1 |
Binary strings
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| binary | 8000 | 0 | 0 | 1 |
| image | 16 | 0 | 0 | 1 |
| varbinary | 8000 | 0 | 0 | 1 |
Other data types
| Name | Max Length | Precision | Scale | Nullable? |
|---|---|---|---|---|
| hierarchyid | 892 | 0 | 0 | 1 |
| sql_variant | 8016 | 0 | 0 | 1 |
| geometry | -1 | 0 | 0 | 1 |
| geography | -1 | 0 | 0 | 1 |
| timestamp* | 8 | 0 | 0 | 0 |
| uniqueidentifier | 16 | 0 | 0 | 1 |
| xml | -1 | 0 | 0 | 1 |
| sysname | 256 | 0 | 0 | 0 |
* The timestamp data type is a synonym for the rowversion data type. See below for more information.
The ‘cursor’, ‘table’ and ‘rowversion’ Types
I retrieved the information in the above tables via the sys.types system catalog view. In addition to the above types, Microsoft’s documentation on data types also lists the cursor and table types, as well as rowversion instead of timestamp.
The ‘timestamp’ Type
Microsoft advises that the timestamp syntax is deprecated. It’s recommended that we now use rowversion instead of timestamp wherever possible in our DDL statements.
However, in the meantime, you might find columns still appear to be defined as timestamp even when you explicitly specify rowversion when creating them (using Transact-SQL). Nevertheless, Microsoft advises that this is the correct syntax to use going forward.
Also note that timestamp in T-SQL/SQL Server is different from the timestamp data type defined in the ISO standard.
It appears that the reason it was deprecated is due to the confusion its name caused. Microsoft acknowledged this in 2007, and advised that they would try to address it in a backwards-compatible way in future releases.
Check your Own Database
You can retrieve data type information for your own databases by using the sys.types system view. See How to Return a List of Data Types in SQL Server for an explanation and examples. Running this view also returns any alias and user-defined types that are in the database.