Data Types in SQL Server

Analogy categoryAnalogies

Every column in a SQL Server table has a data type that determines what values it can hold, how much storage it consumes, and how it's validated and compared. Picking the right type isn't cosmetic: an undersized numeric type can overflow, an oversized character type wastes storage and index space, and using an approximate numeric type for money can introduce rounding errors that compound over millions of rows. Data types broadly fall into exact numeric (INT, DECIMAL), approximate numeric (FLOAT, REAL), character (CHAR, VARCHAR, NVARCHAR), date/time (DATE, DATETIME2), and special types (UNIQUEIDENTIFIER, XML, BIT).

🏏

Cricket analogy: Choosing a data type is like choosing the right equipment for a format: you wouldn't use a Test match's unhurried pacing for a T20 innings; similarly, using DECIMAL for money versus FLOAT for scientific data matches the tool to the job's precision needs.

Numeric and Character Types

For whole numbers, INT (roughly plus/minus 2.1 billion) covers most needs, while BIGINT is reserved for columns that can genuinely exceed that range, such as a heavily-loaded identity key. For exact decimal precision, especially currency, use DECIMAL(precision, scale) rather than FLOAT or REAL, which are binary approximations unsuitable for money. For text, CHAR(n) is fixed-length and pads shorter values with spaces, while VARCHAR(n) is variable-length and only uses the space the actual value needs; prefixing either with N (NCHAR, NVARCHAR) stores Unicode data, necessary for names or text in non-Latin scripts.

🏏

Cricket analogy: INT versus BIGINT is like choosing a scoreboard that tracks runs up to 999 for a T20 versus a career-runs tracker that must handle numbers like Sachin Tendulkar's 34,357 international runs: you need the bigger range for cumulative totals.

Date/Time and Special Types

For dates and times, DATE stores just a calendar date, TIME stores just a time of day, and DATETIME2 (the recommended successor to the legacy DATETIME type) stores both with configurable fractional-second precision and a wider valid range. DATETIMEOFFSET additionally stores a time-zone offset, useful when you need to compare timestamps recorded across regions unambiguously. Special types include UNIQUEIDENTIFIER, a 16-byte GUID often used as a globally unique key in distributed systems, BIT for true/false flags, and XML for storing and querying XML documents natively.

🏏

Cricket analogy: DATETIME2 storing a match's exact start time down to fractions of a second is like the third umpire's ball-tracking system needing millisecond precision, while a simple DATE type is enough for just recording which day a Test match began.

sql

CREATE TABLE dbo.Products (
    ProductID     INT IDENTITY(1,1) PRIMARY KEY,
    ProductCode   CHAR(8)         NOT NULL,       -- fixed-length SKU
    ProductName   NVARCHAR(200)   NOT NULL,        -- variable-length, Unicode
    UnitPrice     DECIMAL(10,2)   NOT NULL,        -- exact currency precision
    IsDiscontinued BIT            NOT NULL DEFAULT 0,
    ReleaseDate   DATE            NULL,
    LastModified  DATETIME2(3)    NOT NULL DEFAULT SYSDATETIME(),
    RowGuid       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
);

Choosing the Right Type

It's tempting to make every character column NVARCHAR(MAX) so it 'always fits,' but this trades away useful constraints: it hides realistic maximums from anyone reading the schema, can prevent certain optimizations like online index rebuilds and in-row storage, and provides no protection against absurdly long input. Right-sizing types also avoids implicit conversions — when SQL Server compares columns of mismatched types, such as a VARCHAR column against an INT parameter, it must convert one side, which can silently prevent an index on that column from being used, turning a fast seek into a full scan.

🏏

Cricket analogy: Overusing NVARCHAR(MAX) for every column is like padding every batsman's role as an all-rounder just in case: it works, but wastes resources compared to specializing a player, or a column, for its actual job.

Comparing or joining columns of mismatched data types (e.g. VARCHAR to INT) can trigger implicit conversions that silently disable index usage, turning a fast seek into a slow table scan. Always match types explicitly in WHERE and JOIN clauses.

Use INT for typical whole numbers and BIGINT only when values can exceed roughly 2.1 billion.
Use DECIMAL(p,s) for exact values like currency; avoid FLOAT/REAL for money due to rounding error.
CHAR is fixed-length (padded); VARCHAR is variable-length; prefix with N for Unicode support.
DATE, TIME, DATETIME2, and DATETIMEOFFSET offer increasing precision and time-zone awareness.
UNIQUEIDENTIFIER (GUID) guarantees global uniqueness, useful in distributed systems.
Avoid defaulting every column to NVARCHAR(MAX); it wastes storage and can block certain optimizations.
Mismatched data types in comparisons can cause implicit conversions that hurt query performance.