Choosing Data Types – SQL Server | Lesson 8

Published / updated3 Oct 2026

Module 2 · Lesson 8Beginner25 minInterview:FMSA

Before this lesson: Normalization 1NF to 3NF – SQL Server Database Design

Introduction

You know every table and column of the SMS. The last design decision is the data type of each column: what kind of value it holds and how much space it uses.

Data types look like a small detail. They decide whether fees add up exactly, whether a Hindi name is stored correctly, and whether attendance still fits after ten years.

By the end of this lesson, you will be able to:

• Choose between TINYINT, SMALLINT, INT, and BIGINT.
• Explain why money and marks use DECIMAL, never FLOAT.
• Choose between VARCHAR, NVARCHAR, and CHAR.
• Choose between DATE and DATETIME2.
• Justify the data type of every important SMS column.

Why this matters

A school stored fees as FLOAT. At year end, the total collected was off by 0.01 from the bank statement.

The accountant spent two days searching for a payment that was never missing. The cause was the data type, not a person.

Another system stored student names as VARCHAR. A parent typed their child's name in Hindi, and the database saved it as question marks. That data was lost the moment it was saved; no code could bring it back.

The concept

A data type tells SQL Server what kind of value a column holds: a whole number, a decimal, text, a date, and so on. It also sets the size of that value on disk.

Think of containers in a kitchen. You do not store a spoon of salt in a water tank, and you cannot fit a tank of water in a cup. Pick the container that fits the largest value you will ever need, and no larger.

Example: whole numbers

Type	Range	Bytes	SMS example
TINYINT	0 to 255	1	Classes.DisplayOrder (1 to 10), Sections.Capacity
SMALLINT	about -32,000 to 32,000	2	Enrollments.RollNumber
INT	about -2.1 billion to 2.1 billion	4	Most Id columns
BIGINT	about -9 quintillion to 9 quintillion	8	AttendanceRecords.Id, Marks.Id

Why is AttendanceRecords.Id a BIGINT? One school of 10,000 students with 200 school days a year makes 2 million rows a year. If the SMS one day serves 1,000 schools of that size, that is 2 billion rows a year, past the limit of INT.

Example: DECIMAL vs FLOAT

DECIMAL(p, s) stores an exact number. Precision p is the total number of digits, and scale s is how many come after the decimal point. DECIMAL(10, 2) holds up to 99,999,999.99.

FLOAT stores an approximation. It is made for science, where tiny errors do not matter. Money and marks must be exact, so they always use DECIMAL.

SMS column	Type	Largest value
FeePayments.Amount	DECIMAL(10, 2)	99,999,999.99
ExamSubjects.MaxMarks	DECIMAL(5, 2)	999.99
Marks.MarksObtained	DECIMAL(5, 2)	999.99, so 72.5 is stored exactly

Example: VARCHAR vs NVARCHAR

VARCHAR stores text using one byte per English letter. It cannot store letters outside its code page: the limited set of characters it knows.

NVARCHAR stores Unicode: every writing system, including Hindi. It uses two bytes per character for common letters.

The rule for the SMS: names and addresses use NVARCHAR, because people write them in many languages. Codes such as AdmissionNumber or Email use VARCHAR, because we control them and they are always plain English letters and digits.

Always put N before Unicode text in SQL, like N'आरव'. Without the N, SQL Server treats the text as VARCHAR and damages it before it is even stored.

Example: CHAR vs VARCHAR

CHAR(n) always uses exactly n bytes, padding with spaces. VARCHAR(n) uses only what the value needs, plus 2 bytes to remember the length.

Use CHAR only when every value has the same short length. Students.Gender is CHAR(1): always one letter, M, F, or O.

Example: dates and times

Type	Stores	SMS example
DATE	Only the day	DateOfBirth, AttendanceDate, DueDate
DATETIME2(0)	Day and time, to the second	FeePayments.PaidOn, CreatedAt

Use DATE when the time has no meaning. A birthday has no time of day.

The older DATETIME type still appears in old code. Prefer DATETIME2: it is more precise and covers a wider range of dates.

Store timestamps in UTC (Coordinated Universal Time, the world's standard clock) with SYSUTCDATETIME(). Convert to local time only when you display it.

Example: yes or no with BIT

BIT holds 1 (yes) or 0 (no). Use it for flags such as Teachers.IsActive or Marks.IsAbsent. Never store "Yes" and "No" as text.

Example: choosing the key type

Every SMS table uses INT IDENTITY for its Id. IDENTITY makes SQL Server create the next number for you: 1, 2, 3, and so on.

The alternative is a GUID (UNIQUEIDENTIFIER): a random 16-byte value that is unique everywhere. GUIDs are useful when many systems create rows independently. For one school's database, INT is four times smaller and keeps new rows in order, which keeps indexes fast.

Apply it to the SMS

See the differences for yourself. Save this script as queries\008-data-types.sql. It only reads; it creates nothing.

Example: the data types script

queries/008-data-types.sql

-- Lesson 8: see why data types matter. Read-only: it creates nothing.
USE SchoolDb;
GO

-- 1. FLOAT stores an approximation; DECIMAL stores the exact value.
SELECT
    CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) - CAST(0.3 AS FLOAT) AS FloatError,
    CAST(0.1 AS DECIMAL(10, 2)) + CAST(0.2 AS DECIMAL(10, 2)) - CAST(0.3 AS DECIMAL(10, 2)) AS DecimalError;
GO

-- 2. VARCHAR cannot hold Hindi letters; NVARCHAR can.
SELECT
    CAST(N'आरव' AS VARCHAR(20)) AS AsVarchar,
    CAST(N'आरव' AS NVARCHAR(20)) AS AsNvarchar;
GO

-- 3. How many bytes each type uses for the same name.
SELECT
    DATALENGTH(CAST('Aarav' AS VARCHAR(50))) AS VarcharBytes,
    DATALENGTH(CAST(N'Aarav' AS NVARCHAR(50))) AS NvarcharBytes,
    DATALENGTH(CAST('Aarav' AS CHAR(50))) AS CharBytes;
GO

-- 4. DATE keeps only the day; DATETIME2(0) keeps the time to the second.
SELECT
    CAST('2026-07-01 08:45:30' AS DATE) AS AsDate,
    CAST('2026-07-01 08:45:30' AS DATETIME2(0)) AS AsDateTime2;
GO

CAST(value AS type) converts a value to another type. DATALENGTH returns how many bytes a value uses. Press F5 and read the four results.

Result 1: 0.1 + 0.2 - 0.3 should be exactly zero.

FloatError	DecimalError
5.5511151231257827E-17	0.00

FLOAT is wrong by a tiny amount: E-17 means "times 10 to the power of -17". Over thousands of fee payments, tiny errors add up to a visible 0.01.

DECIMAL is exact. SSMS may show fewer digits, but the error is the same.

Result 2:

AsVarchar	AsNvarchar
???	आरव

VARCHAR replaced every Hindi letter with ?. That data is lost for good.

Result 3:

VarcharBytes	NvarcharBytes	CharBytes
5	10	50

NVARCHAR uses twice the space of VARCHAR. CHAR(50) always uses 50 bytes, even for a five-letter name.

Result 4:

AsDate	AsDateTime2
2026-07-01	2026-07-01 08:45:30

Example: a value that does not fit

Try to put 300 into a TINYINT, whose limit is 255. This fails on purpose:

Example

SELECT CAST(300 AS TINYINT);

Output

Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 300.

SQL Server refuses the value instead of saving something wrong. That is exactly what you want: a clear error now, not bad data later.

Example: SMS column types

Here is why the main SMS columns have the types you will create in Module 3:

Column	Type	Why
Students.FirstName	NVARCHAR(50)	Names can be in any language
Students.AdmissionNumber	VARCHAR(15)	A code we control, like ADM-2017-0001
Students.Gender	CHAR(1)	Always one letter
Students.DateOfBirth	DATE	No time of day
Teachers.Phone	VARCHAR(15)	Not a number: no maths, may start with + or 0
FeePayments.Amount	DECIMAL(10, 2)	Money must be exact
FeePayments.PaidOn	DATETIME2(0)	The moment of payment, in UTC
AttendanceRecords.Id	BIGINT	Billions of rows across many schools
Teachers.IsActive	BIT	Yes or no

Common mistakes

1. Using FLOAT for money or marks. Totals drift by small amounts. Fix: use DECIMAL with the precision you need.
2. Using VARCHAR for names. Non-English letters become ?. Fix: use NVARCHAR, and write Unicode text as N'...'.
3. Storing phone numbers as numbers. A leading 0 or + is lost. Fix: use VARCHAR.
4. Making every text column NVARCHAR(MAX). It wastes space and cannot be indexed normally. Fix: choose a realistic maximum length.
5. Storing local time. Times break when users or servers are in different time zones. Fix: store UTC with SYSUTCDATETIME().

In real projects

From 16 years of .NET projects

The most expensive data type mistake I have fixed was an INT key on an audit table that reached 2.1 billion rows on a busy night. Inserts started failing at 2 AM. Changing a key column on a table that size took hours of downtime.

Now, for any table that grows with time and users, I estimate rows per year and multiply by ten years of growth. If the answer is near a billion, it gets BIGINT from day one. Four extra bytes per row is cheap; a production outage is not.

Best practices

• Money and marks: DECIMAL, never FLOAT.
• Names and free text from people: NVARCHAR; codes we control: VARCHAR.
• Dates without time: DATE; moments in time: DATETIME2 in UTC.
• Size every column for its largest real value, and no larger.
• Use BIGINT keys for tables that grow with time across many users.

Recap

• A data type decides what a column holds and how much space it uses.
• TINYINT, SMALLINT, INT, BIGINT differ in range and size.
• DECIMAL is exact; FLOAT is approximate.
• NVARCHAR stores any language; VARCHAR stores a limited code page.
• DATE holds a day; DATETIME2(0) holds a day and a time.
• SQL Server refuses values that do not fit the type.

Homework

Library module task: Choose a data type for every column of Books, BookCopies, and BookIssues. Write one reason for each choice.

A correct result looks like: a table of columns, types, and reasons, with NVARCHAR for titles and authors and DECIMAL for the fine amount.

Show the solution

Column	Type	Reason
Books.Id	INT IDENTITY	A school library has thousands of books, not billions
Books.Isbn	VARCHAR(13)	A 13-character code we control
Books.Title	NVARCHAR(200)	Titles can be in any language
Books.Author	NVARCHAR(100)	Names can be in any language
Books.PublishedYear	SMALLINT	A year fits easily; no need for a full date
BookCopies.CopyNumber	SMALLINT	Small whole numbers
BookCopies.Status	VARCHAR(9)	Fixed words: Available, Issued, Lost
BookIssues.IssuedOn	DATE	The day matters, not the time
BookIssues.ReturnedOn	DATE, allows empty	Empty until the book comes back
BookIssues.FineAmount	DECIMAL(8, 2)	Money must be exact

Quiz

1. Which type should FeePayments.Amount use?

   1. FLOAT
   2. INT
   3. DECIMAL(10, 2)
   4. VARCHAR(10)
   Show answer

   Answer: C. DECIMAL(10, 2) Money must be exact; DECIMAL stores exact values.

2. A parent enters a child's name in Hindi. Which type keeps it correctly?

   1. VARCHAR(50)
   2. NVARCHAR(50)
   3. CHAR(50)
   4. TEXT
   Show answer

   Answer: B. NVARCHAR(50) NVARCHAR stores Unicode, so every language is kept.

3. What does DECIMAL(5, 2) hold at most?

   1. 99999.99
   2. 999.99
   3. 5.2
   4. 99.99
   Show answer

   Answer: B. 999.99 5 digits in total, 2 after the point: 999.99.

4. Which type fits a student's date of birth?

   1. DATETIME2(0)
   2. DATE
   3. VARCHAR(10)
   4. INT
   Show answer

   Answer: B. DATE A birthday has no time of day, so DATE is enough.

5. Why is Teachers.Phone a VARCHAR and not a number?

   1. Numbers are slower
   2. Phones can start with + or 0 and are never used in maths
   3. SQL Server has no number types
   4. To save space
   Show answer

   Answer: B. Phones can start with + or 0 and are never used in maths A phone number is a code, not a quantity; a number type would lose leading characters.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is the difference between VARCHAR and NVARCHAR? concept

VARCHAR stores text in a limited code page, usually one byte per character, so letters outside it become ?. NVARCHAR stores Unicode, so any language works, at about two bytes per character. Use NVARCHAR for names, and write Unicode literals as N'...'.

See the example · Permalink

F Why should money use DECIMAL and not FLOAT? concept

FLOAT stores an approximation in binary, so values like 0.1 cannot be stored exactly and totals drift by tiny amounts. DECIMAL stores exact decimal digits. In SQL Server, 0.1 + 0.2 - 0.3 as FLOAT is about 5.55E-17, while as DECIMAL it is exactly 0.00.

See the example · Permalink

F What is the difference between DATE and DATETIME2? concept

DATE stores only the day, which suits a date of birth or an attendance date. DATETIME2 stores the day and the time, with precision you choose; DATETIME2(0) keeps whole seconds. DATETIME2 replaces the older DATETIME, with a wider range and better precision.

See the example · Permalink

Mid (3-5 yrs)

M How do you choose between TINYINT, SMALLINT, INT, and BIGINT? concept

Pick the smallest type that safely holds the largest value the column will ever need. TINYINT covers 0 to 255, SMALLINT about ±32,000, INT about ±2.1 billion, and BIGINT far more. For tables that grow with time, estimate rows per year times years of growth; attendance across many schools needs BIGINT.

See the example · Permalink

M When would you use CHAR instead of VARCHAR? concept

Use CHAR(n) only when every value has the same short length, because it always uses n bytes and pads with spaces. Gender CHAR(1) is a good fit. For values of varying length, VARCHAR saves space; CHAR(50) stores a five-letter name in 50 bytes.

See the example · Permalink

M What do precision and scale mean in DECIMAL(10, 2)? concept

Precision (10) is the total number of digits, and scale (2) is how many of them come after the decimal point. DECIMAL(10, 2) holds values up to 99,999,999.99, which is enough for any school fee.

See the example · Permalink

Senior (6-9 yrs)

S The total of fees collected is off by 0.01 from the bank statement. How do you investigate? scenario

Check the data types first: a FLOAT or REAL amount column, or a FLOAT used in calculations, causes exactly this kind of drift. Then check rounding in code and reports, for example rounding each line versus rounding the total. Fix the type to DECIMAL and recalculate from the original payments.

How to answer: Give a method: compare totals at each layer (database, API, report) to find where the difference appears. Explain the FLOAT cause with a concrete number. Close with the fix and its cost: changing a column type on a large table needs a migration, so it is cheaper to choose DECIMAL from the start.

See the example · Permalink

S Why store timestamps in UTC instead of local time? concept

UTC never changes with time zones or daylight saving, so every server, user, and report agrees on the same moment. Local times break when users or servers are in different zones, and some local times repeat or are skipped. Store UTC with SYSUTCDATETIME() and convert only for display.

How to answer: Explain the problem with a concrete case: a payment at 11:30 PM local time lands on a different day in another time zone. Give the rule (store UTC, convert at the edges). Note the trade-off: every display needs a conversion, and dates without a time, like a date of birth, should stay plain DATE.

See the example · Permalink

Architect / Lead (10+ yrs)

A Would you use INT IDENTITY or GUID (UNIQUEIDENTIFIER) primary keys? design

INT IDENTITY is 4 bytes and always increasing, so new rows go to the end of the index and joins stay fast. A GUID is 16 bytes and random, which fragments indexes, but it can be created anywhere without a central counter. Use INT or BIGINT for a single database, and GUIDs when many systems create rows independently.

How to answer: Compare size, insert order, and index fragmentation, then the cases where GUIDs win: offline clients, merging databases, or not exposing a guessable Id in public URLs. Give the middle road: an INT key inside the database plus a GUID "public id" column for outside use.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-8-data-types (code repository coming soon)
• Files: queries/008-data-types.sql
• Video: coming soon

What's next

The design is complete. Module 3 turns it into real tables, starting with the master tables of the school. Lesson 9: Master tables

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.