SQL Server Data Types Explained: INT, VARCHAR, DATE, DECIMAL and More

Introduction

When creating a database table in Microsoft SQL Server, one of the most important decisions is selecting the correct data type for each column.

A Data Type defines what kind of information a database column can store, such as numbers, text, dates, decimal values, or Boolean-like values.

For example, imagine that you are developing a Student Management System for a college. You need to store student IDs, names, email addresses, dates of birth, course fees, and examination marks. Each field requires an appropriate data type to store its information correctly.

If you select INT for a student name or VARCHAR for a date field, your database design may become inefficient or produce unexpected results.

In this beginner-friendly tutorial, we will learn about SQL Server data types, including INT, BIGINT, VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, DECIMAL, FLOAT, BIT, and more. We will also understand their differences and use practical SQL examples to select the right data type when creating database tables.

Let's understand SQL Server Data Types step by step.

1. What Are Data Types in SQL Server?

Data Types define the type of data that can be stored in a database column or variable.

Every column in a SQL Server table must have a data type that determines the kind of values it can store.

For example:

• StudentID → INT

• StudentName → NVARCHAR(100)

• Email → VARCHAR(150)

• DateOfBirth → DATE

• CourseFees → DECIMAL(10,2)

• IsActive → BIT

Data types help SQL Server understand how to store, process, validate, and retrieve information.

Example:

CREATE TABLE Students
(
    StudentID INT,
    StudentName NVARCHAR(100),
    Age INT,
    Fees DECIMAL(10,2),
    DateOfBirth DATE
);

In this example, each column uses a different data type according to the information it stores.

2. Categories of SQL Server Data Types

SQL Server provides several categories of data types.

The major categories include:

1. Numeric Data Types

2. Character and String Data Types

3. Date and Time Data Types

4. Binary Data Types

5. Boolean-Like Data Types

6. Other Special Data Types

Understanding these categories helps developers select the correct data type for different database requirements.

3. Numeric Data Types in SQL Server

Numeric data types are used to store numerical values such as IDs, ages, quantities, prices, and calculations.

Some commonly used numeric data types are:

Data Type	Description	Example
TINYINT	Stores small whole numbers from 0 to 255	25
SMALLINT	Stores small-range signed integers	1500
INT	Stores standard whole numbers	100
BIGINT	Stores very large whole numbers	1000000
DECIMAL	Stores exact decimal values	2500.50
NUMERIC	Equivalent to DECIMAL	150.75
FLOAT	Stores approximate floating-point values	85.75
REAL	Stores approximate single-precision values	45.5

Let's understand the important numeric data types individually.

4. INT Data Type in SQL Server

INT is one of the most commonly used numeric data types in SQL Server.

It stores whole numbers without decimal values.

Storage: 4 bytes

Range: -2,147,483,648 to 2,147,483,647

Example:

CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeAge INT,
    Salary INT
);

Insert some records:

INSERT INTO Employees
VALUES
(101, 25, 30000),
(102, 28, 45000),
(103, 30, 50000);

Retrieve the records:

SELECT * FROM Employees;

When to use INT:

• Student IDs within the integer range.

• Employee IDs.

• Product quantities.

• Age values.

• Counting records.

INT is a suitable choice for many everyday whole-number requirements.

5. BIGINT Data Type in SQL Server

BIGINT is used to store very large whole numbers.

It provides a much larger range than INT.

Storage: 8 bytes

Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Example:

CREATE TABLE Transactions
(
    TransactionID BIGINT,
    AccountNumber BIGINT,
    Amount INT
);

Insert a record:

INSERT INTO Transactions
VALUES
(98765432101, 123456789012, 5000);

Retrieve the record:

SELECT * FROM Transactions;

When to use BIGINT:

• Very large transaction identifiers.

• Large counting values.

• High-volume application records.

• Numeric values that may exceed the INT range.

Important: BIGINT uses more storage than INT, so use it when the expected range requires it.

6. SMALLINT and TINYINT Data Types

SQL Server also provides SMALLINT and TINYINT for storing smaller whole-number values.

SMALLINT

• Storage: 2 bytes

• Range: -32,768 to 32,767

Example:

CREATE TABLE Courses
(
    CourseID SMALLINT,
    CourseDuration SMALLINT
);

TINYINT

• Storage: 1 byte

• Range: 0 to 255

Example:

CREATE TABLE StudentGrades
(
    GradeID INT,
    GradeLevel TINYINT
);

When to use them:

• SMALLINT is useful for values that fit within its smaller range.

• TINYINT is useful for small positive numbers such as status codes or limited category values.

7. DECIMAL and NUMERIC Data Types

DECIMAL is used to store exact numeric values with decimal places.

It is particularly useful for financial calculations, prices, fees, and amounts.

Syntax:

DECIMAL(Precision, Scale)

• Precision defines the maximum total number of digits.

• Scale defines the maximum number of digits after the decimal point.

Example:

CREATE TABLE CourseFees
(
    CourseID INT,
    CourseName VARCHAR(100),
    Fees DECIMAL(10,2)
);

Insert records:

INSERT INTO CourseFees
VALUES
(1, 'Web Development', 15000.50),
(2, 'Digital Marketing', 12000.75),
(3, 'Python Programming', 18000.00);

Retrieve records:

SELECT * FROM CourseFees;

Understanding DECIMAL(10,2):

• Total digits: 10

• Digits after decimal: 2

• Maximum positive value: 99,999,999.99

When to use DECIMAL:

• Course fees.

• Product prices.

• Salary amounts requiring exact decimal storage.

• Financial transactions.

• Invoice calculations.

Important: DECIMAL and NUMERIC are equivalent in SQL Server. For financial values, DECIMAL is generally preferable to FLOAT because it stores exact decimal values within its defined precision and scale.

8. FLOAT and REAL Data Types

FLOAT and REAL are used to store approximate numeric values.

They are useful when working with measurements, scientific calculations, and values that may require a large range.

FLOAT Example:

CREATE TABLE Measurements
(
    MeasurementID INT,
    Temperature FLOAT
);

Insert a record:

INSERT INTO Measurements
VALUES
(1, 36.75);

REAL Example:

CREATE TABLE ProductMeasurements
(
    ProductID INT,
    ProductWeight REAL
);

Difference between FLOAT and REAL:

• FLOAT supports different precision levels.

• REAL is a 4-byte approximate numeric type.

• FLOAT without an explicit precision uses the default precision of 53 in SQL Server.

Important: FLOAT and REAL store approximate values. Avoid using them for financial calculations where exact decimal representation is required.

9. Character and String Data Types in SQL Server

Character and string data types are used to store text information such as names, addresses, email addresses, descriptions, and contact details.

Common SQL Server string data types include:

Data Type	Description
CHAR(n)	Fixed-length non-Unicode character data
VARCHAR(n)	Variable-length non-Unicode character data
NCHAR(n)	Fixed-length Unicode character data
NVARCHAR(n)	Variable-length Unicode character data
VARCHAR(MAX)	Large variable-length non-Unicode text
NVARCHAR(MAX)	Large variable-length Unicode text

Let's understand the important string data types.

10. VARCHAR Data Type in SQL Server

VARCHAR is used to store variable-length non-Unicode character data.

It is commonly used for English text, email addresses, usernames, and other text fields when the selected code page supports the required characters.

Syntax:

VARCHAR(n)

Here, n specifies the maximum number of bytes.

Example:

CREATE TABLE Customers
(
    CustomerID INT,
    CustomerName VARCHAR(100),
    Email VARCHAR(150),
    City VARCHAR(50)
);

Insert records:

INSERT INTO Customers
VALUES
(1, 'Rahul Sharma', 'rahul@example.com', 'Raipur'),
(2, 'Amit Kumar', 'amit@example.com', 'Delhi');

Retrieve records:

SELECT * FROM Customers;

When to use VARCHAR:

• Email addresses.

• Usernames.

• English names.

• City names.

• Short descriptions.

Important: VARCHAR length is measured in bytes, not always characters. The number of bytes used depends on the collation and encoding.

11. NVARCHAR Data Type in SQL Server

NVARCHAR is used to store variable-length Unicode character data.

It is useful when an application needs to support multiple languages, including Hindi and English.

Syntax:

NVARCHAR(n)

Here, n specifies the maximum number of byte-pairs, not a guaranteed number of visible characters.

Example:

CREATE TABLE StudentInformation
(
    StudentID INT,
    StudentName NVARCHAR(100),
    Address NVARCHAR(200)
);

Insert multilingual records:

INSERT INTO StudentInformation
VALUES
(1, N'Rahul Sharma', N'Raipur'),
(2, N'अमित कुमार', N'छत्तीसगढ़');

Retrieve records:

SELECT * FROM StudentInformation;

Important: Use the N prefix before Unicode string literals when inserting Unicode text, such as Hindi characters.

When to use NVARCHAR:

• Hindi names.

• Multilingual applications.

• International customer information.

• Unicode text.

• Applications supporting different languages.

12. Difference Between VARCHAR and NVARCHAR

VARCHAR and NVARCHAR are both used for storing text, but they have different storage and character-support behavior.

Feature	VARCHAR	NVARCHAR
Character support	Non-Unicode	Unicode
Storage	Variable	Variable
Length unit	Bytes	Byte-pairs
Hindi text support	Depends on encoding	Yes
Common usage	English text	Multilingual text

Example:

CREATE TABLE LanguageExample
(
    EnglishName VARCHAR(100),
    HindiName NVARCHAR(100)
);

For applications that need to store both Hindi and English content, NVARCHAR is a suitable choice.

13. CHAR and NCHAR Data Types

CHAR and NCHAR are fixed-length character data types.

CHAR

CHAR stores fixed-length non-Unicode character data.

Example:

CREATE TABLE CountryCodes
(
    CountryCode CHAR(3),
    CountryName VARCHAR(100)
);

If a value contains fewer characters than the defined length, SQL Server pads the remaining space.

NCHAR

NCHAR stores fixed-length Unicode character data.

Example:

CREATE TABLE LanguageCodes
(
    LanguageCode NCHAR(5),
    LanguageName NVARCHAR(100)
);

Difference:

• CHAR stores fixed-length non-Unicode data.

• NCHAR stores fixed-length Unicode data.

• VARCHAR and NVARCHAR are variable-length alternatives.

Use CHAR and NCHAR when fixed-length values are appropriate, such as standardized codes.

14. DATE Data Type in SQL Server

DATE is used to store date values without time information.

It stores:

• Year

• Month

• Day

Storage: 3 bytes

Date Range: 0001-01-01 to 9999-12-31

Example:

CREATE TABLE Students
(
    StudentID INT,
    StudentName VARCHAR(100),
    DateOfBirth DATE
);

Insert records:

INSERT INTO Students
VALUES
(1, 'Rahul Sharma', '2005-04-15'),
(2, 'Priya Verma', '2004-08-20');

Retrieve records:

SELECT * FROM Students;

When to use DATE:

• Date of birth.

• Admission date.

• Joining date.

• Exam date.

• Course start date.

If you only need a date and do not need time information, DATE is an appropriate choice.

15. TIME Data Type in SQL Server

TIME is used to store time values without a date.

It stores:

• Hours

• Minutes

• Seconds

• Fractional seconds

Example:

CREATE TABLE ClassSchedule
(
    ClassID INT,
    ClassName VARCHAR(100),
    StartTime TIME
);

Insert a record:

INSERT INTO ClassSchedule
VALUES
(1, 'SQL Server Training', '10:30:00');

Retrieve records:

SELECT * FROM ClassSchedule;

When to use TIME:

• Class schedules.

• Office opening times.

• Appointment times.

• Daily work schedules.

16. DATETIME and DATETIME2 Data Types

DATETIME is used to store both date and time information.

It includes:

• Year

• Month

• Day

• Hours

• Minutes

• Seconds

• Fractional seconds

Example:

CREATE TABLE BlogPosts
(
    BlogID INT,
    BlogTitle VARCHAR(200),
    PublishDate DATETIME
);

Insert a record:

INSERT INTO BlogPosts
VALUES
(1, 'SQL Server Introduction', '2026-11-07 10:30:00');

Retrieve records:

SELECT * FROM BlogPosts;

DATETIME2 Example:

CREATE TABLE EmployeeAttendance
(
    AttendanceID INT,
    EmployeeName VARCHAR(100),
    CheckInTime DATETIME2
);

Insert a record:

INSERT INTO EmployeeAttendance
VALUES
(1, 'Rahul Sharma', '2026-11-07 09:15:30.1234567');

Difference between DATETIME and DATETIME2:

• DATETIME uses 8 bytes and has a date range from 1753-01-01 to 9999-12-31.

• DATETIME2 supports a wider date range, greater fractional-second precision, and uses 6 to 8 bytes depending on precision.

• DATETIME2 is generally preferred for new SQL Server development when a date and time value is required.

17. BIT Data Type in SQL Server

BIT is used to store Boolean-like values.

It can represent:

• 0 = False

• 1 = True

• NULL = Unknown or not specified, if the column allows NULL

Example:

CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    IsActive BIT
);

Insert records:

INSERT INTO Employees
VALUES
(1, 'Rahul Sharma', 1),
(2, 'Amit Kumar', 0);

Retrieve records:

SELECT * FROM Employees;

When to use BIT:

• IsActive status.

• IsVerified status.

• IsAvailable status.

• IsPublished status.

• IsApproved status.

BIT is useful when a column needs to represent a simple true-or-false condition.

18. BINARY and VARBINARY Data Types

BINARY and VARBINARY are used to store binary data.

BINARY

BINARY stores fixed-length binary data.

Example:

CREATE TABLE BinaryExample
(
    RecordID INT,
    FileCode BINARY(5)
);

VARBINARY

VARBINARY stores variable-length binary data.

Example:

CREATE TABLE FileStorage
(
    FileID INT,
    FileData VARBINARY(MAX)
);

Difference:

• BINARY stores fixed-length binary values.

• VARBINARY stores variable-length binary values.

VARBINARY(MAX) can be used for large binary content when storing files directly in a database is appropriate.

For many application designs, storing files in external storage and keeping their paths or references in SQL Server is also a practical option.

19. UNIQUEIDENTIFIER Data Type

UNIQUEIDENTIFIER stores a globally unique identifier (GUID).

It is commonly used when records need unique identifiers across different systems.

Example:

CREATE TABLE Products
(
    ProductID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Insert a record:

INSERT INTO Products
(
    ProductName,
    Price
)
VALUES
(
    'Laptop',
    55000.00
);

Retrieve records:

SELECT * FROM Products;

SQL Server generates a unique identifier using NEWID() when a new record is inserted without specifying ProductID.

When to use UNIQUEIDENTIFIER:

• Distributed applications.

• Systems with multiple databases.

• Globally unique record identification.

• Applications that generate identifiers independently.

20. SQL Server Data Type Conversion

Sometimes you need to convert one data type into another.

SQL Server provides CAST() and CONVERT() functions for data type conversion.

Example Using CAST():

SELECT CAST(100 AS VARCHAR(20)) AS ConvertedValue;

This converts the integer value 100 into a VARCHAR value.

Example Using CONVERT():

SELECT CONVERT(VARCHAR(20), 2500) AS ConvertedValue;

This also converts a numeric value into text.

Converting Text into an Integer:

SELECT CAST('500' AS INT) AS NumberValue;

Using TRY_CAST():

SELECT TRY_CAST('ABC' AS INT) AS Result;

TRY_CAST() returns NULL when a conversion fails, instead of raising a conversion error.

Important: Data conversion may fail when the source value cannot be represented by the target data type. Always validate data before converting it.

21. How to Check Data Types of Table Columns

SQL Server provides different methods to check the data types of existing table columns.

Method 1: Using sp_help

EXEC sp_help 'Students';

This displays information about the Students table, including its column data types.

Method 2: Using INFORMATION_SCHEMA.COLUMNS

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';

This query displays:

• Column names.

• Data types.

• Maximum character lengths, where applicable.

• Whether NULL values are allowed.

22. How to Choose the Right Data Type in SQL Server

Selecting the correct data type is an important part of database design.

Consider the following guidelines:

Requirement	Recommended Data Type
Student ID	INT
Very large numeric ID	BIGINT
Student Name	NVARCHAR(100)
Email Address	VARCHAR(150)
Hindi Text	NVARCHAR(200)
Date of Birth	DATE
Class Timing	TIME
Blog Publish Date and Time	DATETIME2
Course Fees	DECIMAL(10,2)
Active Status	BIT
Product GUID	UNIQUEIDENTIFIER

Important Guidelines:

1. Choose a data type according to the actual information you need to store.

2. Avoid using unnecessarily large data types.

3. Use DECIMAL for financial values requiring exact decimal precision.

4. Use DATE when you do not need time information.

5. Use NVARCHAR when Unicode and multilingual text support is required.

6. Use BIGINT only when the INT range is insufficient.

7. Consider storage, performance, and application requirements while designing tables.

23. Complete Practical Example: Student Management Database

Now let's create a practical Student Management Database using different SQL Server data types.

We will create a database named StudentDB and a Students table with appropriate columns.

Step 1: Create Database

CREATE DATABASE StudentDB;
GO

Step 2: Select Database

USE StudentDB;
GO

Step 3: Create Students Table

CREATE TABLE Students
(
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    StudentName NVARCHAR(100) NOT NULL,
    Email VARCHAR(150),
    Age TINYINT,
    DateOfBirth DATE,
    CourseFees DECIMAL(10,2),
    IsActive BIT,
    AdmissionDate DATETIME2
);
GO

Step 4: Insert Student Records

INSERT INTO Students
(
    StudentName,
    Email,
    Age,
    DateOfBirth,
    CourseFees,
    IsActive,
    AdmissionDate
)
VALUES
(
    N'Rahul Sharma',
    'rahul@example.com',
    21,
    '2005-04-15',
    15000.50,
    1,
    '2026-11-07 09:30:00'
),
(
    N'प्रियंका वर्मा',
    'priya@example.com',
    22,
    '2004-08-20',
    18000.75,
    1,
    '2026-11-07 10:00:00'
),
(
    N'Amit Kumar',
    'amit@example.com',
    20,
    '2006-02-10',
    12000.00,
    0,
    '2026-11-07 11:00:00'
);
GO

Step 5: Retrieve Student Records

SELECT * FROM Students;
GO

Step 6: Check Column Data Types

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';
GO

Expected Result:

The Students table will contain:

• StudentID as INT.

• StudentName as NVARCHAR.

• Email as VARCHAR.

• Age as TINYINT.

• DateOfBirth as DATE.

• CourseFees as DECIMAL.

• IsActive as BIT.

• AdmissionDate as DATETIME2.

Congratulations! You have successfully created a practical SQL Server table using different data types.

24. Common Data Type Errors in SQL Server

1. String or Binary Data Would Be Truncated

This error occurs when you try to insert text that exceeds the defined column length.

Solution:

• Check the column length.

• Increase VARCHAR or NVARCHAR length when appropriate.

• Validate the input before inserting.

2. Conversion Failed When Converting Data

This error occurs when SQL Server cannot convert a value into the specified data type.

For example:

SELECT CAST('Hello' AS INT);

Solution:

• Check the input value.

• Use the correct data type.

• Use TRY_CAST() when conversion failure needs to be handled safely.

3. Arithmetic Overflow Error

This error occurs when a numeric value exceeds the allowed range or precision of the selected data type.

Solution:

• Check the numeric range.

• Select a suitable data type.

• Review DECIMAL precision and scale.

4. Incorrect Date Format

This error may occur when a date string is invalid or interpreted differently according to date settings.

Solution:

Use an unambiguous ISO-style date format:

SELECT CAST('2026-11-07' AS DATE);

5. Cannot Insert NULL Value

This error occurs when a NULL value is inserted into a column defined as NOT NULL.

Solution:

• Supply a valid value.

• Use a DEFAULT constraint where appropriate.

• Allow NULL only if the business requirement permits it.

25. Frequently Asked Questions (FAQs)

Q1. What are Data Types in SQL Server?

Data Types define the kind of information that can be stored in database columns, such as numbers, text, dates, and decimal values.

Q2. What is the difference between INT and BIGINT?

INT uses 4 bytes and supports a smaller numeric range, while BIGINT uses 8 bytes and supports a much larger numeric range.

Q3. What is the difference between VARCHAR and NVARCHAR?

VARCHAR stores variable-length non-Unicode text, while NVARCHAR stores variable-length Unicode text and is suitable for multilingual applications.

Q4. Which data type is used to store dates in SQL Server?

DATE stores date-only values, while DATETIME and DATETIME2 store both date and time.

Q5. Which data type is best for storing money?

DECIMAL is generally suitable for storing exact financial values because it supports defined precision and scale.

Q6. What is the difference between FLOAT and DECIMAL?

FLOAT stores approximate numeric values, while DECIMAL stores exact numeric values within its defined precision and scale.

Q7. What is the BIT data type?

BIT is used to represent 0, 1, or NULL, making it useful for status and Boolean-like fields.

Q8. What is the purpose of BIGINT?

BIGINT is used when a whole-number value may exceed the supported range of INT.

Q9. Can SQL Server store Hindi text?

Yes. NVARCHAR supports Unicode text and can store Hindi, English, and other supported Unicode characters.

Q10. How can I check the data type of a column?

You can use sp_help or INFORMATION_SCHEMA.COLUMNS to retrieve column information and data types.

Conclusion

SQL Server Data Types are an essential part of database design and development. They define how information is stored, processed, and retrieved from database tables.

In this tutorial, we learned about numeric data types such as INT, BIGINT, SMALLINT, TINYINT, DECIMAL, FLOAT, and REAL. We also explored character data types such as VARCHAR, NVARCHAR, CHAR, and NCHAR, along with DATE, TIME, DATETIME, DATETIME2, BIT, BINARY, VARBINARY, and UNIQUEIDENTIFIER.

We also understood the differences between important data types, learned how to convert values using CAST() and CONVERT(), checked table column information, and created a practical Student Management Database using multiple SQL Server data types.

At Param Classes, we focus on helping beginners understand SQL Server and Database Management through simple explanations, practical SQL queries, and real-world examples.

Keep practising these data types and use them carefully while designing your database tables. Choosing the right data type will help you build accurate, efficient, and well-structured databases.

Learn, Practise and Build with Param Classes!