SQL Server Data Types
Data types in SQL Server define the type of data that can be stored in a column, variable, parameter, or return value. Choosing the correct data type is crucial for data integrity, performance, and efficient storage. This document provides a comprehensive overview of the various data types available in SQL Server.
Categories of Data Types
SQL Server data types can be broadly categorized as follows:
- Exact Numerics: Integers and money values.
- Approximate Numerics: Floating-point numbers.
- Date and Time: Temporal values.
- Character Strings: Text data (Unicode and non-Unicode).
- Binary Strings: Raw binary data.
- Unicode Character Strings: Unicode text data.
- Other Data Types: Including date, time, spatial, XML, JSON, hierarchical, and unique identifiers.
Key Data Types Overview
Exact Numeric Data Types
These types provide exact precision for calculations.
| Data Type | Description | Storage Size |
|---|---|---|
BIT |
An integer that can be 0, 1, or NULL. Used for true/false or on/off values. | 1 bit |
TINYINT |
Unsigned integer. Range: 0 to 255. | 1 byte |
SMALLINT |
Signed integer. Range: -32,768 to 32,767. | 2 bytes |
INT |
Signed integer. Range: -2,147,483,648 to 2,147,483,647. | 4 bytes |
BIGINT |
Signed integer. Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. | 8 bytes |
DECIMAL(p, s) |
Fixed-precision decimal number. 'p' is precision (total digits), 's' is scale (digits after decimal). | Variable (up to 17-34 bytes) |
NUMERIC(p, s) |
Synonym for DECIMAL. |
Variable (up to 17-34 bytes) |
MONEY |
Monetary values. Range: -922,337,203,685,477.5807 to 922,337,203,685,477.5807. | 8 bytes |
SMALLMONEY |
Smaller monetary values. Range: -214,748.3647 to 214,748.3647. | 4 bytes |
Approximate Numeric Data Types
These types are used for scientific calculations where exact precision is not critical.
| Data Type | Description | Storage Size |
|---|---|---|
FLOAT(n) |
Floating-point number, where 'n' specifies precision (1-53 bits). | 4 or 8 bytes |
REAL |
Synonym for FLOAT(24). |
4 bytes |
Date and Time Data Types
Used to store date and time values with varying levels of precision.
| Data Type | Description | Storage Size |
|---|---|---|
DATE |
Stores date only. Range: '0001-01-01' through '9999-12-31'. | 3 bytes |
TIME(n) |
Stores time only, with optional fractional seconds precision 'n'. | 3-5 bytes |
DATETIME |
Stores date and time. Range: '1753-01-01' through '9999-12-31'. Precision: .000, .003, .007 seconds. | 8 bytes |
DATETIME2(n) |
Date and time with higher precision and wider date range than DATETIME. Precision 'n' up to 7 digits. |
6-8 bytes |
SMALLDATETIME |
Stores date and time. Range: '1900-01-01' through '2079-06-06'. Precision: Nearest minute. | 4 bytes |
DATETIMEOFFSET(n) |
Date and time with time zone offset. Precision 'n' up to 7 digits. | 8-10 bytes |
Character String Data Types
Store non-Unicode character data.
| Data Type | Description | Storage Size |
|---|---|---|
CHAR(n) |
Fixed-length, non-Unicode character string. 'n' is the length in bytes. Padded with spaces. | n bytes |
VARCHAR(n) |
Variable-length, non-Unicode character string. 'n' is the maximum length in bytes. | n + 2 bytes |
VARCHAR(MAX) |
Variable-length, non-Unicode character string up to 2 GB. | Variable |
TEXT |
Variable-length, non-Unicode character data. Deprecated in favor of VARCHAR(MAX). |
Variable |
Unicode Character String Data Types
Store Unicode character data (UTF-16 encoding).
| Data Type | Description | Storage Size |
|---|---|---|
NCHAR(n) |
Fixed-length Unicode character string. 'n' is the length in characters. Padded with spaces. | 2 * n bytes |
NVARCHAR(n) |
Variable-length Unicode character string. 'n' is the maximum length in characters. | 2 * n + 2 bytes |
NVARCHAR(MAX) |
Variable-length Unicode character string up to 2 GB. | Variable |
NTEXT |
Variable-length Unicode character data. Deprecated in favor of NVARCHAR(MAX). |
Variable |
Binary String Data Types
Store binary data.
| Data Type | Description | Storage Size |
|---|---|---|
BINARY(n) |
Fixed-length binary data. 'n' is the length in bytes. Padded with 0x00. | n bytes |
VARBINARY(n) |
Variable-length binary data. 'n' is the maximum length in bytes. | n + 2 bytes |
VARBINARY(MAX) |
Variable-length binary data up to 2 GB. | Variable |
IMAGE |
Variable-length binary data. Deprecated in favor of VARBINARY(MAX). |
Variable |
Other Important Data Types
UNIQUEIDENTIFIER: Stores a globally unique identifier (GUID).XML: Stores XML data.SQL_VARIANT: Stores values of various .NET Framework data types.HIERARCHYID: Represents position in a hierarchy.GEOGRAPHYandGEOMETRY: Spatial data types for storing geographic and planar data.JSON: Supported via string types (VARCHAR,NVARCHAR) with JSON functions.
Important Note: For optimal performance and storage efficiency, always select the smallest data type that can reliably store the required range of values. Avoid using
TEXT, NTEXT, and IMAGE in new development; prefer VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) respectively.
Example Usage
Creating a table with various data types:
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY(1,1),
ProductName VARCHAR(100) NOT NULL,
ProductDescription NVARCHAR(MAX),
Price DECIMAL(10, 2),
StockQuantity SMALLINT,
IsAvailable BIT DEFAULT 1,
LastUpdated DATETIME2(3)
);
Insert statement:
INSERT INTO Products (ProductName, ProductDescription, Price, StockQuantity, LastUpdated)
VALUES (
'Wireless Mouse',
N'Ergonomic wireless mouse with adjustable DPI.',
29.99,
150,
GETDATE()
);