Published / updated29 Aug 2026

Tables and Data Types

Before you start

You need: a working SSMS connection (Article 01).

Time: about 50 minutes, plus the practice.

Learning objective

Design a table where every column has the correct type and size, and predict how NULL affects comparisons and aggregates.

Topics

• CREATE TABLE and ALTER TABLE
• Numeric types, and why money is not FLOAT
• String types: VARCHAR, NVARCHAR, and the N prefix
• Date and time types
• BIT, UNIQUEIDENTIFIER, and enum-style columns
• IDENTITY
• NULL — three-valued logic
• ISNULL and COALESCE

Creating a table

CREATE TABLE dbo.Student
(
    Id          INT              IDENTITY(1,1) NOT NULL,
    PublicId    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    SchoolId    INT              NOT NULL,
    Name        NVARCHAR(100)    NOT NULL,
    RollNumber  NVARCHAR(20)     NOT NULL,
    ClassName   NVARCHAR(10)     NOT NULL,
    Section     NVARCHAR(1)      NOT NULL,
    DateOfBirth DATE             NOT NULL,
    ParentName  NVARCHAR(100)    NOT NULL,
    ParentPhone NVARCHAR(15)     NOT NULL,
    Address     NVARCHAR(250)    NULL,
    Status      TINYINT          NOT NULL DEFAULT 0
);

Every column declares a type, a size where relevant, and whether it accepts NULL. Those three decisions are the whole of table design at this stage, and getting them wrong is expensive to change later on a populated table.

Changing an existing table:

ALTER TABLE dbo.Student ADD Email NVARCHAR(150) NULL;

ALTER TABLE dbo.Student ALTER COLUMN Address NVARCHAR(500) NULL;

ALTER TABLE dbo.Student DROP COLUMN Email;

Adding a NOT NULL column to a table with rows fails unless you supply a DEFAULT — there is no value for the existing rows otherwise:

ALTER TABLE dbo.Student
ADD IsHosteller BIT NOT NULL DEFAULT 0;

Numeric types

Type	Range	Storage	Use for
TINYINT	0 to 255	1 byte	Small enums — Status
SMALLINT	±32,767	2 bytes	Small counts
INT	±2.1 billion	4 bytes	Ids, most counts
BIGINT	±9.2 quintillion	8 bytes	High-volume ids
DECIMAL(p,s)	Exact	5–17 bytes	Money, marks, percentages
FLOAT	Approximate	8 bytes	Scientific measurement only
BIT	0, 1, NULL	1 bit	Boolean flags

Never use FLOAT for money

DECLARE @wrong FLOAT = 0.1;
DECLARE @right DECIMAL(18,2) = 0.1;

SELECT @wrong * 3 AS FloatResult,     -- 0.30000000000000004
       @right * 3 AS DecimalResult;   -- 0.30

FLOAT is binary floating point and cannot represent most decimal fractions exactly. Fee totals drift by fractions of a rupee, and a receipt that does not reconcile with a balance is a real support problem.

DECIMAL(p,s) is exact. p is total digits, s is digits after the point:

TotalFees   DECIMAL(18,2)   -- up to 9,999,999,999,999,999.99
Percentage  DECIMAL(5,2)    -- up to 999.99
MarksObtained DECIMAL(5,2)  -- 100.00, or 87.50 for half marks

MONEY also exists. It has only four decimal places and rounds unexpectedly in division — use DECIMAL.

Integer division

SELECT 87 / 100;                        -- 0
SELECT 87.0 / 100;                      -- 0.87
SELECT CAST(87 AS DECIMAL(5,2)) / 100;  -- 0.870000

Dividing two integers gives an integer. A percentage calculated as MarksObtained / MaxMarks * 100 where both are INT returns 0 for every student below 100%. Cast one side first.

String types

Type	Stores	Bytes per character
CHAR(n)	Fixed length, padded	1
VARCHAR(n)	Variable length	1
NCHAR(n)	Fixed, Unicode	2
NVARCHAR(n)	Variable, Unicode	2
VARCHAR(MAX)	Up to 2 GB	1
NVARCHAR(MAX)	Up to 2 GB	2

The N prefix means Unicode. Without it, the column cannot store characters outside its collation's code page — Telugu, Hindi, Tamil, and most non-Latin scripts become ?.

For an Indian school system storing student and parent names, NVARCHAR is the correct default. The doubled storage is not a real cost at these row counts, and losing a name to ? is a defect nobody can fix afterwards because the original characters are gone.

Literals need the prefix too:

INSERT INTO dbo.Student (Name) VALUES (N'రవి కుమార్');   -- correct
INSERT INTO dbo.Student (Name) VALUES ('రవి కుమార్');    -- becomes ??? ?????

CHAR versus VARCHAR

CHAR(20) always occupies 20 characters, padding with spaces. 'A' stored in CHAR(1) is fine; 'A' in CHAR(20) comes back as 'A ', and comparisons against 'A' still succeed but string concatenation shows the padding.

Use CHAR only for genuinely fixed-length values. Use VARCHAR/NVARCHAR for everything else.

Sizing

Name        NVARCHAR(100)   -- generous but bounded
RollNumber  NVARCHAR(20)    -- format is NCA-2024-0012
ParentPhone NVARCHAR(15)    -- string, not a number
Email       NVARCHAR(150)
Address     NVARCHAR(250)

Phone numbers are strings. Stored as INT they lose leading zeros, cannot hold +91, and overflow for international numbers. You never do arithmetic on a phone number.

Avoid NVARCHAR(MAX) unless the content is genuinely unbounded, such as a remarks field. MAX columns are stored off-row, cannot be indexed normally, and make queries slower.

A declared size is also a validation: NVARCHAR(20) on RollNumber means a 500-character value is rejected rather than stored.

Date and time types

Type	Holds	Storage
DATE	Date only	3 bytes
TIME	Time only	3–5 bytes
DATETIME2(n)	Date and time, year 1–9999	6–8 bytes
DATETIMEOFFSET	With time-zone offset	8–10 bytes
DATETIME	Legacy, 1753 onward, ~3ms accuracy	8 bytes

Use DATE when there is no time — DateOfBirth, ExamDate, JoiningDate. Storing a birthday as DATETIME invites a midnight-boundary bug the first time someone compares it.

Use DATETIME2 for timestamps. DATETIME is a legacy type with a rounding quirk: it rounds to increments of .000, .003, and .007 seconds, so a value of 23:59:59.999 becomes the next day.

Write date literals in the unambiguous ISO format:

WHERE ExamDate >= '2024-06-15'          -- ISO, safe on any server
WHERE ExamDate >= '15/06/2024'          -- depends on server language settings

'15/06/2024' fails or silently misreads on a server set to US English. This is a classic bug that appears only after deployment.

Date range filtering

-- Wrong — excludes everything after midnight on the 30th
WHERE PaidOn BETWEEN '2024-06-01' AND '2024-06-30'

-- Correct
WHERE PaidOn >= '2024-06-01' AND PaidOn < '2024-07-01'

BETWEEN on a date-time column compares against 2024-06-30 00:00:00, so a payment at 10 a.m. on the 30th is excluded. Use >= and < with the start of the next day.

Also avoid wrapping the column in a function:

-- Cannot use an index — the function is applied to every row
WHERE YEAR(PaidOn) = 2024

-- Can use an index
WHERE PaidOn >= '2024-01-01' AND PaidOn < '2025-01-01'

BIT, UNIQUEIDENTIFIER and enums

IsPresent   BIT              NOT NULL DEFAULT 1
IsAbsent    BIT              NOT NULL DEFAULT 0
PublicId    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
Status      TINYINT          NOT NULL DEFAULT 0

BIT accepts 0, 1, and NULL. Filter as WHERE IsPresent = 1, not WHERE IsPresent = 'true'.

UNIQUEIDENTIFIER holds a GUID. It exists here because exposing sequential INT ids in URLs lets anyone enumerate records — PublicId goes in the URL, Id stays internal.

Do not make a GUID your clustered primary key. NEWID() produces random values, so every insert lands in the middle of the index and fragments it. Keep INT IDENTITY as the clustered key and put a separate unique index on PublicId.

Enum-style columns store the number and rely on the application for meaning:

Status TINYINT NOT NULL DEFAULT 0   -- 0 Active, 1 Inactive, 2 Graduated, 3 Transferred

The comment is not enough. Add a CHECK constraint — covered in the next article — so the database rejects a value of 9.

IDENTITY

Id INT IDENTITY(1,1) NOT NULL

Starts at 1, increments by 1. Never supply it on insert. Retrieve the generated value with:

INSERT INTO dbo.Student (SchoolId, Name, RollNumber, ClassName, Section,
                         DateOfBirth, ParentName, ParentPhone)
VALUES (1, N'Sneha Patel', 'NCA-2024-0044', '9th', 'A',
        '2010-03-14', N'Mahesh Patel', '9812345670');

SELECT SCOPE_IDENTITY() AS NewStudentId;

Use SCOPE_IDENTITY(), not @@IDENTITY. @@IDENTITY returns the last identity generated in the session, including one created by a trigger on another table — so a trigger that logs to AuditLog makes @@IDENTITY return the audit row's id instead of the student's.

Identity values are not guaranteed contiguous. A rolled-back transaction consumes numbers permanently. Gaps are normal and are not a defect.

NULL

NULL is not zero and not an empty string. It means unknown, and it makes SQL logic three-valued: TRUE, FALSE, UNKNOWN.

SELECT 1 WHERE NULL = NULL;      -- no rows: UNKNOWN, not TRUE
SELECT 1 WHERE NULL <> 'Ravi';   -- no rows: UNKNOWN
SELECT 1 WHERE NULL IS NULL;     -- one row: this is the correct test

Only IS NULL and IS NOT NULL test for it.

This is why a filter silently loses rows:

-- Students with no Section recorded are EXCLUDED — NULL <> 'A' is UNKNOWN
SELECT Name FROM dbo.Student WHERE Section <> 'A';

-- Include them explicitly
SELECT Name FROM dbo.Student WHERE Section <> 'A' OR Section IS NULL;

A WHERE clause keeps only rows where the condition is TRUE. UNKNOWN is discarded exactly like FALSE, with no warning. When a report is missing records nobody can explain, a nullable column in the WHERE clause is the first place to look.

NULL in aggregates and concatenation

-- COUNT(*) counts rows; COUNT(column) skips NULLs
SELECT COUNT(*)       AS AllStudents,      -- 100
       COUNT(Address) AS WithAddress       -- 62
FROM   dbo.Student;

-- AVG ignores NULLs — it does NOT treat them as zero
SELECT AVG(MarksObtained) FROM dbo.ExamResult;

AVG skipping NULL is usually what you want for absent students, and occasionally not — decide deliberately rather than by accident.

-- Any NULL in a concatenation makes the whole result NULL
SELECT Name + ', ' + Address FROM dbo.Student;              -- NULL where Address is NULL
SELECT Name + ', ' + ISNULL(Address, 'No address') FROM dbo.Student;

ISNULL and COALESCE

SELECT ISNULL(Address, 'Not recorded')                AS Address,
       COALESCE(ParentPhone, AlternatePhone, 'None')  AS ContactNumber
FROM   dbo.Student;

ISNULL takes exactly two arguments and is SQL Server-specific. COALESCE takes any number, returns the first non-NULL, and is standard SQL.

One difference that causes truncation:

DECLARE @short VARCHAR(3) = NULL;

SELECT ISNULL(@short, 'Not recorded');    -- 'Not' — truncated to VARCHAR(3)
SELECT COALESCE(@short, 'Not recorded');  -- 'Not recorded'

ISNULL returns the type of the first argument. COALESCE resolves to the highest-precedence type across all arguments. Prefer COALESCE.

NOT NULL as a design decision

Name        NVARCHAR(100) NOT NULL,   -- a student must have a name
ParentPhone NVARCHAR(15)  NOT NULL,   -- the school requires a contact
Address     NVARCHAR(250) NULL        -- genuinely optional

Every nullable column is a case your queries must handle. Make a column nullable only when "not known" is a real, meaningful state — not because it is easier to insert.

Errors you will hit

Message	Cause	Fix
String or binary data would be truncated	Value longer than the column	Widen the column, or validate the input
Arithmetic overflow error converting numeric to data type numeric	Value exceeds the DECIMAL precision	Check DECIMAL(18,2) is wide enough
Conversion failed when converting the varchar value to int	Text in a numeric column	Fix the data or the type
Cannot insert the value NULL into column	Column is NOT NULL with no default	Supply a value or add a DEFAULT
Fee totals off by paise	Column is FLOAT	DECIMAL(18,2) for money, always
Dates behave oddly across machines	Ambiguous date literal	Use ISO '2024-06-15'

FLOAT for money produces no error at all. The totals are simply slightly wrong, and nobody notices until a reconciliation.

Common mistakes

• FLOAT for money, producing totals that do not reconcile
• VARCHAR instead of NVARCHAR, losing non-Latin names to ?
• Omitting the N prefix on a Unicode literal
• Phone numbers stored as INT
• INT / INT division returning 0 for every percentage
• BETWEEN on a date-time column, dropping the last day
• Non-ISO date literals that break after deployment
• NVARCHAR(MAX) where a bounded size would do
• = NULL instead of IS NULL
• A <> filter silently excluding NULL rows
• @@IDENTITY instead of SCOPE_IDENTITY()
• A GUID as the clustered primary key
• Treating identity gaps as a bug

Practice

Create the full Student, Teacher, Staff, Subject, Exam, ExamResult, FeeAccount, and FeePayment tables with correct types and nullability for each column. Justify, in one line per column, why each is nullable or not.

Then prove the traps, running each and recording the result:

1. SELECT CAST(0.1 AS FLOAT) * 3 and the same with DECIMAL(18,2).
2. Insert a Telugu or Hindi name into a VARCHAR column and into an NVARCHAR column with the N prefix. Compare.
3. SELECT 87 / 100 and SELECT 87.0 / 100.
4. Insert five students, leave Address NULL for two, then run SELECT COUNT(*), COUNT(Address).
5. Run WHERE Section <> 'A' on data where one row has NULL section. Count what you get versus what you expected.
6. Insert a payment dated 2024-06-30 10:00, then filter with BETWEEN '2024-06-01' AND '2024-06-30'. Confirm it is missing.
7. Compare ISNULL(@short, 'Not recorded') and COALESCE with @short declared VARCHAR(3).

Exercises 5 and 6 are the two that silently corrupt reports in production. Make both mistakes here, once, deliberately.

You can now

• Choose the correct type and size for any column
• Say why money is DECIMAL(18,2) and never FLOAT
• Use NVARCHAR where names may not be ASCII
• Write date literals that mean the same on every server
• Predict what a too-narrow column does to an insert

Review questions

1. Why must money be DECIMAL and never FLOAT?
2. What does the N prefix do, and what is lost without it?
3. Why does WHERE Section <> 'A' exclude rows where Section is NULL?
4. Why use SCOPE_IDENTITY() rather than @@IDENTITY?

Next: Keys and constraints