SQL Server Data Types

Introduction

In Microsoft SQL Server, every column must have a data type.

👉 Data type defines:

What kind of data you can store
How much space it takes
What operations you can perform

Why Data Types Are Important?

Without proper data types:

❌ Data may become incorrect
❌ Performance becomes slow
❌ Storage gets wasted

👉 With correct data types:

✅ Fast queries
✅ Less memory usage
✅ Data accuracy

Types of Data Types in SQL Server

1️⃣ Numeric Data Types

INT (Integer)

👉 Stores whole numbers

CREATE TABLE Student (
    Id INT,
    Age INT
);

INSERT INTO Student VALUES (1, 20);

✔ Output:

👉 Use When:

No decimal values
Example: Age, Quantity

BIGINT

👉 Stores very large numbers

BIGINT

👉 Use When:

Large data like population, transactions

DECIMAL / NUMERIC

👉 Stores exact decimal values

Price DECIMAL(10,2)

👉 Example:

INSERT INTO Product VALUES (101, 199.99);

✔ Output:

Price
199.99

👉 Use When:

Money, banking (exact values required)

FLOAT

👉 Stores approximate decimal values

Value FLOAT

👉 Use When:

Scientific calculations
Approximate values

⚠️ Note: Not accurate like DECIMAL

2️⃣ Character (String) Data Types

CHAR

👉 Fixed length string

Code CHAR(5)

👉 Example:

INSERT INTO Test VALUES ('A');

✔ Output:

'A ' (extra spaces added)

👉 Use When:

Fixed length data (like PIN code)

VARCHAR

👉 Variable length string

Name VARCHAR(50)

👉 Example:

INSERT INTO Student VALUES ('Abhay');

✔ Output:

'Abhay'

👉 Use When:

Names, emails, addresses

✅ Saves space

NVARCHAR

👉 Stores Unicode (multiple languages)

Name NVARCHAR(50)

👉 Example:

INSERT INTO Student VALUES (N'અભય');

✔ Output:

અભય

👉 Use When:

Multi-language support

3️⃣ Date and Time Data Types

DATE

👉 Stores only date

DOB DATE

✔ Example:

2026-03-31

DATETIME

👉 Stores date + time

CreatedAt DATETIME

✔ Example:

2026-03-31 10:30:00

DATETIME2 (Recommended)

👉 More accurate than DATETIME

👉 Use When:

High precision time needed

TIME

👉 Stores only time

LoginTime TIME

✔ Output:

10:30:00

4️⃣ Boolean Data Type

BIT

👉 Stores only:

0 (False)
1 (True)

IsActive BIT

✔ Example:

IsActive
1

👉 Use When:

Yes/No values

5️⃣ Binary Data Types

VARBINARY

👉 Stores files (images, videos)

ProfileImage VARBINARY(MAX)

👉 Use When:

Store images in DB

6️⃣ Unique Identifier

UNIQUEIDENTIFIER

👉 Generates unique ID (GUID)

Id UNIQUEIDENTIFIER DEFAULT NEWID()

✔ Example Output:

A1B2C3D4-5678-90AB-CDEF-1234567890AB

👉 Use When:

Unique records across systems

7️⃣ Special Data Types

XML

👉 Stores XML data

Data XML

JSON (Stored in NVARCHAR)

👉 SQL Server stores JSON as string

Data NVARCHAR(MAX)

When to Use Which Data Type (Important)

Situation → Best Data Type
Age → INT
Salary → DECIMAL
Name → VARCHAR / NVARCHAR
Date of Birth → DATE
Login Time → TIME
Yes/No → BIT
Image → VARBINARY
Unique ID → UNIQUEIDENTIFIER

Common Mistakes

❌ Using VARCHAR instead of NVARCHAR for multi-language
❌ Using FLOAT for money
❌ Using CHAR unnecessarily (wastes space)
❌ Using DATETIME instead of DATETIME2

Pro Tips (Very Important)

Always choose smallest possible data type
Use DECIMAL for money
Use NVARCHAR for international apps
Use DATETIME2 instead of DATETIME

Real-Life Example

CREATE TABLE Employee (
    Id INT,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2),
    DOB DATE,
    IsActive BIT
);

Conclusion

👉 Data types are the foundation of database design

If you choose wrong:

Performance ↓
Bugs ↑

If you choose correct:

Fast system
Clean data