SQL Server Data Types Explained: Every Major Type, and Which One to Actually Use

Every column has exactly one data type — SQL Server enforces it strictly, unlike a spreadsheet cell that’ll happily hold text or a number interchangeably. This lesson covers every data type category you’ll realistically encounter: exact and approximate numbers, character strings, dates and times, binary data, and the “special purpose” types like UNIQUEIDENTIFIER and XML.

Exact Numeric Types

Use these when the value must be precisely correct — counts, IDs, money. No rounding error, ever.

Approximate Numeric Types

FLOAT is a binary approximation and can accumulate tiny rounding errors — summing many FLOAT values can produce $100.00000000001 instead of exactly $100.00. DECIMAL stores exact values with zero rounding error, which is why it’s the correct choice for financial data.

Character String Types

Rule of thumb: default to NVARCHAR. The storage cost difference versus VARCHAR is small, and it avoids an entire class of bugs where a user’s name or a customer’s address contains a character your ASCII column silently mangles.

Date and Time Types

Binary Types

Other Special-Purpose Types

There’s no dedicated JSON type in SQL Server — JSON is stored as NVARCHAR(MAX) and manipulated with built-in functions like JSON_VALUE, JSON_QUERY, and ISJSON.

A Real Table Using a Representative Mix

CREATE TABLE dbo.Example_DataTypes (
    id            INT             IDENTITY(1,1) PRIMARY KEY,
    public_id     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    full_name     NVARCHAR(100)   NOT NULL,
    country_code  CHAR(2)         NOT NULL,
    price         DECIMAL(10,2)   NOT NULL,
    quantity      SMALLINT        NOT NULL,
    is_active     BIT             NOT NULL DEFAULT 1,
    signed_up_on  DATE            NOT NULL DEFAULT GETDATE(),
    last_login    DATETIME2       NULL,
    profile_json  NVARCHAR(MAX)   NULL,
    avatar        VARBINARY(MAX)  NULL,
    row_version   ROWVERSION
);

Why DECIMAL, Never FLOAT, for Money

This is one of the most common code-review flags in real SQL Server codebases: FLOAT’s binary rounding makes it fundamentally unsuitable for currency, while DECIMAL guarantees exact values.

The Rule of Thumb

Pick the smallest type that can never realistically overflow, and default to Unicode (NVARCHAR) for text and DATETIME2 for timestamps unless you have a specific reason not to. Using NVARCHAR(MAX) or BIGINT everywhere “to be safe” wastes storage and slows indexes down — you’ll see exactly why once we get to performance tuning.