Top SQL data types you need to understand
When designing databases with SQL Server, choosing the right data type is essential for data accuracy, efficient storage, performance tuning, and long-term maintainability. This guide covers all the major SQL Server data types, grouped by category, with extended use cases and example code.
Learn SQL online
1. Exact Numeric Data Types
These data types are designed for storing numbers that require exact precision, such as identifiers, counts, and monetary values.
| Data Type | Storage | Range |
|---|---|---|
int | 4 bytes | -2,147,483,648 to 2,147,483,647 |
bigint | 8 bytes | ±9.2 quintillion |
smallint | 2 bytes | -32,768 to 32,767 |
tinyint | 1 byte | 0 to 255 |
bit | 1 bit (internally 1 byte) | Boolean (0 or 1) |
decimal(p,s) / numeric(p,s) | Varies by p | Fixed-point, exact precision |
Use Cases
int: Often used for primary keys, foreign keys, and auto-incrementing identity columns (IDENTITY). It is the default integer type for general-purpose numeric values like counts, order numbers, or user IDs.bigint: Ideal for very large datasets, like high-volume transaction IDs, log record IDs, distributed system IDs, or scenarios whereintmay overflow.smallintandtinyint: Suitable for lookup tables, status codes, flags, or rating scales where you don’t need a large range. For example,tinyintcan be used for values likeUserLevel (0 = Guest, 1 = User, 2 = Admin).bit: Used for Boolean-style flags likeIsActive,IsDeleted,IsArchived. It’s space-efficient and self-documenting.decimal/numeric: Critical for financial systems, scientific calculations, or inventory applications where exact decimal representation is needed to avoid rounding errors. For example, storing currency (Price decimal(18, 2)), tax rates, or measurement data.
Code Example
CREATE TABLE Product (
ProductID int PRIMARY KEY,
Price decimal(10, 2),
IsAvailable bit,
InventoryCount smallint,
GlobalId bigint
);
2. Approximate Numeric Data Types
Used to represent real numbers with a wide range, but not guaranteed to be exact due to binary floating-point representation.
| Data Type | Storage | Precision |
|---|---|---|
float(n) | 4–8 bytes | Up to 15 digits |
real | 4 bytes | ~7-digit precision (float(24)) |
Why Use Approximate Numerics? And Their Limitations
Floating-point numbers are suited for storing very small or very large numbers such as scientific measurements, probabilities, machine learning predictions, or simulation results.
However, due to their binary representation, values like
0.1or0.3cannot be precisely stored. This results in tiny rounding errors, which can compound in aggregations or comparisons.
Avoid using float or real for currency or values requiring high accuracy—use decimal instead.
Use Cases
Scientific and Engineering Data: Storing gravitational constants, voltages, frequencies, etc., where precision is less important than range.
Sensor Data / IoT: Temperatures, pH levels, or humidity readings that may not require exact decimals.
Probabilistic Models: Storing model output probabilities (e.g.,
0.812345...) where small variations are tolerable.Data Mining/Analytics: Fast and rough estimation calculations where small rounding errors are acceptable.
Code Example
CREATE TABLE SensorReadings (
ReadingId int IDENTITY PRIMARY KEY,
Temperature float,
SignalStrength real
);
Why Aren’t Floating-Point Numbers Precise?
Floating-point numbers are stored in binary format using the IEEE 754 standard, which divides the number into three parts:
Sign bit (positive or negative)
Exponent (controls magnitude)
Mantissa or significand (controls precision)
Due to this representation:
Some decimal numbers cannot be exactly represented in binary. For example:
Just as 1/3 cannot be precisely expressed in decimal (
0.333...), the number 0.1 cannot be represented exactly in binary floating point.It becomes something like:
0.0001100110011...(a repeating binary fraction), which must be truncated or rounded.
This leads to rounding errors, even if they’re very small:
-- This will return false
SELECT CASE WHEN 0.1 + 0.2 = 0.3 THEN 'Equal' ELSE 'Not Equal' END;
-- Output: 'Not Equal'
Why You Should Avoid float / real for Currency or Accounting
Currency requires exactness to the last decimal — e.g., $19.99 must always mean exactly 19 dollars and 99 cents. But with floating point:
SELECT CAST(19.99 AS float) * 100;
-- May return 1998.999999... instead of 1999
This is unacceptable in:
Invoicing systems
Payroll
Banking
Tax reporting
Instead, always use decimal(p,s) for these scenarios.
3. Date and Time Data Types
SQL Server offers specialized types for representing dates, times, and timestamps, with varying precision and storage requirements.
| Data Type | Description |
|---|---|
date | Stores only the calendar date |
datetime | Stores date + time to the nearest 1/300 second |
smalldatetime | Less precise and smaller date range |
datetime2 | High precision datetime (100ns increments) |
datetimeoffset | Adds time zone offset |
time | Stores time of day (without date) |
Use Cases
date: Best for birthdates, anniversaries, registration dates, or any column where time-of-day is irrelevant.datetime: Suitable for general-purpose timestamps, like order creation times or login events. Common in legacy systems.datetime2: Ideal for event logging, financial transaction timestamps, or high-frequency systems (e.g., stock tickers) where higher time precision is needed.datetimeoffset: Required when working across multiple time zones, such as booking systems, international apps, or server synchronization.smalldatetime: Good for archive tables or historical logs where precision is not critical and you want to save space.time: Useful in scheduling apps, shifts, or time-based events likeShiftStartTimeandLunchBreakEnd.
Code Example
CREATE TABLE EventSchedule (
EventId int,
EventName varchar(100),
EventDate date,
StartTime time,
ScheduledAt datetime2,
CoordinatedTime datetimeoffset
);
4. Character String Data Types
These types are used to store alphanumeric data in ASCII format. They are efficient and flexible for handling textual data.
| Data Type | Description |
|---|---|
char(n) | Fixed-length string |
varchar(n) | Variable-length string |
text | Large text blob (deprecated) |
Use Cases (Expanded)
char(n): Good for fixed-format codes like country codes (US,FR), postal codes, SSNs, or check digits where length is always the same.varchar(n): Ideal for general textual input like user names, addresses, comments, product descriptions, etc. It saves space by storing only the characters needed.text: Historically used for large content like articles or logs. Deprecated—usevarchar(max)instead.
Code Example
CREATE TABLE Customer (
CustomerId int,
CountryCode char(2),
Name varchar(100),
Notes text -- Deprecated
);
5. Unicode Character String Data Types
These data types support multilingual text using UTF-16 encoding, allowing you to store characters from any language.
| Data Type | Description |
|---|---|
nchar(n) | Fixed-length Unicode |
nvarchar(n) | Variable-length Unicode |
ntext | Large Unicode text (deprecated) |
Use Cases (Expanded)
nchar(n): Use in multilingual apps where you store fixed-length codes (e.g., language codes, country abbreviations) and need Unicode support.nvarchar(n): Recommended for storing names, addresses, and text where internationalization is a concern (e.g., Japanese, Arabic, Russian).ntext: For large Unicode documents like policy texts, product manuals, or internationalized blog content. Deprecated in favor ofnvarchar(max).
Code Example
CREATE TABLE InternationalProduct (
ProductId int,
Name nvarchar(100),
Description nvarchar(max)
);
6. Binary Data Types
These types store raw binary data, including images, files, encrypted values, and serialized content.
| Data Type | Description |
|---|---|
binary(n) | Fixed-length binary |
varbinary(n) | Variable-length binary |
image | Large binary object (deprecated) |
Use Cases (Expanded)
binary(n): Suitable for fixed-size binary identifiers, hash values, or binary flags (e.g., storing SHA-256 digests).varbinary(n)/varbinary(max): Use for file uploads, profile images, PDFs, video/audio content, or certificate storage.image: Deprecated, was previously used for storing large images or documents.
Code Example
CREATE TABLE UserProfile (
UserId int,
Avatar varbinary(max),
PasswordHash binary(64)
);
Summary: Best Practices for Data Type Selection
| Principle | Guideline |
|---|---|
| Performance | Prefer smaller data types (tinyint, smallint) when appropriate |
| Precision Matters | Use decimal, not float, for currency and accounting |
| Internationalization | Use nvarchar for global/multilingual apps |
| Storage Optimization | Avoid fixed-length types unless necessary |
| Modern SQL | Use varchar(max), nvarchar(max) instead of text, ntext |
| Future-Proofing | Avoid deprecated types; adopt datetime2, varbinary(max), etc. |
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
