Understanding T-SQL Data Types
Data types define the kind of data that can be stored in a column, variable, or parameter. Choosing the correct data type is crucial for data integrity, storage efficiency, and query performance in SQL Server.
Numeric Data Types
These types are used for storing numerical values.
Exact Numeric Types
| Data Type | Storage Size | Range | Description |
|---|---|---|---|
TINYINT |
1 byte | 0 to 255 | Unsigned integer. |
SMALLINT |
2 bytes | -32,768 to 32,767 | Signed integer. |
INT |
4 bytes | -2,147,483,648 to 2,147,483,647 | Signed integer (most common). |
BIGINT |
8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | Signed integer for very large numbers. |
DECIMAL(p, s) |
Variable (up to 17 bytes) | Depends on precision (p) and scale (s) | Fixed-point number. p is total digits, s is digits after decimal. |
NUMERIC(p, s) |
Variable (up to 17 bytes) | Depends on precision (p) and scale (s) | Synonym for DECIMAL. |
MONEY |
8 bytes | -922,337,203,685,477.5808 to 9,223,372,036,854,775.807 | Monetary values, fixed precision of 4 decimal places. |
SMALLMONEY |
4 bytes | -214,748.3648 to 214,748.3647 | Smaller monetary values. |
Approximate Numeric Types
| Data Type | Storage Size | Precision | Description |
|---|---|---|---|
FLOAT(n) |
4 or 8 bytes | Variable (7 or 15 digits) | Approximate floating-point number. n is precision. |
REAL |
4 bytes | Variable (7 digits) | Approximate floating-point number (equivalent to FLOAT(24)). |
Character String Types
Used for storing text data.
| Data Type | Storage | Max Length | Description |
|---|---|---|---|
CHAR(n) |
n bytes |
8000 characters | Fixed-length non-Unicode string. Padded with spaces. |
VARCHAR(n) |
n + 2 bytes overhead |
8000 characters | Variable-length non-Unicode string. |
VARCHAR(MAX) |
Variable | 2^31 - 1 characters | Variable-length non-Unicode string for large text. |
NCHAR(n) |
2 * n bytes |
4000 characters | Fixed-length Unicode string. Padded with spaces. |
NVARCHAR(n) |
2 * n + 2 bytes overhead |
4000 characters | Variable-length Unicode string. |
NVARCHAR(MAX) |
Variable | 2^31 - 1 characters | Variable-length Unicode string for large text. |
TEXT |
Variable | 2^31 - 1 characters | Deprecated. Use VARCHAR(MAX) instead. |
NTEXT |
Variable | 2^31 - 1 characters | Deprecated. Use NVARCHAR(MAX) instead. |
Date and Time Types
For storing temporal data.
| Data Type | Storage Size | Range | Accuracy |
|---|---|---|---|
DATE |
3 bytes | '0001-01-01' through '9999-12-31' | Days |
TIME[(p)] |
3 to 5 bytes | '00:00:00.0000000' through '23:59:59.9999999' | Fractional seconds (up to 7 decimal places). |
DATETIME |
8 bytes | '1753-01-01' through '9999-12-31' | 3.33 milliseconds |
SMALLDATETIME |
4 bytes | '1900-01-01' through '2079-06-06' | 1 minute |
DATETIME2[(p)] |
6 to 8 bytes | '0001-01-01' through '9999-12-31' | Fractional seconds (up to 7 decimal places). |
DATETIMEOFFSET[(p)] |
8 to 10 bytes | '0001-01-01' through '9999-12-31' | Fractional seconds (up to 7 decimal places), plus timezone offset. |
Binary String Types
For storing raw binary data.
| Data Type | Storage | Max Length | Description |
|---|---|---|---|
BINARY(n) |
n bytes |
8000 bytes | Fixed-length binary data. Padded with zeros. |
VARBINARY(n) |
n + 2 bytes overhead |
8000 bytes | Variable-length binary data. |
VARBINARY(MAX) |
Variable | 2^31 - 1 bytes | Variable-length binary data for large amounts. |
IMAGE |
Variable | 2^31 - 1 bytes | Deprecated. Use VARBINARY(MAX) instead. |
Other Data Types
A variety of other useful data types.
| Data Type | Storage Size | Description |
|---|---|---|
BIT |
1 bit (stored as 1 byte) | Stores 0, 1, or NULL. Often used for boolean flags. |
UNIQUEIDENTIFIER |
16 bytes | Stores GUIDs (Globally Unique Identifiers). |
XML |
Variable | Stores XML data. XML(n) is not supported. |
HIERARCHYID |
Variable | Represents position in a hierarchy. |
Special Data Types
Types that handle specific types of structured or spatial data.
| Data Type | Storage | Description |
|---|---|---|
GEOMETRY |
Variable | Represents data in a geometric coordinate system. |
GEOGRAPHY |
Variable | Represents data on a ellipsoidal (e.g., Earth) surface. |
TABLE |
Variable | Used for table-valued parameters and variables. Returns a table. |
SQL_VARIANT |
Variable | Can store values of various other SQL Server-supported data types. |
XMLDATA |
Variable | Stores XML data. |
Tip: For character data, prefer
NVARCHAR over VARCHAR if you need to store international characters or a wider range of symbols. VARCHAR(MAX) and NVARCHAR(MAX) are generally preferred over the deprecated TEXT and NTEXT types. For numeric precision, DECIMAL or NUMERIC are ideal for financial calculations.
Choosing the Right Data Type
- Storage Efficiency: Use the smallest data type that can accommodate the expected range of values.
- Data Integrity: Select types that accurately represent the data (e.g.,
DATEfor dates,BITfor true/false). - Performance: Smaller data types generally lead to faster queries and less I/O. Avoid unnecessary type conversions.
- Unicode vs. Non-Unicode: Use Unicode types (
NCHAR,NVARCHAR) when dealing with multiple languages or special characters.
Understanding these data types is fundamental to building robust and efficient SQL Server databases.