08. Data Types -- Complete Guide

Level: Beginner

ℹ️ What You'll Learn

• Numeric types (INT, BIGINT, SMALLINT, DECIMAL, FLOAT)
• String types (VARCHAR, NVARCHAR, CHAR, TEXT)
• Date/Time types (DATE, DATETIME, DATETIME2, TIME)
• Boolean type (BIT)
• Unique identifier (UNIQUEIDENTIFIER, GUID)
• Choosing correct data type
• Storage size and performance
• Common data type mistakes

Tables have columns. Columns have data types. Choosing wrong type causes errors, wastes space, breaks code. This article explains every important type.

Why Data Types Matter for Backend Developers

Wrong data type = bugs:

-- WRONG: Using VARCHAR for age
StudentAge VARCHAR(3)  -- Can store "XYZ"?
-- Backend code: age + 1 -> ERROR (can't add text)

-- RIGHT: Using INT for age
StudentAge INT  -- Can only store numbers
-- Backend code: age + 1 -> Works (101)

Choosing correct type prevents errors and improves performance.

Numeric Types

INT (4 bytes)

Whole numbers -2,147,483,648 to 2,147,483,647.

CREATE TABLE Student
(
    StudentId INT,           -- ID
    RollNumber INT,          -- Roll: 101, 102
    Age INT,                 -- Age: 15, 16, 17
    YearOfAdmission INT      -- Year: 2022, 2023
);

Use INT for: IDs, counts, whole number data.

BIGINT (8 bytes)

Whole numbers -9.2 quadrillion to 9.2 quadrillion (for very large numbers).

CREATE TABLE Attendance
(
    AttendanceId BIGINT,  -- Can handle millions of records
    StudentId BIGINT
);

Use BIGINT for: High-volume record IDs (if INT not enough).

SMALLINT (2 bytes)

Whole numbers -32,768 to 32,767 (smaller range).

CREATE TABLE Grade
(
    GradeLevel SMALLINT,  -- 1, 2, 3... 12
    Rating SMALLINT        -- 1 to 5 stars
);

Use SMALLINT for: Small whole numbers (1-100 range).

DECIMAL(p,s) or NUMERIC(p,s)

Decimal numbers with exact precision.

CREATE TABLE Student
(
    Fees DECIMAL(10,2),           -- 10 total digits, 2 after decimal
    GPA DECIMAL(3,2)              -- Max 9.99
);

p = precision (total digits), s = scale (digits after decimal).

DECIMAL(10,2) stores: -99,999,999.99 to 99,999,999.99

Use DECIMAL for: Money, exact decimal values (not FLOAT).

FLOAT (4 or 8 bytes)

Decimal numbers with approximate precision.

CREATE TABLE Subject
(
    AverageScore FLOAT  -- 85.5, 92.3 (approximate)
);

Uses scientific notation. Less precise than DECIMAL.

Use FLOAT for: Scientific calculations, approximate values (not money!).

String Types

VARCHAR(n)

Variable length text, up to n characters.

CREATE TABLE Student
(
    Name VARCHAR(100),          -- 'Ravi Kumar' (10 chars) -> uses 10 bytes
    Address VARCHAR(255),       -- 'Some address' (12 chars) -> uses 12 bytes
    Status VARCHAR(20)          -- 'Active' (6 chars) -> uses 6 bytes
);

Storage = actual length + 2 bytes overhead. "Ravi" = 4 + 2 = 6 bytes.

Use VARCHAR for: Names, addresses, email, most text (variable length).

NVARCHAR(n)

Unicode variable length text (supports all languages).

CREATE TABLE Student
(
    Name NVARCHAR(100),  -- 'Ravi Kumar' or any local-language name
    Address NVARCHAR(255) -- 'Hyderabad' or any local-language address
);

Each character = 2 bytes. "Ravi" = 4 chars x 2 = 8 bytes + 2 overhead.

Use NVARCHAR for: Multilingual text, names (safe choice). Costs more storage than VARCHAR.

CHAR(n)

Fixed length text, always n characters.

CREATE TABLE School
(
    Code CHAR(5)  -- 'NCA12' -> always 5 bytes
);

Even if you store 'ABC', it uses 5 bytes (padded with spaces).

Use CHAR for: Fixed codes, serial numbers (predictable length).

TEXT

Very large text (deprecated, avoid for new code).

-- NOT RECOMMENDED
Description TEXT  -- Old way

Use instead: VARCHAR(MAX) for large text.

VARCHAR(MAX)

Variable length text, up to 2GB.

CREATE TABLE AuditLog
(
    ChangeDetails VARCHAR(MAX)  -- Can store entire document
);

Use for: Large text, documents (but prefer relational approach).

Date/Time Types

DATE

Date only (YYYY-MM-DD), 3 bytes.

CREATE TABLE Student
(
    DateOfBirth DATE,      -- 2010-05-15
    JoiningDate DATE       -- 2023-06-01
);

Use DATE for: Dates without time component.

DATETIME

Date + Time (YYYY-MM-DD HH:MM:SS), 8 bytes.

CREATE TABLE ExamResult
(
    SubmittedAt DATETIME  -- 2023-06-15 14:30:45
);

Accurate to 0.00333 seconds (3.33ms).

Use DATETIME for: When you need both date and time.

DATETIME2(precision)

Date + Time with higher precision, 6-8 bytes.

CREATE TABLE ExamResult
(
    SubmittedAt DATETIME2(3)  -- Accurate to 1 millisecond
);

Use DATETIME2 for: API timestamps, precise logging (better than DATETIME).

TIME

Time only (HH:MM:SS), 3-5 bytes.

CREATE TABLE Class
(
    StartTime TIME,  -- 09:30:00
    EndTime TIME     -- 10:30:00
);

Use TIME for: Time without date.

Special Types

BIT

Boolean: 0 (false) or 1 (true), 1 byte.

CREATE TABLE Student
(
    IsActive BIT,      -- 1 = Active, 0 = Inactive
    IsPassed BIT       -- 1 = Passed, 0 = Failed
);

Use BIT for: Flags, yes/no, on/off.

UNIQUEIDENTIFIER (GUID)

Globally unique identifier, 16 bytes.

CREATE TABLE Student
(
    StudentId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Name NVARCHAR(100)
);

Generates unique value: A1B2C3D4-E5F6-7890-ABCD-EF1234567890

Use GUID for: Distributed systems, merging databases.

Decision Matrix: Choosing Data Type

Data	Type	Example	Why
Whole number (count, ID, age)	INT	StudentId, RollNumber	Efficient, 4 bytes
Decimal money	DECIMAL(10,2)	Fees, Salary	Exact precision
Decimal calculation	FLOAT	Temperature	Approximate OK
Text (short)	VARCHAR(50)	Name, Email	Variable length
Text (multilingual)	NVARCHAR(100)	Name (supports Hindi)	Unicode support
Date only	DATE	DateOfBirth	Smallest size
Date + time	DATETIME2(3)	SubmittedAt	Precise logging
Yes/No flag	BIT	IsActive	Boolean
Unique ID	UNIQUEIDENTIFIER	For API key	Globally unique

Example: Well-Designed Student Table

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),      -- INT for ID
    Name NVARCHAR(100) NOT NULL,                  -- NVARCHAR for names
    RollNumber INT NOT NULL,                      -- INT for roll number
    ClassName VARCHAR(10) NOT NULL,               -- VARCHAR for class
    DateOfBirth DATE,                             -- DATE for date only
    Fees DECIMAL(10,2),                           -- DECIMAL for money
    IsActive BIT DEFAULT 1                        -- BIT for boolean
);

---

Common Data Type Mistakes

Mistake 1: Using VARCHAR for numbers

Wrong:

CREATE TABLE Student
(
    RollNumber VARCHAR(5)  -- Stores as text "ABC123"?
);
-- Backend: RollNumber + 1 -> ERROR (can't add text)

Can't do math on text. "101" + 1 = ERROR.

Result: Application crashes when trying to calculate.

Fix: Use INT:

RollNumber INT
-- Backend: RollNumber + 1 -> 102 (works)

Mistake 2: Using FLOAT for money

Wrong:

CREATE TABLE Student
(
    Fees FLOAT  -- 50000.00
);
-- Floating point rounding errors
-- 50000.10 might store as 50000.0999999999

FLOAT has rounding errors due to binary representation.

Result: Money calculations wrong by pennies.

Fix: Use DECIMAL:

Fees DECIMAL(10,2)  -- Exact: 50000.10

Mistake 3: Using VARCHAR for dates

Wrong:

CREATE TABLE Student
(
    DateOfBirth VARCHAR(20)  -- "2010-05-15" (text)
);
-- Can't sort dates correctly (string sort != date sort)
-- Can't calculate age (DATE - today)

Result: Date operations fail. Sorting wrong.

Fix: Use DATE:

DateOfBirth DATE  -- 2010-05-15 (binary date)
-- Now sorting works, age calculation works

Mistake 4: Using TEXT instead of VARCHAR

Wrong:

CREATE TABLE Student
(
    Description TEXT  -- Old, deprecated
);

TEXT is slow, deprecated. NVARCHAR/VARCHAR better.

Result: Slower queries, harder to index.

Fix: Use VARCHAR(MAX):

Description VARCHAR(MAX)  -- Modern, indexable

---

Best Practices for Data Types

1. Choose smallest appropriate type -- INT not BIGINT unless needed
2. Use DECIMAL for money -- Never FLOAT for money
3. Use NVARCHAR for names -- Safer than VARCHAR
4. Use DATE for dates only -- DATETIME2 if you need time
5. Use INT for IDs and counts -- Standard choice
6. Be consistent -- Same type across related columns
7. Document why chosen -- Comments help maintenance

---

🎯 Q1: Why use INT instead of BIGINT for IDs?

INT:

• Range: -2.1B to 2.1B (enough for most systems)
• Size: 4 bytes
• Speed: Faster operations

BIGINT:

• Range: -9.2 quadrillion to 9.2 quadrillion
• Size: 8 bytes
• Speed: Slightly slower

For most systems: INT is fine. 2 billion IDs is huge.

Only BIGINT if: Expecting >2B records (rare).

Best practice: Start INT, migrate to BIGINT only if needed.

🎯 Q2: Should I use VARCHAR or NVARCHAR for names?

VARCHAR:

• ASCII text only (English, numbers, symbols)
• 1 byte per character
• Smaller storage

NVARCHAR:

• Unicode (all languages: Hindi, Chinese, Arabic, etc.)
• 2 bytes per character
• Larger storage, safer

For school: Names can be multilingual, so NVARCHAR is safer for student and parent names.

Best practice: Use NVARCHAR for names (slight storage cost, no bugs later).

Name NVARCHAR(100)  -- Supports all languages

🎯 Q3: What's the difference between DATETIME and DATETIME2?

DATETIME:

SubmittedAt DATETIME  -- 2023-06-15 14:30:45.123
-- Accurate to 3.33ms (0.00333 seconds)

DATETIME2(3):

SubmittedAt DATETIME2(3)  -- 2023-06-15 14:30:45.123
-- Accurate to 1ms (0.001 seconds)

DATETIME2 more precise, smaller storage (usually same size for DATETIME2(3)).

Best practice: Use DATETIME2(3) for timestamps (more precision, same size).

🎯 Q4: How do I store money in SQL Server?

WRONG -- using FLOAT:

Fees FLOAT  -- Rounding errors
-- 5000.50 might be 5000.4999999

RIGHT -- using DECIMAL:

Fees DECIMAL(10,2)
-- (10, 2) = 10 total digits, 2 after decimal
-- Stores: -99,999,999.99 to 99,999,999.99

Why DECIMAL: Exact precision, no rounding errors.

DECIMAL(10,2) explanation:

• 10 = total digits (e.g., 12345678.90)
• 2 = decimal places (money always has cents)

🎯 Q5: What's the difference between CHAR and VARCHAR?

CHAR(5):

Stored: 'ABC' becomes 'ABC  ' (padded to 5)
Always 5 bytes

VARCHAR(5):

Stored: 'ABC' stays 'ABC' (3 bytes)
Grows/shrinks as needed

Use CHAR for:

• Fixed length codes: 'NCA10' (school code)
• No wasted space if always filled

Use VARCHAR for:

• Variable length: names, addresses
• Saves space if mostly short values

Best practice: Use VARCHAR unless truly fixed length (rare).

🎯 Q6: Why avoid TEXT type in SQL Server?

TEXT (deprecated):

Description TEXT  -- Old, slow

Problems:

• Slow (not stored with row, stored separately)
• Can't index efficiently
• Not NVARCHAR compatible

Modern alternative:

Description VARCHAR(MAX)  -- Better

VARCHAR(MAX):

• Can store up to 2GB
• Better performance
• Compatible with NVARCHAR

Best practice: Use VARCHAR(MAX) for large text, not TEXT.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on SQL Server data types and choosing correct types. Try these prompts:

• "Explain INT, VARCHAR, DATE, DECIMAL with real examples from Student table"
• "Why use DECIMAL for money instead of FLOAT? Show the problem"
• "What's the difference between VARCHAR and NVARCHAR? When use each?"
• "How do I choose the right data type for a column?"
• "Quiz me: ask 5 questions about data types, storage, choosing types"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

09. Constraints -- Data Integrity Rules ->