SQL Server Documentation > T-SQL > T-SQL Data Types
T-SQL Data Types

This document provides a comprehensive overview of the data types supported by Transact-SQL (T-SQL) in Microsoft SQL Server. Understanding these data types is crucial for efficient database design, accurate data storage, and effective querying.

Overview of T-SQL Data Types

T-SQL data types define the kind of data that can be stored in a column, variable, parameter, or return value. They determine the storage size, the range of values, and the operations that can be performed on the data.

Categories of Data Types

T-SQL data types can be broadly categorized as follows:

Exact Numeric Data Types

These data types store numbers with a fixed precision and scale.

Data Type Description Storage Size Range
BIT Integer that can be 0, 1, or NULL. Used for true/false values. 1 bit 0, 1
TINYINT Unsigned integer. 1 byte 0 to 255
SMALLINT Signed integer. 2 bytes -32,768 to 32,767
INT Signed integer. (Most common integer type) 4 bytes -2,147,483,648 to 2,147,483,647
BIGINT Signed integer. 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL / NUMERIC Fixed-point number with specified precision and scale. Variable (up to 17-33 bytes) -1038 + 1 to 1038 - 1
MONEY Currency value. 8 bytes -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807
SMALLMONEY Smaller currency value. 4 bytes -214,748.3648 to 214,748.3647

Approximate Numeric Data Types

These data types store numbers that may have a fractional part, but precision can vary.

Data Type Description Storage Size Approximate Range
FLOAT Approximate floating-point number. Precision can be specified (24 or 53 bits). 4 or 8 bytes Approximately +/- 1.18e-38 to +/- 3.40e+38
REAL Approximate floating-point number (equivalent to FLOAT(24)). 4 bytes Approximately +/- 1.18e-38 to +/- 3.40e+38

Date and Time Data Types

Store temporal information with varying precision.

Data Type Description Storage Size Range
DATE Stores date only. 3 bytes '0001-01-01' to '9999-12-31'
TIME Stores time only. Precision is configurable. 3 to 5 bytes '00:00:00.0000000' to '23:59:59.9999999'
DATETIME Stores date and time. Lower precision and range. 8 bytes '1753-01-01 00:00:00.000' to '9999-12-31 23:59:59.997'
SMALLDATETIME Stores date and time. Lower precision and range than DATETIME. 4 bytes '1900-01-01 00:00:00' to '2079-06-06 23:59:59'
DATETIME2 Stores date and time. Higher precision and wider range than DATETIME. 6 to 8 bytes '0001-01-01 00:00:00.0000000' to '9999-12-31 23:59:59.9999999'
DATETIMEOFFSET Stores date and time with time zone offset. 8 to 10 bytes '0001-01-01 00:00:00.0000000' to '9999-12-31 23:59:59.9999999' with an offset of -14:30 to +14:00

Character String Data Types

Store non-Unicode character data.

Data Type Description Storage Size Max Length
CHAR Fixed-length character string. Pads with spaces. n bytes 8,000 characters
VARCHAR Variable-length character string. n bytes + 2 bytes overhead 8,000 characters
VARCHAR(MAX) Variable-length character string. Variable (up to 2 GB) 2 GB
TEXT Variable-length character data. (Deprecated, use VARCHAR(MAX)) Variable (up to 2 GB) 2 GB

Unicode Character String Data Types

Store Unicode character data, supporting a wider character set.

Data Type Description Storage Size Max Length
NCHAR Fixed-length Unicode character string. 2n bytes 4,000 characters
NVARCHAR Variable-length Unicode character string. 2n bytes + 2 bytes overhead 4,000 characters
NVARCHAR(MAX) Variable-length Unicode character string. Variable (up to 2 GB) 2 GB
NTEXT Variable-length Unicode character data. (Deprecated, use NVARCHAR(MAX)) Variable (up to 2 GB) 2 GB

Binary String Data Types

Store raw binary data.

Data Type Description Storage Size Max Length
BINARY Fixed-length binary data. Pads with NULL bytes. n bytes 8,000 bytes
VARBINARY Variable-length binary data. n bytes + 2 bytes overhead 8,000 bytes
VARBINARY(MAX) Variable-length binary data. Variable (up to 2 GB) 2 GB
IMAGE Variable-length binary data. (Deprecated, use VARBINARY(MAX)) Variable (up to 2 GB) 2 GB

Other Data Types

A variety of other useful data types are available.

Data Type Description
UNIQUEIDENTIFIER Globally unique identifier (GUID).
XML Stores XML data.
GEOGRAPHY Stores spatial data representing locations on a globe.
GEOMETRY Stores spatial data representing geometric shapes in a Euclidean plane.
TABLE A special type used for table variables in T-SQL.
HIERARCHYID Stores hierarchical data.
Tip: When choosing a data type, consider the range of values, precision requirements, and the type of data being stored. Using the most appropriate data type can significantly improve performance and reduce storage space. For character data, prefer Unicode types (NVARCHAR) to support international characters, unless storage is extremely critical and only ASCII characters are ever expected.

Common T-SQL Data Type Conversions

T-SQL supports implicit and explicit data type conversions. Implicit conversions happen automatically, while explicit conversions require using functions like CAST or CONVERT.

Explicit Conversion Examples:

-- Convert an integer to a string
SELECT CAST(123 AS VARCHAR(10));

-- Convert a date to a string in a specific format
SELECT CONVERT(VARCHAR, GETDATE(), 103); -- DD/MM/YYYY format

-- Convert a string to a decimal
SELECT CAST('123.45' AS DECIMAL(5, 2));

Always be mindful of potential data loss or errors during conversions. It is generally recommended to use explicit conversions when ambiguity exists or when precise control over the conversion process is needed.

Important: Be cautious when converting between numeric and string types, especially with floating-point numbers, as precision can be lost.

For more detailed information on specific data types, their properties, and advanced usage, please refer to the official Microsoft SQL Server documentation.