Master Tables with CREATE TABLE – SQL Server | Lesson 9

Published / updated3 Oct 2026

Introduction

The design is finished. Now you build it. In this module, you turn the ER diagram into real tables in SchoolDb, one group at a time.

You start with the master tables: the lists that everything else points to. They are academic years, classes, sections, and subjects.

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

Write a CREATE TABLE statement with columns, types, and NOT NULL.
Make SQL Server number rows for you with IDENTITY.
Add a named primary key.
Write a create script that is safe to run more than once.
Check your tables with sys.tables.

Why this matters

A student is enrolled in a section, for an academic year. A mark belongs to a subject. Before any of that can be saved, these lists must exist.

If the master tables are wrong, every table that points to them inherits the problem. Getting these four right is the foundation for the other 18.

The concept

Example: master tables vs transaction tables

Kind	What it holds	Changes	SMS examples
Master table (also called lookup or reference table)	Lists that other data points to	Rarely; set up by an admin	AcademicYears, Classes, Sections, Subjects
Transaction table	Events that happen every day	Constantly	AttendanceRecords, Marks, FeePayments

Think of a school calendar and a diary. The calendar (master) is printed once a year. The diary (transactions) gets new lines every day, and each line refers to a date on the calendar.

Example: the CREATE TABLE statement

CREATE TABLE is a DDL command that creates a new, empty table. You give it a name and a list of columns. Each column has a name, a data type, and a rule about empty values.

Example
CREATE TABLE dbo.Subjects
(
    Id   INT IDENTITY(1, 1) NOT NULL,
    Code VARCHAR(10)        NOT NULL,
    Name NVARCHAR(50)       NOT NULL,
    CONSTRAINT PK_Subjects PRIMARY KEY (Id)
);

Example: NULL and NOT NULL

NULL means "no value": not zero, not an empty string, just unknown or missing.

NOT NULL means the column must always have a value. Every subject must have a name.
NULL means the column may be left empty. A teacher's qualification might be unknown.

If you write neither, SQL Server usually allows NULL. Always write it explicitly, so the rule is clear to the next reader.

Example: IDENTITY

IDENTITY(1, 1) tells SQL Server to fill the column automatically: start at 1 and add 1 for each new row. You never type the Id yourself.

Numbers can have gaps. If an insert fails, the number it used is not reused. That is normal: an Id identifies a row, it does not count rows.

Example: the primary key

A primary key is the column (or columns) that identifies each row. SQL Server makes sure it is never NULL and never repeated.

Example
CONSTRAINT PK_Subjects PRIMARY KEY (Id)

CONSTRAINT PK_Subjects gives the rule a name. Without a name, SQL Server invents one like PK__Subjects__3214EC07A1B2C3D4, which is different on every server. Named constraints make scripts and error messages readable.

We use a surrogate key: an Id with no meaning outside the database. A natural key, like a subject code, is real-world data that might change one day. Natural keys still matter; in Lesson 10 you make them unique.

Example: a create script that runs twice

Running CREATE TABLE for a table that exists fails with an error. So every create statement in this course is wrapped in a check:

Example
IF OBJECT_ID(N'dbo.Subjects', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Subjects
    (
        Id INT IDENTITY(1, 1) NOT NULL,
        CONSTRAINT PK_Subjects PRIMARY KEY (Id)
    );
END;

OBJECT_ID returns the ID of an object, or NULL if it does not exist. N'U' means "a user table". BEGIN and END group the statements that belong to the IF.

Example: choosing each column of AcademicYears

Every column is a small decision. Here is the thinking behind one table, using the data type rules from Lesson 8:

Column	Type	Why
`Id`	`INT IDENTITY(1, 1)`	A school has a few dozen years, so `INT` is plenty; SQL Server numbers them
`Name`	`VARCHAR(7)`	Always the short form `2026-27`: seven plain characters
`StartDate`	`DATE`	The first day of the year; no time of day
`EndDate`	`DATE`	The last day; Lesson 10 checks it is after `StartDate`
`IsCurrent`	`BIT`	Yes or no: is this the year the school is in now?

All five columns are NOT NULL. An academic year without a name or dates makes no sense, so the database should never accept one.

Example: the naming rules

The whole course follows one set of names, written in the course reference:

Thing	Rule	Example
Table	Plural, PascalCase	`AcademicYears`
Column	Singular, PascalCase	`StartDate`
Primary key column	Always `Id`	`Sections.Id`
Foreign key column	Singular table name plus `Id`	`Sections.ClassId`
Primary key constraint	`PK_` plus the table name	`PK_Sections`

PascalCase means each word starts with a capital letter and there are no spaces. With fixed rules, you can guess a name correctly without looking it up.

Apply it to the SMS

Create the four master tables. Save this as database\009-master-tables.sql in your project folder.

Example: the master tables script

database/009-master-tables.sql
-- Lesson 9: create the four master tables.
-- Safe to run more than once: each table is created only if it does not exist yet.
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.AcademicYears', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.AcademicYears
    (
        Id        INT IDENTITY(1, 1) NOT NULL,
        Name      VARCHAR(7)         NOT NULL,  -- for example 2026-27
        StartDate DATE               NOT NULL,
        EndDate   DATE               NOT NULL,
        IsCurrent BIT                NOT NULL,
        CONSTRAINT PK_AcademicYears PRIMARY KEY (Id)
    );
END;
GO

IF OBJECT_ID(N'dbo.Classes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Classes
    (
        Id           INT IDENTITY(1, 1) NOT NULL,
        Name         NVARCHAR(20)       NOT NULL,  -- Class 1 to Class 10
        DisplayOrder TINYINT            NOT NULL,  -- 1 to 10, for sorting
        CONSTRAINT PK_Classes PRIMARY KEY (Id)
    );
END;
GO

IF OBJECT_ID(N'dbo.Sections', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Sections
    (
        Id       INT IDENTITY(1, 1) NOT NULL,
        ClassId  INT                NOT NULL,  -- which class this section belongs to
        Name     VARCHAR(2)         NOT NULL,  -- A, B, ...
        Capacity TINYINT            NOT NULL,
        CONSTRAINT PK_Sections PRIMARY KEY (Id)
    );
END;
GO

IF OBJECT_ID(N'dbo.Subjects', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Subjects
    (
        Id   INT IDENTITY(1, 1) NOT NULL,
        Code VARCHAR(10)        NOT NULL,  -- for example MATH
        Name NVARCHAR(50)       NOT NULL,
        CONSTRAINT PK_Subjects PRIMARY KEY (Id)
    );
END;
GO

-- Check: list the tables in SchoolDb.
SELECT name AS TableName
FROM sys.tables
ORDER BY name;
GO

Example: check the tables

Press F5. The last query lists the tables:

TableName
AcademicYears
Classes
Sections
Subjects

Press F5 again. No error, and the same four tables: the script is re-runnable.

In Object Explorer, expand SchoolDb > Tables and press Refresh to see them. Expand a table and its Columns folder to see each column with its type. The Keys folder shows PK_AcademicYears with a small key icon.

Example: what is still missing

These tables accept bad data right now. Nothing stops two subjects with the code MATH, a section pointing to class 99, or an academic year that ends before it starts. In Lesson 10, you add the rules that block all three.

Notice that Sections.ClassId is just a number for now. It looks like a link to Classes, but SQL Server does not know that yet. A foreign key turns it into a real link.

Add the script to database so run-all.cmd picks it up. From now on, every lesson adds the next numbered script.

Common mistakes

Leaving out dbo. Tables may end up in another schema for some users. Fix: always write dbo.TableName.
Not naming the primary key. You get a random name that differs on every server. Fix: write CONSTRAINT PK_TableName PRIMARY KEY (Id).
Typing values into an IDENTITY column. The insert fails. Fix: leave the Id out of your INSERT; SQL Server fills it.
Forgetting NOT NULL. Required data can then be left empty. Fix: decide NULL or NOT NULL for every column, and write it.
Using DROP TABLE to "start again". It deletes all the data too. Fix: use re-runnable scripts, and never put DROP TABLE in a normal script.

In real projects

From 16 years of .NET projects

On real teams, nobody creates tables by clicking in SSMS. Every change is a numbered script, reviewed like code and stored in Git. The same scripts run on a developer's laptop, the test server, and production, in the same order.

I once saw a release fail at midnight because a script assumed a table did not exist, but a hotfix had already created it. Since then, every script I approve checks before it creates. Your IF OBJECT_ID(...) IS NULL is that habit.

Best practices

Name every table in the plural and every key Id, as in the reference.
Name every constraint: PK_, FK_, UQ_, CK_, DF_.
Write NULL or NOT NULL on every column.
Make every create script re-runnable with IF OBJECT_ID(...) IS NULL.
Keep one numbered script per change in the database folder.

Recap

Master tables hold lists that other tables point to.
CREATE TABLE defines columns, types, and NULL rules.
IDENTITY(1, 1) numbers rows automatically; gaps are normal.
A primary key identifies each row; name it with CONSTRAINT PK_....
IF OBJECT_ID(N'dbo.X', N'U') IS NULL makes a create script re-runnable.
sys.tables lists the tables in the current database.

Homework

Library module task: Create the Books table in SchoolDb with the columns from your Lesson 8 homework: Id, Isbn, Title, Author, Publisher, PublishedYear, and Category. Make the script re-runnable and name the primary key.

A correct result looks like: Books appears in sys.tables, and running the script twice gives no error.

Show the solution

library-assignment/009-books.sql
-- Library homework, Lesson 9: the Books table.
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.Books', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Books
    (
        Id            INT IDENTITY(1, 1) NOT NULL,
        Isbn          VARCHAR(13)        NOT NULL,
        Title         NVARCHAR(200)      NOT NULL,
        Author        NVARCHAR(100)      NOT NULL,
        Publisher     NVARCHAR(100)      NULL,
        PublishedYear SMALLINT           NULL,
        Category      NVARCHAR(50)       NOT NULL,
        CONSTRAINT PK_Books PRIMARY KEY (Id)
    );
END;
GO

Quiz

Which of these is a master table?
1. AttendanceRecords
2. Marks
3. Subjects
4. FeePayments
Show answer
Answer: C. Subjects Subjects is a list other tables point to; the others record daily events.
What does IDENTITY(1, 1) do?
1. Makes the column unique text
2. Numbers new rows automatically, starting at 1 and adding 1
3. Creates a foreign key
4. Makes the column optional
Show answer
Answer: B. Numbers new rows automatically, starting at 1 and adding 1 IDENTITY fills the column for you: seed 1, increment 1.
What does NULL mean in a column?
1. Zero
2. An empty string
3. No value: unknown or missing
4. False
Show answer
Answer: C. No value: unknown or missing NULL is the absence of a value, not zero or an empty string.
Why name a constraint, as in CONSTRAINT PK_Subjects?
1. SQL Server requires it
2. So scripts and error messages use the same readable name on every server
3. To make it faster
4. To allow NULL values
Show answer
Answer: B. So scripts and error messages use the same readable name on every server Unnamed constraints get random names that differ between servers.
What does IF OBJECT_ID(N'dbo.Subjects', N'U') IS NULL check?
1. Whether the table is empty
2. Whether the table does not exist yet
3. Whether the Id column is NULL
4. Whether the user is an admin
Show answer
Answer: B. Whether the table does not exist yet OBJECT_ID returns NULL when the table does not exist, so the create runs only once.

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 does CREATE TABLE do, and what do you define for each column? concept

CREATE TABLE creates a new, empty table. For each column you define a name, a data type, and whether it allows NULL. You usually also define the primary key, and often other constraints, in the same statement.

See the example · Permalink

F What is an IDENTITY column? concept

An IDENTITY(seed, increment) column is filled automatically by SQL Server. IDENTITY(1, 1) starts at 1 and adds 1 for each new row, so you never supply the value yourself. It is the usual way to create surrogate primary keys like Id.

See the example · Permalink

F What is a primary key? Can it contain NULL or duplicate values? concept

A primary key is the column, or set of columns, that uniquely identifies each row. It can never contain NULL and never contain duplicates; SQL Server enforces both. A table has at most one primary key.

See the example · Permalink

Mid (3-5 yrs)

M Why name constraints yourself instead of letting SQL Server name them? concept

SQL Server generates names like PK__Subjects__3214EC07A1B2C3D4, which differ on every server. Named constraints such as PK_Subjects appear clearly in error messages and can be referenced reliably in change scripts, for example to drop or alter them.

See the example · Permalink

M Does an IDENTITY column guarantee numbers without gaps? concept

No. A failed or rolled-back insert still uses up its number, and a server restart can skip a block of cached values. Gaps are normal: an identity value identifies a row, it does not count rows. If the business needs gapless numbers, such as invoice numbers, generate them separately inside a transaction.

See the example · Permalink

M What is the difference between a master (lookup) table and a transaction table? concept

A master table holds lists that other data refers to and rarely changes, such as classes and subjects. A transaction table records events that happen all the time, such as attendance or payments. Transaction rows point to master rows, not the other way round.

See the example · Permalink

Senior (6-9 yrs)

S A deployment script fails in production with "There is already an object named 'Students'". How should deployment scripts be written? scenario

They should be idempotent: safe to run more than once. Each change checks first, for example IF OBJECT_ID(N'dbo.Students', N'U') IS NULL before CREATE TABLE, or COL_LENGTH before adding a column. Then a re-run after a partial failure, or a hotfix that already applied a change, does not break the release.

How to answer: Name the principle (idempotent scripts), show the check pattern for tables, columns, and constraints, and explain the real cause: environments drift. Mention the trade-off: checks add lines to every script, while migration tools track applied scripts in a table instead.

See the example · Permalink

S Why does the SMS use an Id column as primary key instead of AdmissionNumber? design

Id is a surrogate key: small, never changes, and has no business meaning. AdmissionNumber is a natural key from the real world, and real-world values can change or be entered wrongly; changing a primary key means updating every foreign key that uses it. So the natural key is protected with a UNIQUE constraint instead.

How to answer: Define surrogate and natural keys, then use the SMS case: a typo in an admission number must be fixable without touching attendance, marks, and fees. Note when natural keys are fine, such as small code tables that never change, and that you still enforce the natural key's uniqueness.

See the example · Permalink

Architect / Lead (10+ yrs)

A How do you manage database schema changes across development, test, and production over years? design

Every change is a versioned script stored in source control and reviewed like code. The same scripts run in the same order in every environment, usually through a migration tool that records which scripts were applied. Nobody changes production by hand.

How to answer: Describe the pipeline: numbered, idempotent scripts, code review, automated deployment, and a record of applied changes. Compare approaches: migration tools that run scripts in order versus state-based tools that compare a model with the database. Mention the hard parts: data migrations on large tables, and rolling back.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-9-master-tables (code repository coming soon)
Files: database/009-master-tables.sql, library-assignment/009-books.sql
Video: coming soon

What's next

The master tables exist but accept bad data. Next, you add the rules that stop it. Lesson 10: Constraints

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

Introduction​

Why this matters​

The concept​

Example: master tables vs transaction tables​

Example: the CREATE TABLE statement​

Example: NULL and NOT NULL​

Example: IDENTITY​

Example: the primary key​

Example: a create script that runs twice​

Example: choosing each column of AcademicYears​

Example: the naming rules​

Apply it to the SMS​

Example: the master tables script​

Example: check the tables​

Example: what is still missing​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: master tables vs transaction tables

Example: the CREATE TABLE statement

Example: NULL and NOT NULL

Example: IDENTITY

Example: the primary key

Example: a create script that runs twice

Example: choosing each column of AcademicYears

Example: the naming rules

Apply it to the SMS

Example: the master tables script

Example: check the tables

Example: what is still missing

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next