Sample Data and 10,000 Students with INSERT – SQL Server | Lesson 15
Introduction
All 22 tables exist, and all of them are empty. In this lesson, you fill them in two steps.
First, the fixed sample data: the school, its teachers, and the 12 students you have met in every lesson. Second, a generator that adds 10,000 more students with 60 days of attendance and two exams of marks.
By the end of this lesson, you will be able to:
- Add rows with
INSERT INTO ... VALUES, one or many at a time. - Explain what happens to columns you leave out of an insert.
- Insert fixed
Idvalues withSET IDENTITY_INSERT. - Write a seed script that is safe to run more than once.
- Run a generator that creates over 700,000 rows in seconds.
Why this matters
Module 4 is about writing queries. You cannot learn queries on empty tables, and you cannot learn them well on random data either. The fixed sample data gives every lesson the same, known answers: Aarav is student 1, Kabir was absent on 1 July.
The 10,000 students serve a different purpose. With 12 students, every query is instant, even a badly written one.
With 600,000 attendance rows, a slow query is obviously slow. That is where Module 5's performance lessons start.
The concept
Example: INSERT INTO ... VALUES
INSERT INTO adds new rows. You list the columns, then the values in the same order:
INSERT INTO dbo.Subjects (Code, Name)
VALUES ('MATH', N'Mathematics');
One VALUES clause can add many rows, separated by commas:
INSERT INTO dbo.Subjects (Code, Name)
VALUES
('ENG', N'English'),
('SCI', N'Science');
Always list the columns. If someone adds a column to the table later, an insert without a column list breaks, or worse, puts values in the wrong columns.
Example: columns you leave out
When an insert leaves a column out, SQL Server fills it in this order:
- With the column's
DEFAULT, if it has one:Sections.Capacitybecomes 40. - With the next number, if it is an
IDENTITYcolumn. - With
NULL, if the column allows it. - Otherwise, the insert fails with "Cannot insert the value NULL".
That is why the sample script never mentions CreatedAt: its default fills in the time.
Example: fixed Ids with IDENTITY_INSERT
Normally you cannot type a value into an IDENTITY column. For seed data, we want
fixed Ids, so every lesson can say "Aarav is student 1". SET IDENTITY_INSERT allows
it for one table at a time:
SET IDENTITY_INSERT dbo.Subjects ON;
INSERT INTO dbo.Subjects (Id, Code, Name) VALUES (1, 'ENG', N'English');
SET IDENTITY_INSERT dbo.Subjects OFF;
With IDENTITY_INSERT on, you must list the Id column. Turn it off straight away.
New rows added later continue from the highest Id.
Example: a seed script that runs twice
Running the same inserts twice breaks unique rules. So each block first checks that its table is still empty:
IF NOT EXISTS (SELECT 1 FROM dbo.Subjects)
BEGIN
INSERT INTO dbo.Subjects (Code, Name) VALUES ('ENG', N'English');
END;
EXISTS is true when the query inside returns at least one row. SELECT 1 returns
the number 1 for each row; we only care whether there is a row at all.
Example: the insert order
Foreign keys decide the order. A row can only point to rows that already exist, so the sample script fills the tables in this order:
| Step | Tables | Why this position |
|---|---|---|
| 1 | AcademicYears, Classes, Sections, Subjects | Point to nothing (Sections points to Classes, inserted just before) |
| 2 | Teachers, Students, Guardians, StudentGuardians | People first, then the links between them |
| 3 | Enrollments, ClassTeachers, SubjectAllocations | Need students, sections, teachers, and years |
| 4 | AttendanceRecords, Exams, ExamSubjects, Marks | Need enrollments, classes, and subjects |
| 5 | FeeStructures, FeeInvoices, FeeInvoiceItems, FeePayments | Need enrollments and classes |
| 6 | Roles, Users, UserRoles | Users point to teachers and guardians |
This is the same order in which you created the tables in Lessons 9 to 14.
Apply it to the SMS
Step 1: the fixed sample data
Save this as database\015-1-sample-data.sql and run it. It inserts every table in
dependency order: masters, people, enrollments, attendance and exams, fees, users.
Every value matches the course reference.
Show database/015-1-sample-data.sql
-- Lesson 15 (part 1): the fixed sample data used in every lesson.
-- Rows get fixed Ids (IDENTITY_INSERT), so "Aarav Sharma is student 1" is always true.
-- Safe to run more than once: each table is filled only when it is empty.
USE SchoolDb;
GO
-- ── Masters ─────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM dbo.AcademicYears)
BEGIN
SET IDENTITY_INSERT dbo.AcademicYears ON;
INSERT INTO dbo.AcademicYears (Id, Name, StartDate, EndDate, IsCurrent)
VALUES
(1, '2025-26', '2025-06-01', '2026-03-31', 0),
(2, '2026-27', '2026-06-01', '2027-03-31', 1);
SET IDENTITY_INSERT dbo.AcademicYears OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.Classes)
BEGIN
SET IDENTITY_INSERT dbo.Classes ON;
INSERT INTO dbo.Classes (Id, Name, DisplayOrder)
VALUES
(1, N'Class 1', 1), (2, N'Class 2', 2), (3, N'Class 3', 3), (4, N'Class 4', 4),
(5, N'Class 5', 5), (6, N'Class 6', 6), (7, N'Class 7', 7), (8, N'Class 8', 8),
(9, N'Class 9', 9), (10, N'Class 10', 10);
SET IDENTITY_INSERT dbo.Classes OFF;
END;
-- Sections A and B in every class. Capacity is left out, so the default (40) is used.
IF NOT EXISTS (SELECT 1 FROM dbo.Sections)
BEGIN
SET IDENTITY_INSERT dbo.Sections ON;
INSERT INTO dbo.Sections (Id, ClassId, Name)
VALUES
(1, 1, 'A'), (2, 1, 'B'), (3, 2, 'A'), (4, 2, 'B'), (5, 3, 'A'), (6, 3, 'B'),
(7, 4, 'A'), (8, 4, 'B'), (9, 5, 'A'), (10, 5, 'B'), (11, 6, 'A'), (12, 6, 'B'),
(13, 7, 'A'), (14, 7, 'B'), (15, 8, 'A'), (16, 8, 'B'), (17, 9, 'A'), (18, 9, 'B'),
(19, 10, 'A'), (20, 10, 'B');
SET IDENTITY_INSERT dbo.Sections OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.Subjects)
BEGIN
SET IDENTITY_INSERT dbo.Subjects ON;
INSERT INTO dbo.Subjects (Id, Code, Name)
VALUES
(1, 'ENG', N'English'), (2, 'MATH', N'Mathematics'), (3, 'SCI', N'Science'),
(4, 'SST', N'Social Studies'), (5, 'HIN', N'Hindi'), (6, 'CS', N'Computer Science');
SET IDENTITY_INSERT dbo.Subjects OFF;
END;
GO
-- ── People ──────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM dbo.Teachers)
BEGIN
SET IDENTITY_INSERT dbo.Teachers ON;
INSERT INTO dbo.Teachers (Id, EmployeeCode, FirstName, LastName, Email, Phone, Gender, DateOfBirth, JoiningDate, Qualification)
VALUES
(1, 'TCH-001', N'Anitha', N'Rao', 'anitha.rao@nexcodingschool.example', '9876540001', 'F', '1985-03-14', '2012-06-01', N'M.Sc. Mathematics, B.Ed.'),
(2, 'TCH-002', N'Rahul', N'Verma', 'rahul.verma@nexcodingschool.example', '9876540002', 'M', '1988-07-22', '2015-06-01', N'M.Sc. Physics, B.Ed.'),
(3, 'TCH-003', N'Sarah', N'Thomas', 'sarah.thomas@nexcodingschool.example', '9876540003', 'F', '1990-11-05', '2016-06-01', N'M.A. English, B.Ed.'),
(4, 'TCH-004', N'Imran', N'Khan', 'imran.khan@nexcodingschool.example', '9876540004', 'M', '1984-01-18', '2011-06-01', N'M.A. History, B.Ed.'),
(5, 'TCH-005', N'Priya', N'Nair', 'priya.nair@nexcodingschool.example', '9876540005', 'F', '1992-09-30', '2019-06-01', N'MCA'),
(6, 'TCH-006', N'Deepak', N'Joshi', 'deepak.joshi@nexcodingschool.example', '9876540006', 'M', '1983-05-09', '2010-06-01', N'M.A. Hindi, B.Ed.'),
(7, 'TCH-007', N'Kavya', N'Menon', 'kavya.menon@nexcodingschool.example', '9876540007', 'F', '1991-04-25', '2018-06-01', N'M.Sc. Mathematics, B.Ed.'),
(8, 'TCH-008', N'Arjun', N'Mehta', 'arjun.mehta@nexcodingschool.example', '9876540008', 'M', '1989-12-12', '2017-06-01', N'M.Sc. Chemistry, B.Ed.');
SET IDENTITY_INSERT dbo.Teachers OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.Students)
BEGIN
SET IDENTITY_INSERT dbo.Students ON;
INSERT INTO dbo.Students (Id, AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate)
VALUES
(1, 'ADM-2017-0001', N'Aarav', N'Sharma', '2011-04-12', 'M', '2017-06-01'),
(2, 'ADM-2017-0002', N'Diya', N'Patel', '2011-08-23', 'F', '2017-06-01'),
(3, 'ADM-2017-0003', N'Kabir', N'Singh', '2011-01-30', 'M', '2017-06-01'),
(4, 'ADM-2017-0004', N'Meera', N'Iyer', '2011-11-05', 'F', '2017-06-01'),
(5, 'ADM-2017-0005', N'Rohan', N'Das', '2011-06-17', 'M', '2017-06-01'),
(6, 'ADM-2017-0006', N'Sana', N'Sheikh', '2011-03-09', 'F', '2017-06-01'),
(7, 'ADM-2021-0001', N'Advika', N'Sharma', '2015-07-21', 'F', '2021-06-01'),
(8, 'ADM-2021-0002', N'Ananya', N'Reddy', '2015-02-14', 'F', '2021-06-01'),
(9, 'ADM-2021-0003', N'Vihaan', N'Kapoor', '2015-09-02', 'M', '2021-06-01'),
(10, 'ADM-2021-0004', N'Zara', N'Ali', '2015-12-11', 'F', '2021-06-01'),
(11, 'ADM-2021-0005', N'Arnav', N'Mishra', '2015-05-28', 'M', '2021-06-01'),
(12, 'ADM-2021-0006', N'Kiara', N'Fernandes', '2015-10-19', 'F', '2021-06-01');
SET IDENTITY_INSERT dbo.Students OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.Guardians)
BEGIN
SET IDENTITY_INSERT dbo.Guardians ON;
INSERT INTO dbo.Guardians (Id, FirstName, LastName, Phone, Email, Occupation)
VALUES
(1, N'Rajesh', N'Sharma', '9876540101', 'rajesh.sharma@nexcodingschool.example', N'Engineer'),
(2, N'Neha', N'Sharma', '9876540102', NULL, N'Doctor'),
(3, N'Suresh', N'Patel', '9876540103', NULL, N'Business owner'),
(4, N'Kavita', N'Patel', '9876540104', NULL, NULL),
(5, N'Harpreet', N'Singh', '9876540105', NULL, N'Pilot'),
(6, N'Simran', N'Singh', '9876540106', NULL, N'Teacher'),
(7, N'Venkat', N'Iyer', '9876540107', NULL, N'Accountant'),
(8, N'Lakshmi', N'Iyer', '9876540108', NULL, NULL),
(9, N'Amit', N'Das', '9876540109', NULL, N'Shopkeeper'),
(10, N'Rina', N'Das', '9876540110', NULL, N'Nurse'),
(11, N'Imtiaz', N'Sheikh', '9876540111', NULL, N'Driver'),
(12, N'Ayesha', N'Sheikh', '9876540112', NULL, NULL),
(13, N'Ravi', N'Reddy', '9876540113', NULL, N'Farmer'),
(14, N'Padma', N'Reddy', '9876540114', NULL, NULL),
(15, N'Karan', N'Kapoor', '9876540115', NULL, N'Designer'),
(16, N'Pooja', N'Kapoor', '9876540116', NULL, N'Lawyer'),
(17, N'Faisal', N'Ali', '9876540117', NULL, N'Engineer'),
(18, N'Nadia', N'Ali', '9876540118', NULL, NULL),
(19, N'Sanjay', N'Mishra', '9876540119', NULL, N'Banker'),
(20, N'Anjali', N'Mishra', '9876540120', NULL, N'Teacher'),
(21, N'Joseph', N'Fernandes', '9876540121', NULL, N'Chef'),
(22, N'Maria', N'Fernandes', '9876540122', NULL, NULL);
SET IDENTITY_INSERT dbo.Guardians OFF;
END;
-- Rajesh (1) and Neha (2) are the guardians of both Aarav (1) and Advika (7).
IF NOT EXISTS (SELECT 1 FROM dbo.StudentGuardians)
BEGIN
INSERT INTO dbo.StudentGuardians (StudentId, GuardianId, Relationship, IsPrimaryContact)
VALUES
(1, 1, 'Father', 1), (1, 2, 'Mother', 0),
(2, 3, 'Father', 1), (2, 4, 'Mother', 0),
(3, 5, 'Father', 1), (3, 6, 'Mother', 0),
(4, 7, 'Father', 1), (4, 8, 'Mother', 0),
(5, 9, 'Father', 1), (5, 10, 'Mother', 0),
(6, 11, 'Father', 1), (6, 12, 'Mother', 0),
(7, 1, 'Father', 1), (7, 2, 'Mother', 0),
(8, 13, 'Father', 1), (8, 14, 'Mother', 0),
(9, 15, 'Father', 1), (9, 16, 'Mother', 0),
(10, 17, 'Father', 1), (10, 18, 'Mother', 0),
(11, 19, 'Father', 1), (11, 20, 'Mother', 0),
(12, 21, 'Father', 1), (12, 22, 'Mother', 0);
END;
GO
-- ── Enrollment and allocation ───────────────────────────────────────────────
-- 2025-26 (Ids 1-12): one class lower. 2026-27 (Ids 13-24): the current year.
-- Section Ids: 9-A = 17, 9-B = 18, 10-A = 19, 10-B = 20, 5-A = 9, 5-B = 10, 6-A = 11, 6-B = 12.
IF NOT EXISTS (SELECT 1 FROM dbo.Enrollments)
BEGIN
SET IDENTITY_INSERT dbo.Enrollments ON;
INSERT INTO dbo.Enrollments (Id, StudentId, AcademicYearId, SectionId, RollNumber, EnrolledOn)
VALUES
(1, 1, 1, 17, 1, '2025-06-01'), (2, 2, 1, 17, 2, '2025-06-01'),
(3, 3, 1, 17, 3, '2025-06-01'), (4, 4, 1, 17, 4, '2025-06-01'),
(5, 5, 1, 18, 1, '2025-06-01'), (6, 6, 1, 18, 2, '2025-06-01'),
(7, 7, 1, 9, 1, '2025-06-01'), (8, 8, 1, 9, 2, '2025-06-01'),
(9, 9, 1, 9, 3, '2025-06-01'), (10, 10, 1, 9, 4, '2025-06-01'),
(11, 11, 1, 10, 1, '2025-06-01'), (12, 12, 1, 10, 2, '2025-06-01'),
(13, 1, 2, 19, 1, '2026-06-01'), (14, 2, 2, 19, 2, '2026-06-01'),
(15, 3, 2, 19, 3, '2026-06-01'), (16, 4, 2, 19, 4, '2026-06-01'),
(17, 5, 2, 20, 1, '2026-06-01'), (18, 6, 2, 20, 2, '2026-06-01'),
(19, 7, 2, 11, 1, '2026-06-01'), (20, 8, 2, 11, 2, '2026-06-01'),
(21, 9, 2, 11, 3, '2026-06-01'), (22, 10, 2, 11, 4, '2026-06-01'),
(23, 11, 2, 12, 1, '2026-06-01'), (24, 12, 2, 12, 2, '2026-06-01');
SET IDENTITY_INSERT dbo.Enrollments OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.ClassTeachers)
BEGIN
INSERT INTO dbo.ClassTeachers (AcademicYearId, SectionId, TeacherId)
VALUES
(2, 19, 1), -- 10-A: Anitha Rao
(2, 20, 2), -- 10-B: Rahul Verma
(2, 11, 7), -- 6-A: Kavya Menon
(2, 12, 8); -- 6-B: Arjun Mehta
END;
-- Subjects: 1 ENG, 2 MATH, 3 SCI, 4 SST, 5 HIN, 6 CS.
IF NOT EXISTS (SELECT 1 FROM dbo.SubjectAllocations)
BEGIN
INSERT INTO dbo.SubjectAllocations (AcademicYearId, SectionId, SubjectId, TeacherId)
VALUES
(2, 19, 1, 3), (2, 19, 2, 1), (2, 19, 3, 2), (2, 19, 4, 4), (2, 19, 5, 6), (2, 19, 6, 5),
(2, 20, 1, 3), (2, 20, 2, 1), (2, 20, 3, 2), (2, 20, 4, 4), (2, 20, 5, 6), (2, 20, 6, 5),
(2, 11, 1, 3), (2, 11, 2, 7), (2, 11, 3, 8), (2, 11, 4, 4), (2, 11, 5, 6), (2, 11, 6, 5),
(2, 12, 1, 3), (2, 12, 2, 7), (2, 12, 3, 8), (2, 12, 4, 4), (2, 12, 5, 6), (2, 12, 6, 5);
END;
GO
-- ── Attendance, exams and marks ─────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM dbo.AttendanceRecords)
BEGIN
INSERT INTO dbo.AttendanceRecords (EnrollmentId, AttendanceDate, Status, Remarks)
VALUES
(13, '2026-07-01', 'Present', NULL), (14, '2026-07-01', 'Present', NULL),
(15, '2026-07-01', 'Absent', N'Fever'), (16, '2026-07-01', 'Present', NULL),
(17, '2026-07-01', 'Present', NULL), (18, '2026-07-01', 'Present', NULL),
(19, '2026-07-01', 'Present', NULL), (20, '2026-07-01', 'Present', NULL),
(21, '2026-07-01', 'Present', NULL), (22, '2026-07-01', 'Present', NULL),
(23, '2026-07-01', 'Present', NULL), (24, '2026-07-01', 'Present', NULL),
(13, '2026-07-02', 'Present', NULL), (14, '2026-07-02', 'Present', NULL),
(15, '2026-07-02', 'Present', NULL), (16, '2026-07-02', 'Present', NULL),
(17, '2026-07-02', 'Present', NULL), (18, '2026-07-02', 'Present', NULL),
(19, '2026-07-02', 'Present', NULL), (20, '2026-07-02', 'Present', NULL),
(21, '2026-07-02', 'Present', NULL), (22, '2026-07-02', 'Late', N'Bus delay'),
(23, '2026-07-02', 'Present', NULL), (24, '2026-07-02', 'Present', NULL),
(13, '2026-07-03', 'Present', NULL), (14, '2026-07-03', 'Present', NULL),
(15, '2026-07-03', 'Present', NULL), (16, '2026-07-03', 'Present', NULL),
(17, '2026-07-03', 'Leave', N'Family function'), (18, '2026-07-03', 'Present', NULL),
(19, '2026-07-03', 'Present', NULL), (20, '2026-07-03', 'Present', NULL),
(21, '2026-07-03', 'Present', NULL), (22, '2026-07-03', 'Present', NULL),
(23, '2026-07-03', 'Present', NULL), (24, '2026-07-03', 'Present', NULL);
END;
IF NOT EXISTS (SELECT 1 FROM dbo.Exams)
BEGIN
SET IDENTITY_INSERT dbo.Exams ON;
INSERT INTO dbo.Exams (Id, AcademicYearId, Name, ExamType, StartDate, EndDate)
VALUES
(1, 2, N'Unit Test 1', 'UnitTest', '2026-07-20', '2026-07-24'),
(2, 2, N'Half Yearly', 'Term', '2026-09-21', '2026-09-30');
SET IDENTITY_INSERT dbo.Exams OFF;
END;
-- Papers for Class 6 and Class 10. Ids: exam 1 = 1-6 (Class 6) and 7-12 (Class 10);
-- exam 2 = 13-18 (Class 6) and 19-24 (Class 10). Every paper is out of 100.
IF NOT EXISTS (SELECT 1 FROM dbo.ExamSubjects)
BEGIN
SET IDENTITY_INSERT dbo.ExamSubjects ON;
INSERT INTO dbo.ExamSubjects (Id, ExamId, ClassId, SubjectId, ExamDate, MaxMarks, PassMarks)
VALUES
(1, 1, 6, 1, '2026-07-20', 100, 35), (2, 1, 6, 2, '2026-07-21', 100, 35),
(3, 1, 6, 3, '2026-07-22', 100, 35), (4, 1, 6, 4, '2026-07-23', 100, 35),
(5, 1, 6, 5, '2026-07-24', 100, 35), (6, 1, 6, 6, '2026-07-24', 100, 35),
(7, 1, 10, 1, '2026-07-20', 100, 35), (8, 1, 10, 2, '2026-07-21', 100, 35),
(9, 1, 10, 3, '2026-07-22', 100, 35), (10, 1, 10, 4, '2026-07-23', 100, 35),
(11, 1, 10, 5, '2026-07-24', 100, 35), (12, 1, 10, 6, '2026-07-24', 100, 35),
(13, 2, 6, 1, '2026-09-21', 100, 35), (14, 2, 6, 2, '2026-09-22', 100, 35),
(15, 2, 6, 3, '2026-09-23', 100, 35), (16, 2, 6, 4, '2026-09-24', 100, 35),
(17, 2, 6, 5, '2026-09-25', 100, 35), (18, 2, 6, 6, '2026-09-26', 100, 35),
(19, 2, 10, 1, '2026-09-21', 100, 35), (20, 2, 10, 2, '2026-09-22', 100, 35),
(21, 2, 10, 3, '2026-09-23', 100, 35), (22, 2, 10, 4, '2026-09-24', 100, 35),
(23, 2, 10, 5, '2026-09-25', 100, 35), (24, 2, 10, 6, '2026-09-26', 100, 35);
SET IDENTITY_INSERT dbo.ExamSubjects OFF;
END;
-- Unit Test 1 marks in Mathematics and Science. Meera (enrollment 16) was absent for Science.
IF NOT EXISTS (SELECT 1 FROM dbo.Marks)
BEGIN
INSERT INTO dbo.Marks (ExamSubjectId, EnrollmentId, MarksObtained, IsAbsent)
VALUES
(8, 13, 92, 0), (9, 13, 88, 0),
(8, 14, 85, 0), (9, 14, 91, 0),
(8, 15, 67, 0), (9, 15, 72, 0),
(8, 16, 95, 0), (9, 16, NULL, 1),
(8, 17, 58, 0), (9, 17, 61, 0),
(8, 18, 78, 0), (9, 18, 83, 0),
(2, 19, 89, 0), (3, 19, 94, 0),
(2, 20, 76, 0), (3, 20, 69, 0),
(2, 21, 64, 0), (3, 21, 70, 0),
(2, 22, 81, 0), (3, 22, 77, 0),
(2, 23, 33, 0), (3, 23, 48, 0),
(2, 24, 90, 0), (3, 24, 86, 0);
END;
GO
-- ── Fees ────────────────────────────────────────────────────────────────────
-- 2026-27 fee structure. Ids for class c: Tuition = 3c-2, Library = 3c-1, Exam = 3c.
IF NOT EXISTS (SELECT 1 FROM dbo.FeeStructures)
BEGIN
SET IDENTITY_INSERT dbo.FeeStructures ON;
INSERT INTO dbo.FeeStructures (Id, AcademicYearId, ClassId, FeeType, Amount)
VALUES
(1, 2, 1, 'Tuition', 30000), (2, 2, 1, 'Library', 1500), (3, 2, 1, 'Exam', 2000),
(4, 2, 2, 'Tuition', 30000), (5, 2, 2, 'Library', 1500), (6, 2, 2, 'Exam', 2000),
(7, 2, 3, 'Tuition', 30000), (8, 2, 3, 'Library', 1500), (9, 2, 3, 'Exam', 2000),
(10, 2, 4, 'Tuition', 30000), (11, 2, 4, 'Library', 1500), (12, 2, 4, 'Exam', 2000),
(13, 2, 5, 'Tuition', 30000), (14, 2, 5, 'Library', 1500), (15, 2, 5, 'Exam', 2000),
(16, 2, 6, 'Tuition', 36000), (17, 2, 6, 'Library', 1500), (18, 2, 6, 'Exam', 2000),
(19, 2, 7, 'Tuition', 36000), (20, 2, 7, 'Library', 1500), (21, 2, 7, 'Exam', 2000),
(22, 2, 8, 'Tuition', 36000), (23, 2, 8, 'Library', 1500), (24, 2, 8, 'Exam', 2000),
(25, 2, 9, 'Tuition', 42000), (26, 2, 9, 'Library', 1500), (27, 2, 9, 'Exam', 2000),
(28, 2, 10, 'Tuition', 42000), (29, 2, 10, 'Library', 1500), (30, 2, 10, 'Exam', 2000);
SET IDENTITY_INSERT dbo.FeeStructures OFF;
END;
-- One invoice per current enrollment. Class 10 total 45500, Class 6 total 39500.
IF NOT EXISTS (SELECT 1 FROM dbo.FeeInvoices)
BEGIN
SET IDENTITY_INSERT dbo.FeeInvoices ON;
INSERT INTO dbo.FeeInvoices (Id, InvoiceNumber, EnrollmentId, InvoiceDate, DueDate, TotalAmount, Status)
VALUES
(1, 'INV-2026-000001', 13, '2026-06-01', '2026-06-30', 45500, 'Paid'),
(2, 'INV-2026-000002', 14, '2026-06-01', '2026-06-30', 45500, 'PartiallyPaid'),
(3, 'INV-2026-000003', 15, '2026-06-01', '2026-06-30', 45500, 'Unpaid'),
(4, 'INV-2026-000004', 16, '2026-06-01', '2026-06-30', 45500, 'Unpaid'),
(5, 'INV-2026-000005', 17, '2026-06-01', '2026-06-30', 45500, 'Unpaid'),
(6, 'INV-2026-000006', 18, '2026-06-01', '2026-06-30', 45500, 'Unpaid'),
(7, 'INV-2026-000007', 19, '2026-06-01', '2026-06-30', 39500, 'Paid'),
(8, 'INV-2026-000008', 20, '2026-06-01', '2026-06-30', 39500, 'Unpaid'),
(9, 'INV-2026-000009', 21, '2026-06-01', '2026-06-30', 39500, 'Unpaid'),
(10, 'INV-2026-000010', 22, '2026-06-01', '2026-06-30', 39500, 'Unpaid'),
(11, 'INV-2026-000011', 23, '2026-06-01', '2026-06-30', 39500, 'Unpaid'),
(12, 'INV-2026-000012', 24, '2026-06-01', '2026-06-30', 39500, 'Unpaid');
SET IDENTITY_INSERT dbo.FeeInvoices OFF;
END;
-- Three lines per invoice: Class 10 uses structures 28-30, Class 6 uses 16-18.
IF NOT EXISTS (SELECT 1 FROM dbo.FeeInvoiceItems)
BEGIN
INSERT INTO dbo.FeeInvoiceItems (FeeInvoiceId, FeeStructureId, Amount)
VALUES
(1, 28, 42000), (1, 29, 1500), (1, 30, 2000),
(2, 28, 42000), (2, 29, 1500), (2, 30, 2000),
(3, 28, 42000), (3, 29, 1500), (3, 30, 2000),
(4, 28, 42000), (4, 29, 1500), (4, 30, 2000),
(5, 28, 42000), (5, 29, 1500), (5, 30, 2000),
(6, 28, 42000), (6, 29, 1500), (6, 30, 2000),
(7, 16, 36000), (7, 17, 1500), (7, 18, 2000),
(8, 16, 36000), (8, 17, 1500), (8, 18, 2000),
(9, 16, 36000), (9, 17, 1500), (9, 18, 2000),
(10, 16, 36000), (10, 17, 1500), (10, 18, 2000),
(11, 16, 36000), (11, 17, 1500), (11, 18, 2000),
(12, 16, 36000), (12, 17, 1500), (12, 18, 2000);
END;
-- PaidOn is stored in UTC.
IF NOT EXISTS (SELECT 1 FROM dbo.FeePayments)
BEGIN
INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode, TransactionReference)
VALUES
('RCP-2026-000001', 1, 45500, '2026-06-10 05:00:00', 'Online', 'TXN-81726354'),
('RCP-2026-000002', 2, 20000, '2026-06-15 06:30:00', 'Cash', NULL),
('RCP-2026-000003', 7, 39500, '2026-06-10 05:05:00', 'Online', 'TXN-81726377');
END;
GO
-- ── Users and roles ─────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM dbo.Roles)
BEGIN
SET IDENTITY_INSERT dbo.Roles ON;
INSERT INTO dbo.Roles (Id, Name)
VALUES (1, 'Admin'), (2, 'Principal'), (3, 'Teacher'), (4, 'Accountant'), (5, 'Parent');
SET IDENTITY_INSERT dbo.Roles OFF;
END;
-- Real password hashes are created in Module 12. Until then nobody can log in.
IF NOT EXISTS (SELECT 1 FROM dbo.Users)
BEGIN
SET IDENTITY_INSERT dbo.Users ON;
INSERT INTO dbo.Users (Id, UserName, Email, PasswordHash, TeacherId, GuardianId)
VALUES
(1, 'admin', 'admin@nexcodingschool.example', 'NOT-SET', NULL, NULL),
(2, 'principal', 'principal@nexcodingschool.example', 'NOT-SET', NULL, NULL),
(3, 'anitha.rao', 'anitha.rao@nexcodingschool.example', 'NOT-SET', 1, NULL),
(4, 'accounts', 'accounts@nexcodingschool.example', 'NOT-SET', NULL, NULL),
(5, 'rajesh.sharma', 'rajesh.sharma@nexcodingschool.example', 'NOT-SET', NULL, 1);
SET IDENTITY_INSERT dbo.Users OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.UserRoles)
BEGIN
INSERT INTO dbo.UserRoles (UserId, RoleId)
VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
END;
GO
-- Check: how many rows each table has now.
SELECT 'Students' AS TableName, COUNT(*) AS RowsNow FROM dbo.Students
UNION ALL SELECT 'Guardians', COUNT(*) FROM dbo.Guardians
UNION ALL SELECT 'Enrollments', COUNT(*) FROM dbo.Enrollments
UNION ALL SELECT 'AttendanceRecords', COUNT(*) FROM dbo.AttendanceRecords
UNION ALL SELECT 'Marks', COUNT(*) FROM dbo.Marks
UNION ALL SELECT 'FeeInvoices', COUNT(*) FROM dbo.FeeInvoices
UNION ALL SELECT 'FeePayments', COUNT(*) FROM dbo.FeePayments;
GO
The last query shows the row counts:
| TableName | RowsNow |
|---|---|
| Students | 12 |
| Guardians | 22 |
| Enrollments | 24 |
| AttendanceRecords | 36 |
| Marks | 24 |
| FeeInvoices | 12 |
| FeePayments | 3 |
Twelve students have 24 enrollments: one for last year and one for this year. Run the script again: no errors, and the same counts.
Example: GENERATE_SERIES
To create 10,000 students, you do not type 10,000 lines. You generate them from a list
of numbers. GENERATE_SERIES(1, 10000) is a SQL Server 2022 function that returns
the numbers 1 to 10,000, one per row:
SELECT value
FROM GENERATE_SERIES(1, 5);
value
-----
1
2
3
4
5
The generator turns each number into a student, and number 1 becomes ADM-2026-00001.
Names come from short lists with CHOOSE, which picks the nth item from a list.
Each number also decides the student's class, section, and roll number:
| Student numbers | Class | Section | Roll numbers |
|---|---|---|---|
| 1 to 50 | Class 1 | C | 1 to 50 |
| 51 to 100 | Class 1 | D | 1 to 50 |
| 951 to 1,000 | Class 1 | V | 1 to 50 |
| 1,001 to 1,050 | Class 2 | C | 1 to 50 |
So every class gets 1,000 students in 20 sections of 50. Sections A and B keep only the fixed students, which is why 10-A still has exactly four.
This set-based approach inserts all 10,000 rows in one statement. A loop that inserts one row at a time would take far longer. You will learn why in Module 5.
Step 2: the 10,000-student generator
Save this as database\015-2-bulk-data.sql and run it. It uses several query features
you learn in Module 4, such as JOIN and CASE. For now, read the six numbered
comments; each describes one step.
The whole script runs in one transaction with SET XACT_ABORT ON: if any step
fails, everything is undone, and you never get half a dataset. You will study
transactions in Lesson 24.
Show database/015-2-bulk-data.sql
-- Lesson 15 (part 2): a large dataset for performance work in Module 5.
-- Adds 10,000 students in the current year, 60 school days of attendance (600,000 rows)
-- and marks for two exams (120,000 rows). The fixed sample data is not touched.
-- Needs SQL Server 2022 or later (GENERATE_SERIES).
-- Safe to run more than once: it does nothing if the bulk students already exist.
USE SchoolDb;
GO
SET NOCOUNT ON;
SET XACT_ABORT ON; -- any error cancels the whole transaction: all or nothing
IF EXISTS (SELECT 1 FROM dbo.Students WHERE AdmissionNumber = 'ADM-2026-00001')
BEGIN
PRINT 'Bulk data already exists. Nothing to do.';
RETURN;
END;
DECLARE @CurrentYearId INT = (SELECT Id FROM dbo.AcademicYears WHERE IsCurrent = 1);
BEGIN TRANSACTION;
-- 1. Sections C to V (20 more per class), 50 students each.
INSERT INTO dbo.Sections (ClassId, Name, Capacity)
SELECT c.Id, CHAR(ASCII('B') + s.value), 50
FROM dbo.Classes AS c
CROSS JOIN GENERATE_SERIES(1, 20) AS s;
-- 2. 10,000 students: numbers 1-1000 are in Class 1, 1001-2000 in Class 2, and so on.
INSERT INTO dbo.Students (AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate)
SELECT
CONCAT('ADM-2026-', RIGHT(CONCAT('0000', n.value), 5)),
CASE
WHEN n.value % 2 = 1 THEN CHOOSE((n.value / 2) % 10 + 1,
N'Aditya', N'Rahul', N'Vikram', N'Yash', N'Dev', N'Ishaan', N'Kunal', N'Nikhil', N'Om', N'Sameer')
ELSE CHOOSE((n.value / 2) % 10 + 1,
N'Aisha', N'Bhavya', N'Divya', N'Esha', N'Gauri', N'Isha', N'Jiya', N'Kavya', N'Navya', N'Riya')
END,
CHOOSE(n.value % 15 + 1,
N'Sharma', N'Patel', N'Singh', N'Iyer', N'Das', N'Reddy', N'Kapoor', N'Ali',
N'Mishra', N'Fernandes', N'Nair', N'Joshi', N'Menon', N'Mehta', N'Verma'),
DATEADD(DAY, n.value % 365, DATEFROMPARTS(2026 - 5 - ((n.value - 1) / 1000 + 1), 1, 1)),
CASE WHEN n.value % 2 = 1 THEN 'M' ELSE 'F' END,
'2026-06-01'
FROM GENERATE_SERIES(1, 10000) AS n;
-- 3. Enroll them: 50 per section, roll numbers 1-50.
INSERT INTO dbo.Enrollments (StudentId, AcademicYearId, SectionId, RollNumber, EnrolledOn)
SELECT st.Id, @CurrentYearId, sec.Id, (st.n - 1) % 50 + 1, '2026-06-01'
FROM (
SELECT Id, CAST(RIGHT(AdmissionNumber, 5) AS INT) AS n
FROM dbo.Students
WHERE AdmissionNumber LIKE 'ADM-2026-%'
) AS st
JOIN dbo.Sections AS sec
ON sec.ClassId = (st.n - 1) / 1000 + 1
AND sec.Name = CHAR(ASCII('B') + (st.n - 1) % 1000 / 50 + 1);
-- 4. Exam papers for the classes that do not have them yet (copied from Class 10).
INSERT INTO dbo.ExamSubjects (ExamId, ClassId, SubjectId, ExamDate, MaxMarks, PassMarks)
SELECT es.ExamId, c.Id, es.SubjectId, es.ExamDate, es.MaxMarks, es.PassMarks
FROM dbo.ExamSubjects AS es
CROSS JOIN dbo.Classes AS c
WHERE es.ClassId = 10
AND NOT EXISTS (SELECT 1 FROM dbo.ExamSubjects AS x WHERE x.ClassId = c.Id);
-- 5. Attendance: 60 school days (Monday to Saturday) from 2026-06-01.
WITH SchoolDays AS (
SELECT d.value AS DayNumber, DATEADD(DAY, d.value, CAST('2026-06-01' AS DATE)) AS AttendanceDate
FROM GENERATE_SERIES(0, 69) AS d
WHERE DATEDIFF(DAY, '19000107', DATEADD(DAY, d.value, CAST('2026-06-01' AS DATE))) % 7 <> 0 -- not Sunday
),
BulkEnrollments AS (
SELECT e.Id
FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE s.AdmissionNumber LIKE 'ADM-2026-%'
)
INSERT INTO dbo.AttendanceRecords (EnrollmentId, AttendanceDate, Status)
SELECT
be.Id,
sd.AttendanceDate,
CASE
WHEN (be.Id * 7 + sd.DayNumber * 13) % 100 < 88 THEN 'Present'
WHEN (be.Id * 7 + sd.DayNumber * 13) % 100 < 94 THEN 'Absent'
WHEN (be.Id * 7 + sd.DayNumber * 13) % 100 < 97 THEN 'Late'
ELSE 'Leave'
END
FROM BulkEnrollments AS be
CROSS JOIN SchoolDays AS sd;
-- 6. Marks for both exams, every subject. About 1 in 50 papers is marked absent.
INSERT INTO dbo.Marks (ExamSubjectId, EnrollmentId, MarksObtained, IsAbsent)
SELECT
es.Id,
e.Id,
CASE WHEN (e.Id + es.Id * 3) % 50 = 0 THEN NULL ELSE 20 + (e.Id * 17 + es.Id * 29) % 81 END,
CASE WHEN (e.Id + es.Id * 3) % 50 = 0 THEN 1 ELSE 0 END
FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId
JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
JOIN dbo.ExamSubjects AS es ON es.ClassId = sec.ClassId
WHERE s.AdmissionNumber LIKE 'ADM-2026-%';
COMMIT TRANSACTION;
-- Check: row counts after the bulk load.
SELECT 'Students' AS TableName, COUNT(*) AS RowsNow FROM dbo.Students
UNION ALL SELECT 'Sections', COUNT(*) FROM dbo.Sections
UNION ALL SELECT 'Enrollments', COUNT(*) FROM dbo.Enrollments
UNION ALL SELECT 'AttendanceRecords', COUNT(*) FROM dbo.AttendanceRecords
UNION ALL SELECT 'Marks', COUNT(*) FROM dbo.Marks;
GO
Example: check the counts
On a laptop, the generator takes about 10 seconds. The final counts:
| TableName | RowsNow |
|---|---|
| Students | 10012 |
| Sections | 220 |
| Enrollments | 10024 |
| AttendanceRecords | 600036 |
| Marks | 120024 |
That is 12 fixed students plus 10,000 generated, 20 new sections per class, and 600,000 attendance rows plus the original 36. Run the script again and it prints "Bulk data already exists. Nothing to do."
The fixed data is untouched: 10-A still has exactly four students, and every example from earlier lessons gives the same answer.
Example: a look at the generated students
Run this to see the first three generated students:
SELECT TOP (3) AdmissionNumber, FirstName, LastName, DateOfBirth, Gender
FROM dbo.Students
WHERE AdmissionNumber LIKE 'ADM-2026-%'
ORDER BY AdmissionNumber;
| AdmissionNumber | FirstName | LastName | DateOfBirth | Gender |
|---|---|---|---|---|
| ADM-2026-00001 | Aditya | Patel | 2020-01-02 | M |
| ADM-2026-00002 | Bhavya | Singh | 2020-01-03 | F |
| ADM-2026-00003 | Rahul | Iyer | 2020-01-04 | M |
You get exactly these rows every time you rebuild, because every value is calculated
from the student's number. TOP (3) returns only the first three rows; LIKE with %
matches any text that starts with ADM-2026-. Both are covered in Lesson 16.
To rebuild everything from scratch at any time, drop SchoolDb in SSMS and run
database\run-all.cmd. It runs every script, from 004 to 015-2, in order.
Rebuild whenever your practice queries in Module 4 change data you did not mean to change. In about 15 seconds you are back to the exact state every lesson expects. That is the real value of re-runnable, repeatable scripts: mistakes cost seconds, not days.
Common mistakes
- Inserting without a column list. The insert breaks when the table changes.
Fix: always write
INSERT INTO dbo.Table (Col1, Col2). - Forgetting
SET IDENTITY_INSERT ... OFF. Only one table at a time can have it on, so the next table's insert fails. Fix: turn it off right after the insert. - Inserting children before parents. Foreign keys refuse enrollments for students that do not exist yet. Fix: insert in the same order you created the tables.
- Loading test data with a loop. 600,000 single-row inserts take minutes. Fix: generate rows with one set-based insert.
- Using random values in test data. Every run gives different answers. Fix: calculate values from the row number, so the data is the same every time.
In real projects
On every project, I keep two kinds of data scripts. Seed data, like roles, classes, and subjects, is part of the product and runs in every environment, including production. Test data, like our 10,000 students, runs only on developer and test machines, never in production.
The other rule: never copy real production data to test performance. It contains real children's names and their parents' phone numbers. A generator like this one gives you the same volume with none of the risk, and the same numbers every time you rebuild.
Best practices
- Always list the columns in an
INSERT. - Give seed data fixed Ids with
IDENTITY_INSERT, and switch it off immediately. - Guard every seed block so the script can run twice.
- Generate test data set-based and repeatable, never random.
- Keep test data out of production; keep real data out of test.
Recap
INSERT INTO table (columns) VALUES (...), (...)adds one or many rows.- Left-out columns get their default, the next identity value, or NULL.
SET IDENTITY_INSERT ... ONallows fixed Ids for seed data.IF NOT EXISTS (SELECT 1 FROM ...)makes seed blocks re-runnable.GENERATE_SERIEScreates numbers that become thousands of rows in one statement.- The SMS now holds 10,012 students and over 700,000 rows.
run-all.cmdrebuilds the whole database from scratch in seconds, so you can always start clean.
Homework
Library module task: Add three books, four copies, and two issues to the library tables. Use fixed Ids. Make one issue returned on time and one still out, so the "one open issue per copy" rule from Lesson 12 has something to protect.
A correct result looks like: BookIssues has two rows, one with ReturnedOn
empty, and the script runs twice without errors.
Show the solution
-- Library homework, Lesson 15: sample books, copies and issues.
-- Needs the library tables from the Lesson 9 to 14 homework.
USE SchoolDb;
GO
IF NOT EXISTS (SELECT 1 FROM dbo.Books)
BEGIN
SET IDENTITY_INSERT dbo.Books ON;
INSERT INTO dbo.Books (Id, Isbn, Title, Author, Publisher, PublishedYear, Category)
VALUES
(1, '9788173711466', N'Wings of Fire', N'A. P. J. Abdul Kalam', N'Universities Press', 1999, N'Biography'),
(2, '9780141325293', N'The Jungle Book', N'Rudyard Kipling', N'Puffin', 1894, N'Fiction'),
(3, '9780143039655', N'Malgudi Days', N'R. K. Narayan', N'Penguin', 1943, N'Fiction');
SET IDENTITY_INSERT dbo.Books OFF;
END;
IF NOT EXISTS (SELECT 1 FROM dbo.BookCopies)
BEGIN
SET IDENTITY_INSERT dbo.BookCopies ON;
INSERT INTO dbo.BookCopies (Id, BookId, CopyNumber, Status)
VALUES
(1, 1, 1, 'Available'),
(2, 1, 2, 'Available'),
(3, 2, 1, 'Issued'),
(4, 3, 1, 'Available');
SET IDENTITY_INSERT dbo.BookCopies OFF;
END;
-- Aarav (student 1) returned his copy on time. Advika (student 7) still has hers.
IF NOT EXISTS (SELECT 1 FROM dbo.BookIssues)
BEGIN
INSERT INTO dbo.BookIssues (BookCopyId, StudentId, IssuedOn, DueOn, ReturnedOn)
VALUES
(2, 1, '2026-07-01', '2026-07-15', '2026-07-14'),
(3, 7, '2026-07-06', '2026-07-20', NULL);
END;
GO
Try issuing copy 3 to another student. The insert fails with a duplicate key error
from UQ_BookIssues_OpenCopy, because Advika still has it.
Quiz
An INSERT leaves out Sections.Capacity, which has DEFAULT (40). What is stored?
Show answer
Answer: C. 40 A default fills any column the insert does not mention.
What does SET IDENTITY_INSERT dbo.Students ON allow?
Show answer
Answer: B. Typing your own values into the Id column It lets seed data use fixed Ids; switch it off right after.
Why does every block in the seed script start with IF NOT EXISTS (SELECT 1 FROM ...)?
Show answer
Answer: B. So running the script twice does not insert duplicates Each table is filled only while it is empty, so the script is re-runnable.
What does GENERATE_SERIES(1, 10000) return?
Show answer
Answer: B. The numbers 1 to 10,000, one per row It is a list of numbers that the generator turns into rows.
Why does the generator use SET XACT_ABORT ON and one transaction?
Show answer
Answer: B. So any failure undoes everything, and you never get half a dataset All or nothing: a failed step rolls back the whole load.
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 syntax of INSERT, and why should you always list the columns? coding
INSERT INTO dbo.Table (Col1, Col2) VALUES (v1, v2), (v3, v4); adds one or more rows.
Listing the columns keeps the insert working when columns are added later, and stops
values going into the wrong columns if the column order changes.
F What happens to a column you leave out of an INSERT? concept
SQL Server uses the column's DEFAULT if it has one, the next value if it is an IDENTITY column, or NULL if the column allows it. If none of these apply, the insert fails with "Cannot insert the value NULL".
F What does SET IDENTITY_INSERT do? concept
It lets you insert your own values into an IDENTITY column for one table at a time. It is used for seed data that needs fixed Ids, or when copying data between databases. You must list the Id column, and you should turn it off right after the insert.
Mid (3-5 yrs)
M Why is a set-based insert faster than inserting rows one at a time in a loop? concept
A set-based statement inserts all rows in one operation, so SQL Server plans it once, writes the log efficiently, and avoids a round trip per row. A loop runs a separate statement, and often a separate commit, for every row. The SMS generator inserts 600,000 attendance rows in seconds this way.
M What is GENERATE_SERIES, and how did people generate numbers before SQL Server 2022? concept
GENERATE_SERIES(start, stop) returns one row per number in a range, which you can turn
into generated rows. Before SQL Server 2022, developers used a permanent numbers table,
a recursive CTE, or cross joins of system views to produce the same list of numbers.
M Why does the bulk data script use one transaction with SET XACT_ABORT ON? concept
It makes the load all or nothing. With XACT_ABORT ON, any error rolls back the whole
transaction, so you never end up with students but no enrollments. Combined with the
"already exists" check at the top, the script can simply be run again after a failure.
Senior (6-9 yrs)
S Why test with 10,000 students when the sample school has only 12? scenario
With 12 rows every query is instant, so slow designs stay hidden until production. A realistic volume shows missing indexes, queries that return too many rows, and slow reports during development, when they are cheap to fix.
How to answer: Give a concrete SMS example: a class report that scans every attendance row is invisible at 36 rows and obvious at 600,000. Describe how you size test data from the real school's numbers, and note that volume tests run on dedicated test databases, not on developers' shared servers.
S What is the difference between seed data and test data, and where does each run? design
Seed data is part of the product, such as roles, classes, and subjects, and runs in every environment, including production. Test data, such as generated students, runs only in development and test. Mixing them puts fake students into a real school or leaves production without required lookup rows.
How to answer: Define both, give SMS examples of each, and describe how deployment keeps them apart: separate scripts or folders, and pipeline steps that run test data only in non-production environments. Mention keeping seed data re-runnable so redeploys are safe.
Architect / Lead (10+ yrs)
A How do you create realistic test data at scale without using production data? design
Generate it from rules: numbers become names, dates, and statuses through repeatable formulas, with realistic distributions such as about 88 percent attendance. It is safe, repeatable, and sized to match real volumes. If production data must be used, it is masked or anonymized first, never copied as is.
How to answer: Explain why production copies are risky (personal data, access control, compliance). Describe a generator's qualities: deterministic, realistic distributions, correct relationships, and fast set-based loading. Discuss the trade-off: generated data can miss rare real-world cases, so add known edge cases deliberately.
Resources
- Code for this lesson: git tag
lesson-15-sample-data(code repository coming soon) - Files:
database/015-1-sample-data.sql,database/015-2-bulk-data.sql,library-assignment/015-library-data.sql - Video: coming soon
What's next
Module 3 is complete: the SMS database is built and full of data. Module 4 starts asking it questions. Lesson 16: SELECT basics
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.