T-SQL Data Types Reference
This section provides detailed information about the data types supported by Transact-SQL (T-SQL) in SQL Server.
Introduction to Data Types
Data types define the type of data that a column, variable, expression, or parameter can hold. SQL Server supports a wide range of data types to accommodate various storage and manipulation needs. Choosing the appropriate data type is crucial for efficient storage, accurate calculations, and proper data integrity.
Categories of Data Types
T-SQL data types can be broadly categorized as follows:
Exact Numeric Data Types
These data types store exact numeric values. They are ideal for financial calculations and precise measurements.
| Data Type | Description | Storage | Range |
|---|---|---|---|
BIGINT |
Large integer data. | 8 bytes | -263 to 263-1 |
DECIMAL and NUMERIC |
Fixed-point number precision. | Variable (up to 17-34 bytes) | -1038+1 to 1038-1 |
INT |
Integer data. | 4 bytes | -231 to 231-1 |
SMALLINT |
Small integer data. | 2 bytes | -32,768 to 32,767 |
TINYINT |
Very small integer data. | 1 byte | 0 to 255 |
MONEY |
Monetary values. | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
SMALLMONEY |
Small monetary values. | 4 bytes | -214,748,364.87 to +214,748,364.87 |
Approximate Numeric Data Types
These data types store approximate numeric values. They are suitable for scientific calculations where a high degree of precision is not required.
| Data Type | Description | Storage | Approximate Range | Approximate Precision |
|---|---|---|---|---|
FLOAT |
Floating-point number data. | 4 or 8 bytes | ±1.79E-308 to ±1.79E+308 | 15 digits |
REAL |
Floating-point number data. | 4 bytes | ±1.18E-38 to ±3.40E+38 | 7 digits |
Date and Time Data Types
These data types store date and time information.
| Data Type | Description | Storage | Range |
|---|---|---|---|
DATE |
Stores date only. | 3 bytes | 0001-01-01 through 9999-12-31 |
TIME |
Stores time only. | 3 to 5 bytes | 00:00:00.0000000 through 23:59:59.9999999 |
DATETIME |
Stores date and time. | 8 bytes | Jan 1, 1753 through Dec 31, 9999 |
DATETIME2 |
An ANSI SQL-compliant date and time data type. | 6 to 8 bytes | 01-01-0001 through 12-31-9999 |
SMALLDATETIME |
Stores date and time. | 4 bytes | Jan 1, 1900 through Jun 6, 2079 |
DATETIMEOFFSET |
Stores date and time with time zone offset. | 8 to 10 bytes | 01-01-0001 through 12-31-9999 |
String Data Types
These data types store character strings.
| Data Type | Description | Storage |
|---|---|---|
CHAR |
Fixed-length non-Unicode character string. | N bytes |
VARCHAR |
Variable-length non-Unicode character string. | N bytes + 2 bytes overhead |
NCHAR |
Fixed-length Unicode character string. | 2N bytes |
NVARCHAR |
Variable-length Unicode character string. | 2N bytes + 2 bytes overhead |
TEXT |
Variable-length non-Unicode character data. (Deprecated) | Variable |
NTEXT |
Variable-length Unicode character data. (Deprecated) | Variable |
Binary Data Types
These data types store binary data.
| Data Type | Description | Storage |
|---|---|---|
BINARY |
Fixed-length binary string. | N bytes |
VARBINARY |
Variable-length binary string. | N bytes + 2 bytes overhead |
IMAGE |
Variable-length binary data. (Deprecated) | Variable |
Other Data Types
Includes various other data types for specific purposes.
| Data Type | Description | Storage |
|---|---|---|
BIT |
Integer bit. (0, 1, or NULL) | 1 bit |
UNIQUEIDENTIFIER |
Globally unique identifier (GUID). | 16 bytes |
XML |
XML data. | Variable |
SQL_VARIANT |
Stores values of various other data types. | Variable |
GEOMETRY |
Spatial data type for geometric data. | Variable |
GEOGRAPHY |
Spatial data type for geographic data. | Variable |
HIERARCHYID |
Hierarchical data type. | Variable |
TABLE |
Table-valued parameter or variable. | Variable |
Note: Some older data types like TEXT, NTEXT, and IMAGE are deprecated and should be replaced with their modern equivalents (e.g., VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)).
Tip: For optimal performance and storage, always choose the smallest data type that can reliably store your data. For example, use SMALLINT instead of INT if your values will not exceed 32,767.