SQL Server Data Types

This document provides a comprehensive overview of the data types supported by Microsoft SQL Server. Understanding data types is crucial for efficient database design, query performance, and data integrity.

Understanding Data Types

Data types define the kind of data that can be stored in a column. Each data type has specific properties that determine the type of values it can hold, the storage space it requires, and the operations that can be performed on it.

Numeric Data Types

These types are used to store numeric values.

Data Type Description Storage Range
TINYINT Unsigned integer from 0 to 255. 1 Byte 0 to 255
SMALLINT Signed integer. 2 Bytes -32,768 to 32,767
INT Signed integer. This is the most common integer type. 4 Bytes -2,147,483,648 to 2,147,483,647
BIGINT Signed integer. Used for very large numbers. 8 Bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL(p, s) Fixed precision and scale numeric value. Variable (up to 17 Bytes) Precision up to 38, Scale up to 38
NUMERIC(p, s) Synonym for DECIMAL. Variable (up to 17 Bytes) Precision up to 38, Scale up to 38
FLOAT(n) Approximate-number data type (floating-point). 'n' specifies precision. 4 or 8 Bytes Approximate
REAL Approximate-number data type (floating-point). 4 Bytes Approximate
MONEY Monetary data, with fixed precision of 4 decimal places. 8 Bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
SMALLMONEY Monetary data, with fixed precision of 4 decimal places. 4 Bytes -214,748.3647 to 214,748.3647

Date and Time Data Types

These types store date and time information.

Data Type Description Storage Range
DATE Stores a date. 3 Bytes January 1, 1753 to December 31, 9999
TIME(n) Stores a time of day. 3 to 5 Bytes 00:00:00.0000000 to 23:59:59.9999999
DATETIME Stores a date and time combination. 8 Bytes January 1, 1753 to December 31, 9999
DATETIME2(n) An enhanced version of DATETIME with greater precision. 6 to 8 Bytes January 1, 0001 to December 31, 9999
SMALLDATETIME Stores date and time combination with less precision than DATETIME. 4 Bytes January 1, 1900 to June 6, 2079
DATETIMEOFFSET(n) Stores date and time combination, including a time zone offset. 10 Bytes January 1, 0001 to December 31, 9999
JULIANDAY Stores the Julian Day number. 8 Bytes Julian Day number for a specific date.

String Data Types

These types are used for storing character data.

Data Type Description Storage Max Length
CHAR(n) Fixed-length non-Unicode character string. Padded with spaces. n Bytes 8,000 characters
VARCHAR(n) Variable-length non-Unicode character string. n + 2 bytes 8,000 characters
VARCHAR(MAX) Variable-length non-Unicode character string. Variable 2,147,483,647 characters
NCHAR(n) Fixed-length Unicode character string. Padded with spaces. 2n Bytes 4,000 characters
NVARCHAR(n) Variable-length Unicode character string. 2n + 2 bytes 4,000 characters
NVARCHAR(MAX) Variable-length Unicode character string. Variable 2,147,483,647 characters
TEXT Non-Unicode character data. Not recommended for new development. Variable 2,147,483,647 characters
NTEXT Unicode character data. Not recommended for new development. Variable 2,147,483,647 characters

Binary Data Types

These types store binary data.

Data Type Description Storage Max Length
BINARY(n) Fixed-length binary data. Padded with 0x. n Bytes 8,000 Bytes
VARBINARY(n) Variable-length binary data. n + 2 bytes 8,000 Bytes
VARBINARY(MAX) Variable-length binary data. Variable 2,147,483,647 Bytes
IMAGE Binary data. Not recommended for new development. Variable 2,147,483,647 Bytes

Other Data Types

A variety of other useful data types are available.

Data Type Description
BIT Stores 0, 1, or NULL. Used for true/false or yes/no values.
UNIQUEIDENTIFIER Stores a globally unique identifier (GUID).
XML Stores XML data.
GEOMETRY Stores spatial data representing geometric shapes.
GEOGRAPHY Stores spatial data representing geographic locations.
SQL_VARIANT Stores values of various data types supported by SQL Server.
TABLE Stores a result set for later processing.

Example Usage

Here's a simple example of how data types are used in a table definition:

CREATE TABLE Products
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(10, 2) DEFAULT 0.00,
    StockQuantity INT,
    LastUpdated DATETIME2 DEFAULT GETDATE()
);

Further Reading