A Data Type defines the type of data that can be stored in a table column.
Examples include: integer, date, money, string, decimal, binary, and others.
SQL Server data types fall into five categories.
These data types store numbers and numberic data.
| Data Type | Description |
|---|---|
| BIT | Boolean type with values 0 (false), 1 (true), or NULL. Default is 1. |
| TINYINT | Tiny whole numbers from 0 to 255. |
| SMALLINT | Small whole numbers from -32,768 to 32,768. |
| INT | Whole numbers from -2,147,483,648 to 2,147,483,648. |
| BIGINT | Large whole numbers from -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,808. |
| DECIMAL | Fixed-point and scale number from -10^38 +1 to 10^38 +1. |
| NUMERIC | Same as decimal. Fixed-point and scale from -10^38 +1 to 10^38 +1. |
| FLOAT | Floating point number from -1.79E + 308 to 1.79E + 308. |
| REAL | Floating point number from -3.40E + 38 to 3.40E + 38. |
| SMALLMONEY | Monetary value from -214,748.3648 to 214,748.3647. |
| MONEY | Monetary value from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. |
These types store datetime, date, and time data.
| Data Type | Description |
|---|---|
| DATE |
Date in YYYY-MM-DD format. Supported range is from 1000-01-01 to 9999-12-31. |
| TIME |
Time in hh:mm:ss[.nnnnnnn] format. Supported range is from 00:00:00.0000000 to 23:59:59.9999999. |
| DATETIME2 |
Date and time in YYYY-MM-DD hh:mm:ss[.nnnnnnn] format. Date range is 0001-01-01 to 9999-12-31. Time range is 00:00:00 to 23:59:59.9999999. |
| DATETIMEOFFSET |
Datetime with timezone in YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] format. Date range is 0001-01-01 to 9999-12-31. Time range is 00:00:00 to 23:59:59.9999999. Timezone range is -14:00 to +14:00. |
| SMALLDATETIME |
Date and time in YYYY-MM-DD hh:mm:ss format. Date range is 1900-01-01 to 2079-06-06. Time range is 00:00:00 to 23:59:59. |
| DATETIME |
Date and time in YYYY-MM-DD hh:mm:ss[.nnn] format. Date range is 1753-01-01 to 9999-12-31. Time range is 00:00:00 to 23:59:59.997. |
These types store text data.
| Data Type | Description | Max Size |
|---|---|---|
| CHAR | Fixed length character string | 8,000 characters |
| NCHAR | Fixed length Unicode string | 4,000 characters |
| VARCHAR | Variable-length character string | 8,000 characters |
| NVARCHAR | Variable-length Unicode string | 4,000 characters |
| VARCHAR(Max) |
Variable-size character storage The max indicates maximum characters |
2GB of data |
| NVARCHAR(Max) |
Variable-size Unicode storage The max indicates maximum characters |
2GB of data |
| TEXT | Variable-length character string | 2GB of data |
| NTEXT | Variable-length Unicode string | 1GB of data |
These types store binary data.
| Data Type | Description | Max Size |
|---|---|---|
| BINARY | Fixed length binary string | 8,000 bytes |
| VARBINARY | Variable length binary storage | 8,000 bytes |
| VARBINARY(Max) |
Variable length binary storage The max indicates maximum characters |
2GB of data |
| IMAGE | Variable length binary image storage | 2GB of data |
These are other types supported by SQL Server.
| Data Type | Description | Max Size |
|---|---|---|
| ROWVERSION | Binary unique number. Used for version stamping. | 8 bytes |
| UNIQUEIDENTIFIER | An autogenerated GUID (globally unique identifier). | 16 bytes |
| XML | Stores XML data | 2GB of data |
