SQL Server

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:

  • Id

  • Age

  • 1

  • 20

๐Ÿ‘‰ 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
Membership not found