Data Types in SQL Server
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.
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.
Practice what you learned
1. Which data type should you use for storing currency values precisely?
2. What's the key difference between CHAR and VARCHAR?
3. Which data type stores a globally unique 16-byte identifier?
4. What does the N prefix on NVARCHAR indicate?
5. What is a common risk of comparing mismatched data types in a WHERE clause?
Was this page helpful?
You May Also Like
T-SQL Syntax Basics
Core T-SQL syntax rules — statements, batches, comments, identifiers, and control-of-flow — you need before writing real queries.
Your First T-SQL Query
Write, run, and understand your first SELECT statement in SQL Server, from filtering rows to sorting results.
What Is SQL Server?
An introduction to Microsoft SQL Server, its architecture, editions, and where it fits among relational database systems.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics