Datatypes Available for Table Columns

Every column in every SQL Server table must be declared with a specific datatype, a label that tells the engine what kind of value the column holds, numeric, text, date, binary, and so on, and how much disk space to reserve for it. Choosing a datatype is a balancing act: pick one too small and you risk truncated data or rejected inserts; pick one too large, or more precise than you actually need, and you waste storage across every row in the table, multiplied by however many rows it ends up holding. The tables in this lesson walk through the datatypes SQL Server 2025 makes available, organized by category, along with the range of values and storage space each one requires, so you can match each column to the type that genuinely fits it.

An (n) next to a datatype name below means the size is something you specify yourself when you declare the column; DECIMAL(p,s)'s precision and scale work similarly, and are covered in more depth in the next lesson.

Use an exact numeric type whenever precision has to be preserved exactly, financial amounts, quantities, identifiers, with no floating-point rounding involved.

Datatype	Range	Storage
BIT	0, 1, or NULL	1 byte per 8 BIT columns in a row
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 bytes
INT	-2,147,483,648 to 2,147,483,647	4 bytes
BIGINT	roughly ±9.22 × 1018	8 bytes
DECIMAL(p,s) / NUMERIC(p,s)	up to 38 digits total (precision p: 1-38; scale s: 0 to p)	5 bytes (p 1-9), 9 bytes (p 10-19), 13 bytes (p 20-28), 17 bytes (p 29-38)
SMALLMONEY	-214,748.3648 to 214,748.3647	4 bytes, fixed 4 decimal places
MONEY	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes, fixed 4 decimal places

DECIMAL and NUMERIC are functionally identical synonyms in T-SQL; most style guides pick one and use it consistently. Prefer DECIMAL(p,s) over MONEY or SMALLMONEY for currency columns where you'll do division or multiplication: MONEY's fixed four-decimal-place arithmetic can introduce small rounding anomalies in a long calculation chain that DECIMAL at an explicitly chosen scale avoids.

Approximate types trade exact precision for a much wider dynamic range, appropriate for scientific measurements, sensor telemetry, and similar data where a tiny representational error is acceptable.

FLOAT(n) stores a binary floating-point value, where n is the number of bits used for the mantissa, from 1 to 53, the default. A value of n from 1 to 24 is treated as single precision, roughly 7 significant digits, stored in 4 bytes; a value from 25 to 53 is treated as double precision, roughly 15 significant digits, stored in 8 bytes, with a range of about ±1.79 × 10³⁰⁸. REAL is a shorthand synonym for FLOAT(24), always 4 bytes, with a range of about ±3.40 × 10³⁸.

Because FLOAT and REAL are binary approximations, never use them for values that must compare or add up exactly, an invoice total, for instance. That's exactly what the exact numeric types above exist for.

Datatype	Format	Date Range	Storage
DATE	YYYY-MM-DD	0001-01-01 to 9999-12-31	3 bytes
TIME(n)	hh:mm:ss[.nnnnnnn], up to 100-nanosecond precision at n=7	n/a	3 bytes (n≤2), 4 bytes (n=3 or 4), 5 bytes (n≥5)
DATETIME2(n)	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 to 9999-12-31	6 bytes (n<3), 7 bytes (n=3 or 4), 8 bytes (n≥5)
DATETIMEOFFSET(n)	DATETIME2(n) plus a UTC offset	0001-01-01 to 9999-12-31, UTC-aware	DATETIME2(n)'s storage plus 2 bytes for the offset
SMALLDATETIME (legacy)	accurate to the minute	1900-01-01 to 2079-06-06	4 bytes
DATETIME (legacy)	accurate to .000/.003/.007 seconds	1753-01-01 to 9999-12-31	8 bytes

Modern table designs should standardize on DATE, TIME(n), DATETIME2(n), and DATETIMEOFFSET(n). SMALLDATETIME and DATETIME still work and still appear in older databases, but they carry a narrower date range, coarser accuracy, and, for DATETIME, storage that isn't actually any smaller than the modern equivalent, so there's little reason to choose them for a new table. DATETIMEOFFSET is worth calling out specifically: it's the type to reach for whenever a timestamp needs to carry its own time zone information along with it, rather than being stored and interpreted in one assumed zone.

SQL Server splits character data into non-Unicode and Unicode families.

Datatype	Encoding	Max Length	Storage
CHAR(n)	non-Unicode (or UTF-8 under a _UTF8 collation)	1 to 8,000 bytes	fixed n bytes, space-padded
VARCHAR(n)	non-Unicode (or UTF-8 under a _UTF8 collation)	1 to 8,000 bytes	actual length plus 2 bytes overhead
VARCHAR(MAX)	non-Unicode (or UTF-8)	up to 2 GB	actual length plus 2 bytes; in-row up to 8,000 bytes, off-row beyond that
NCHAR(n)	Unicode (UTF-16)	1 to 4,000 characters	fixed 2×n bytes
NVARCHAR(n)	Unicode (UTF-16)	1 to 4,000 characters	2× actual length plus 2 bytes overhead
NVARCHAR(MAX)	Unicode (UTF-16)	up to 2 GB	2× actual length plus 2 bytes; in-row or off-row depending on size

A standard, non-Unicode character set covers 256 distinct characters, enough for English and many Western European languages, but not enough for scripts like Chinese, Japanese, Korean, or Arabic. Unicode solves that by supporting the full range of international characters, at the cost of roughly double the storage: an NVARCHAR column takes 2 bytes per character versus VARCHAR's 1 byte. When you do need Unicode text, SQL Server 2025 offers a second option worth knowing about: applying a _UTF8 collation to a CHAR or VARCHAR column lets it store full Unicode text in UTF-8 encoding instead, which is far more storage-efficient than NVARCHAR for text that's mostly single-byte characters, English-language text with the occasional non-Latin word or symbol, for example, while still supporting the complete character range when needed.

One practical detail trips up a lot of people the first time they hit it: a Unicode string literal has to be prefixed with an uppercase N, or SQL Server will silently convert it through the connection's non-Unicode code page first, which can corrupt the very characters you were trying to preserve.

INSERT INTO TableName (UnicodeColumnName) VALUES (N'UnicodeText');

Leave the N off a literal destined for an NVARCHAR column and the data can come out wrong without SQL Server raising any error about it, so it's worth building the habit of using it any time you're writing Unicode text directly into a statement. Both NCHAR and NVARCHAR use the UCS-2/UTF-16 encoding internally, covering the characters used by the large majority of modern languages.

Datatype	Description	Storage
BINARY(n)	fixed-length raw bytes, 1 to 8,000	always n bytes, zero-padded
VARBINARY(n)	variable-length raw bytes, 1 to 8,000	actual length plus 2 bytes
VARBINARY(MAX)	variable-length raw bytes, up to 2 GB	actual length plus 2 bytes; can be paired with the FILESTREAM attribute to stream large files to the underlying OS volume instead of storing them inside the database file

Binary types store raw byte data rather than anything SQL Server interprets as text or numbers: cryptographic hashes, a SHA2_256 digest needs exactly BINARY(32), serialized objects, and file content are typical uses.

SQL Server 2025 expands this category with two genuinely new native types, alongside the long-standing XML type.

Datatype	Storage	Description
VECTOR(n)	8 + (4×n) bytes for float32	New in SQL Server 2025. Stores a fixed-dimension array of floating-point numbers, typically up to around 1,998 dimensions for float32, used for the numeric embeddings behind AI and similarity-search workloads. Queried with VECTOR_DISTANCE() for exact comparisons, or indexed with approximate nearest-neighbor search for fast lookups across large embedding sets.
JSON	up to 2 GB	New in SQL Server 2025. Stores JSON documents in a pre-parsed native binary format instead of plain character text, validating well-formed JSON on write. Supports targeted in-place .modify() updates and CREATE JSON INDEX for fast path-based lookups, without re-parsing the whole document on every query.
XML	up to 2 GB	Stores structured XML, optionally validated against an XML SCHEMA COLLECTION. Supports XQuery-style methods such as .query(), .value(), .exist(), and .modify(), along with primary and secondary XML indexes.

These three types exist for data that doesn't fit neatly into a single scalar value. XML has been available for years for self-describing structured documents; JSON now gives semi-structured data, increasingly the default interchange format most applications already speak, the same kind of native, validated, indexable storage that XML has long had; and VECTOR is purpose-built for AI workloads, representing a document, an image, or a block of text as a point in a high-dimensional space so that "similar" items can be found by distance rather than by exact match.

A handful of remaining types handle specific, narrower jobs:

• UNIQUEIDENTIFIER: a 16-byte globally unique identifier (GUID), displayed as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, typically populated with NEWID() for a fully random value or NEWSEQUENTIALID() for a value that increases monotonically, reducing index fragmentation compared to a purely random GUID.
• ROWVERSION (the modern name for the older TIMESTAMP type): an 8-byte binary counter that SQL Server increments automatically, database-wide, every time a row is inserted or updated. Despite the deprecated name it replaced, it never stores an actual date or time; it's used for optimistic concurrency control, letting an application detect whether a row changed since it was last read.
• HIERARCHYID: a variable-length type, typically 2 to 892 bytes, built for representing tree structures and hierarchical paths, with built-in methods like .GetAncestor(), .GetDescendant(), and .IsDescendantOf() for navigating the hierarchy directly in T-SQL.
• GEOMETRY and GEOGRAPHY: spatial types stored as variable-length binary. GEOMETRY represents flat, Euclidean (planar) coordinates; GEOGRAPHY represents geodetic, round-earth coordinates such as latitude and longitude, typically referenced to a spatial reference system like WGS 84.
• SQL_VARIANT: a polymorphic type, up to 8,016 bytes, able to hold values of different base datatypes across different rows of the same column, along with metadata recording each value's actual underlying type. It can't hold VECTOR, JSON, XML, VARCHAR(MAX), or NVARCHAR(MAX) values.
• SYSNAME: a built-in alias for NVARCHAR(128) NOT NULL, used throughout SQL Server's own system catalog views to store object names, and a convenient choice for your own columns that store the name of a database object.

Three older types remain supported in table definitions for backward compatibility, but shouldn't be used in new designs: TEXT, NTEXT, and IMAGE. Beyond being deprecated outright, they're incompatible with many of SQL Server's modern string and binary functions. Replace them directly with their modern equivalents: VARCHAR(MAX) in place of TEXT, NVARCHAR(MAX) in place of NTEXT, and VARBINARY(MAX) in place of IMAGE. If you come across one of these three in an existing table, it's a reliable sign the table predates SQL Server 2005, when the MAX length specifier was introduced specifically to replace them.

A single table typically draws from several of these categories at once. A product catalog table for an e-commerce system built on SQL Server 2025 might combine exact numerics, character strings, a date, a specialized type, and the new AI-oriented types, side by side:

CREATE TABLE Production.Products (
    ProductID        INT IDENTITY(1,1) PRIMARY KEY,         -- exact numeric, self-generating
    SKU              VARCHAR(20) NOT NULL UNIQUE,            -- non-Unicode character string
    ProductName      NVARCHAR(200) NOT NULL,                 -- Unicode character string
    UnitPrice        DECIMAL(9,2) NOT NULL,                  -- exact numeric, fixed precision
    DateAdded        DATE NOT NULL DEFAULT (GETDATE()),      -- date and time
    RowGuid          UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWSEQUENTIALID()), -- specialized type
    Specs            JSON NULL,                              -- AI/semi-structured type
    DescriptionVector VECTOR(768) NULL                       -- AI/semi-structured type
);

Every column here reflects a deliberate choice: an auto-generating integer for the primary key, a non-Unicode VARCHAR for a SKU that's always plain ASCII, NVARCHAR for a product name that might include non-English characters, a DECIMAL rather than FLOAT for a price that has to add up exactly, and the newer JSON and VECTOR types for flexible attributes and AI-driven similarity search, respectively. Matching each column to the datatype that actually fits it, rather than defaulting to the same generic type everywhere, is what this lesson has been building toward.

Choosing the right datatype is step one. In the next lesson, you'll learn how to specify length, precision, and scale for the columns that need them, the size arguments that, together with the datatype itself, determine exactly how much storage a column reserves and what range of values it can legally hold.