Transact-SQL Data Types

This section provides detailed information about the data types supported by Transact-SQL (T-SQL), the procedural language extension for SQL Server.

Introduction

Data types define the kind of data that a column, variable, expression, or parameter can hold. Choosing the correct data type is crucial for data integrity, performance, and efficient storage. SQL Server offers a wide range of data types to accommodate various data needs.

Common Data Types

Here's a summary of some frequently used data types:

Data Type	Description	Storage
`INT`	Integer data.	4 bytes
`BIGINT`	Large integer data.	8 bytes
`DECIMAL(p, s)` / `NUMERIC(p, s)`	Fixed-precision and scale numeric data. 'p' is precision, 's' is scale.	Variable
`FLOAT` / `REAL`	Approximate-precision floating-point numbers.	4 or 8 bytes
`VARCHAR(n)`	Variable-length non-Unicode character data. 'n' is the maximum length.	n + 2 bytes overhead
`NVARCHAR(n)`	Variable-length Unicode character data. 'n' is the maximum length.	2n + 2 bytes overhead
`DATE`	Date storage.	3 bytes
`DATETIME`	Date and time storage.	8 bytes
`BIT`	Boolean (0, 1, or NULL).	1 byte
`UNIQUEIDENTIFIER`	Globally unique identifier (GUID).	16 bytes
`VARBINARY(n)`	Variable-length binary data. 'n' is the maximum length.	n + 2 bytes overhead

Detailed Categories

Exact Numeric Data Types

These data types store exact numeric values. They are suitable for financial calculations and other scenarios where precision is critical.

BIGINT
DECIMAL and NUMERIC
INT
MONEY and SMALLMONEY
SMALLINT
TINYINT
BIT

Approximate Numeric Data Types

These data types store approximate numeric values. They are suitable for scientific calculations where exact precision is not required.

FLOAT
REAL

Date and Time Data Types

Used to store dates and times. The choice depends on the required range and precision.

BIGINT
DATE
DATETIME
DATETIME2
DATETIMEOFFSET
SMALLDATETIME
TIME

Character String Data Types

For storing text data. Choose between Unicode and non-Unicode, and fixed or variable length.

CHAR(n)
VARCHAR(n)
CHAR(n) (Unicode)
VARCHAR(n) (Unicode)
TEXT (deprecated)
NCHAR(n)
NVARCHAR(n)
NTEXT (deprecated)

Binary String Data Types

For storing raw binary data.

BINARY(n)
VARBINARY(n)
IMAGE (deprecated)

Other Data Types

Includes types for specific purposes like unique identifiers, spatial data, and XML.

UNIQUEIDENTIFIER
XML
SQL_VARIANT
HIERARCHYID
GEOMETRY
GEOGRAPHY
CURSOR
TABLE

Choosing the Right Data Type

Consider the following when selecting a data type:

Data Range: Ensure the data type can accommodate the minimum and maximum values your data will contain.
Precision: For numeric data, choose between exact and approximate types based on your needs.
Storage Efficiency: Smaller data types use less disk space and memory, improving performance.
Character Encoding: Use Unicode types (NVARCHAR, NCHAR) if your data might contain characters from multiple languages or symbols.
Nullability: Decide if a column can contain NULL values.

Example Usage

Here's how you might declare variables with different data types in T-SQL:


DECLARE @EmployeeID INT = 101;
DECLARE @FirstName VARCHAR(50) = 'Alice';
DECLARE @HireDate DATE = '2023-01-15';
DECLARE @Salary DECIMAL(10, 2) = 75000.50;
DECLARE @IsActive BIT = 1;

SELECT @EmployeeID AS EmployeeID,
       @FirstName AS FirstName,
       @HireDate AS HireDate,
       @Salary AS Salary,
       @IsActive AS IsActive;