Data Types
Choosing the right data type keeps data correct, saves storage and makes queries faster. A few simple rules cover almost every column you will create.
On this page · 5 min · Beginner
On this page
- In simple words
- Learning objectives
- Explanation
- Syntax
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Beginner
- Updated
- Oct 27, 2026
Related tutorials
In simple words#
Data types are the format rules of each register column: amount columns hold money with paise, date columns hold dates, name columns hold text up to a limit.
- INT / BIGINT
- Whole numbers.
- DECIMAL(10,2)
- Exact money values.
- NVARCHAR(n)
- Unicode text up to n characters (Hindi, emoji…).
Learning objectives#
- Pick numeric types, including DECIMAL for money
- Understand VARCHAR vs NVARCHAR
- Store dates and times correctly
- Know when to use BIT and UNIQUEIDENTIFIER
Explanation#
Whole numbers: INT (about ±2.1 billion) is the usual default; BIGINT for very large counters; TINYINT/SMALLINT for small ranges.
Exact decimals: use DECIMAL(p, s) for money and quantities — p is total digits, s is digits after the point. FLOAT and REAL are approximate and can introduce rounding errors.
Text: VARCHAR stores 1 byte per character for most Latin text; NVARCHAR stores Unicode (2 bytes per character) and is required for Hindi, emojis and most non-English text. Use a sensible length, or MAX only for genuinely long text.
Dates: DATE for calendar dates, DATETIME2 for date and time (more precise than old DATETIME), DATETIMEOFFSET when the time zone matters. BIT holds 0/1 flags; UNIQUEIDENTIFIER holds GUIDs.
Syntax#
Price DECIMAL(10,2) -- up to 99,999,999.99
Name NVARCHAR(100)
CreatedAt DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
IsActive BIT NOT NULL DEFAULT 1
Code example: Seeing the difference#
SELECT
DATALENGTH('Hello') AS VarcharBytes,
DATALENGTH(N'Hello') AS NvarcharBytes,
CAST(19.999 AS DECIMAL(5,2)) AS RoundedDecimal,
CAST('2026-10-27' AS DATE) AS JustDate,
CAST(1 AS BIT) AS Flag;
VarcharBytes NvarcharBytes RoundedDecimal JustDate Flag 5 10 20.00 2026-10-27 1
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- DATALENGTH returns bytes: the same five characters take 5 bytes as VARCHAR and 10 as NVARCHAR.
- Casting 19.999 to DECIMAL(5,2) rounds to two decimal places: 20.00.
- A string in ISO format (yyyy-mm-dd) converts reliably to DATE in any language setting.
- BIT stores a single 0 or 1.
Real-world use case#
An e-commerce Products table uses DECIMAL(10,2) for Price, NVARCHAR(200) for Name (product names may contain any language), BIT for IsActive and DATETIME2 for CreatedAt.
Common mistakes#
Floating-point types are approximate. Sums of prices can be off by fractions of a paisa. Use DECIMAL.
MAX columns cannot be index keys and can be slower. Choose a realistic maximum length.
Text dates sort and compare incorrectly and allow invalid values. Use DATE or DATETIME2.
Best practices#
- DECIMAL for money, INT/BIGINT for counters
- NVARCHAR for user-visible text
- DATETIME2 (UTC) for timestamps
- Match parameter types in your app to column types to avoid implicit conversions
Remember this#
Money → DECIMAL, never FLOAT.
Text with Hindi or emoji → NVARCHAR.
Choose the smallest type that fits.
Practice exercise#
Your task: Design columns for an Employees table: name, email, salary, date of joining, whether they are active, and a last-login timestamp.
Show solution
CREATE TABLE Employees (
EmployeeId INT IDENTITY PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
Email VARCHAR(254) NOT NULL UNIQUE,
Salary DECIMAL(12,2) NOT NULL,
JoinedOn DATE NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
LastLoginUtc DATETIME2(0) NULL
);
Mini quiz#
Which type should store product prices?
Want more? Practise all SQL Server MCQs · SQL Server interview questions
My notes#
Saved in this browser.