SQL Server Data Types

This tutorial provides a comprehensive overview of the data types available in Microsoft SQL Server. Understanding data types is crucial for efficient database design, data integrity, and performance optimization.

Understanding Data Types

Data types define the kind of data a column can hold, such as numbers, strings, dates, or binary values. Each data type has specific characteristics regarding storage size, precision, and allowed values.

Categories of Data Types

SQL Server data types can be broadly categorized as follows:

Exact Numeric Data Types

These types represent precise numerical values.

Data Type	Description	Storage
`TINYINT`	Small unsigned integer.	1 byte
`SMALLINT`	Signed integer.	2 bytes
`INT`	Standard signed integer.	4 bytes
`BIGINT`	Large signed integer.	8 bytes
`DECIMAL(p, s)` / `NUMERIC(p, s)`	Fixed-precision decimal values. `p` is total digits, `s` is digits after decimal.	Variable (up to 17 bytes)
`MONEY`	Monetary values.	8 bytes
`SMALLMONEY`	Smaller monetary values.	4 bytes

Approximate Numeric Data Types

These types represent floating-point numbers, which are not always exact.

Data Type	Description	Storage
`FLOAT(n)`	Approximate floating-point number. `n` is precision in bits (1-53).	4 or 8 bytes
`REAL`	Approximate floating-point number (equivalent to `FLOAT(24)`).	4 bytes

Date and Time Data Types

These types store date and time information.

Data Type	Description	Storage
`DATE`	Stores date (YYYY-MM-DD).	3 bytes
`TIME(n)`	Stores time of day. `n` is fractional seconds precision.	3 to 5 bytes
`DATETIME`	Stores date and time (less precise, larger range).	8 bytes
`DATETIME2(n)`	Stores date and time (more precise, larger range). `n` is fractional seconds precision.	6 to 8 bytes
`SMALLDATETIME`	Stores date and time (less precise, smaller range).	4 bytes
`DATETIMEOFFSET(n)`	Stores date and time with time zone offset.	8 to 10 bytes

String Data Types

These types store character or binary strings.

Data Type	Description	Storage
`CHAR(n)`	Fixed-length non-Unicode character string. Pads with spaces.	`n` bytes
`VARCHAR(n)`	Variable-length non-Unicode character string.	`n` + 2 bytes
`VARCHAR(MAX)`	Variable-length non-Unicode character string (up to 2 GB).	Variable
`NCHAR(n)`	Fixed-length Unicode character string. Pads with spaces.	2 * `n` bytes
`NVARCHAR(n)`	Variable-length Unicode character string.	2 * `n` + 2 bytes
`NVARCHAR(MAX)`	Variable-length Unicode character string (up to 2 GB).	Variable
`BINARY(n)`	Fixed-length binary data. Pads with zeros.	`n` bytes
`VARBINARY(n)`	Variable-length binary data.	`n` + 2 bytes
`VARBINARY(MAX)`	Variable-length binary data (up to 2 GB).	Variable

Other Data Types

Includes types for spatial data, XML, JSON, and more.

UNIQUEIDENTIFIER: Globally unique identifier (GUID).
XML: Stores XML data.
JSON: Stores JSON data (requires SQL Server 2016+).
GEOMETRY, GEOGRAPHY: For spatial data.
BIT: Stores 0, 1, or NULL.
ROWVERSION: Binary number that is guaranteed to be unique within a database.

Choosing the Right Data Type

Selecting appropriate data types is critical for:

Data Integrity: Ensures data conforms to expected formats and constraints.
Storage Efficiency: Uses the minimum necessary storage space.
Performance: Affects query speed, especially for indexing and sorting.
Accuracy: Prevents data loss or misinterpretation.

Example: Creating a Table with Various Data Types

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE, Salary DECIMAL(10, 2), IsActive BIT DEFAULT 1, HireDate DATETIME2(0), EmployeeGUID UNIQUEIDENTIFIER DEFAULT NEWID() );

Common Pitfalls

Using VARCHAR(MAX) or NVARCHAR(MAX) when a smaller, fixed size would suffice.
Using floating-point types (FLOAT, REAL) for financial calculations where precision is paramount; prefer DECIMAL or NUMERIC.
Choosing DATETIME over DATETIME2 for new development due to its limitations.
Not considering character encoding (VARCHAR vs. NVARCHAR).

For more detailed information, refer to the official SQL Server Data Types documentation.