Interview Questions

SQL Server is the database server: a service that stores the data and runs queries. SSMS (SQL Server Management Studio) is a client tool that connects to SQL Server so you can write queries and manage objects. Uninstalling SSMS does not touch your data.

LocalDB is a lightweight version of SQL Server that starts on demand and needs no service setup; its server name is (localdb)\MSSQLLocalDB. It suits laptops with little memory and simple development. The Developer edition is a full server with every feature, such as SQL Server Agent, so it is better for realistic development and testing.

Developer edition for development and testing: it has every feature and is free, but it is licensed only for non-production use. For the live server, Express is free but limited (for example, 10 GB per database). Most real schools need Standard edition, or a managed cloud database, depending on size, availability needs, and budget.

Check before you create or change anything. Use IF DB_ID(N'SchoolDb') IS NULL before CREATE DATABASE, IF OBJECT_ID(N'dbo.Students') IS NULL before CREATE TABLE, and CREATE OR ALTER for views and procedures. Such scripts are called idempotent: running them twice leaves the same result as running them once.

GO is not a T-SQL command; SSMS and sqlcmd use it to split a script into batches and send each batch separately. Some statements, such as CREATE VIEW or CREATE PROCEDURE, must be the first statement in a batch. Variables declared before a GO do not exist after it.

A relational database stores data in tables made of rows and columns, and links tables to each other with keys. An RDBMS (Relational Database Management System), such as SQL Server, is the software that stores the data, runs queries, and enforces rules so many users can work safely at the same time.

A table stores one kind of thing, such as students. A row is one item of that thing, such as one student. A column is one detail that every row has, such as DateOfBirth.

DDL (Data Definition Language) defines the structure: CREATE, ALTER, and DROP for tables and other objects. DML (Data Manipulation Language) changes the data inside them: INSERT, UPDATE, and DELETE. Creating the Students table is DDL; adding a student is DML.

master stores server-wide information such as databases and logins. model is the template copied for every new database. msdb stores SQL Server Agent jobs and backup history. tempdb holds temporary objects and work tables, and is recreated at every restart. Application tables never belong in any of them.

SQL is the standard language for relational databases. T-SQL (Transact-SQL) is Microsoft's extension of SQL for SQL Server. It adds variables, control flow such as IF and WHILE, and error handling with TRY...CATCH.

A schema is a named group of objects inside a database, used for organization and permissions; dbo is the default. Writing dbo.Students names the object exactly, so SQL Server does not have to resolve it per user. That is clearer, avoids picking the wrong object, and lets cached query plans be reused.

A database lets many staff work at once without overwriting each other, enforces rules such as unique admission numbers and valid marks, and links data without copying it. It answers questions like "all of Kabir's absences this term" instantly, and adds security, backups, and an audit trail of who changed what.

tempdb holds temporary tables and table variables, and work areas for sorting, hashing, and row versioning. Every database on the server shares it. A busy or badly sized tempdb slows down every application on that server, so it is usually given several data files on fast storage.

A relational database. School data has a stable structure and strict rules: every mark belongs to a real enrollment, and fees must add up exactly across invoices and payments. Constraints and transactions enforce exactly that. NoSQL fits flexible documents or huge write-heavy workloads, which is not the core of a school system.

An ER (Entity-Relationship) diagram is a picture of what a database stores and how the parts connect. An entity is a thing to remember, such as a student, and becomes a table. An attribute is a detail of an entity, such as a date of birth, and becomes a column.

One-to-one: one row on each side at most, such as a teacher and their login. One-to-many: one row links to many, such as a class and its sections. Many-to-many: many on both sides, such as students and guardians, which needs a junction table.

A class has many sections, and each section belongs to one class. The foreign key always goes on the "many" side, so Sections has a ClassId column that points to Classes.

Add a junction table with a foreign key to each side, and make the pair of keys its primary key. For students and guardians, StudentGuardians has StudentId and GuardianId; each row is one link.

The symbols at each end of a line show how many rows can be on that side: || is exactly one, o| is zero or one, |{ is one or more, and o{ is zero or more. The three-pronged "crow's foot" means many. Classes ||--|{ Sections reads "one class has one or more sections".

A junction table connects two tables in a many-to-many relationship. Yes, it can hold facts about the link itself. StudentGuardians stores Relationship (Father, Mother, Guardian) and IsPrimaryContact, because those describe the pair, not either side alone.

A student changes section every year. A SectionId on Students would overwrite last year's section and break the history of attendance, marks, and fees. Enrollments keeps one row per student per academic year, and everything year-specific links to it.

Make the relationship its own table and add the period it is valid for. In the SMS, SubjectAllocations links teacher, subject, section, and academic year, with a unique rule on (year, section, subject). Next year gets new rows, and old years stay correct for reports.

Keep attendance in its own table linked to the enrollment, and plan an optional period column now. Daily records leave it empty; period records fill it, with a unique rule on enrollment, date, and period. Reports read both, so old data stays valid and new data adds detail.

Normalization is a set of rules for splitting tables so that every fact is stored once. It prevents update, insert, and delete anomalies, where copies of the same fact disagree or are lost. The usual target for business systems is third normal form.

A table is in 1NF when every cell holds a single value, there are no repeating columns such as Guardian1 and Guardian2, and every row can be identified by a key. A cell containing "Rajesh Sharma, Neha Sharma" breaks 1NF.

An update anomaly: a fact stored twice is changed in one place only, so copies disagree. An insert anomaly: you cannot record one fact without another, such as a class teacher without a student. A delete anomaly: deleting one row removes an unrelated fact, such as a guardian's phone.

A table is in 2NF when it is in 1NF and every non-key column depends on the whole key. In a table keyed by (AdmissionNumber, GuardianName), StudentName depends only on the admission number: a partial dependency. Moving student details to Students and guardian details to Guardians fixes it.

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. ClassTeacher on Students depends on the section, which depends on the student: a transitive dependency. Moving the class teacher to ClassTeachers (per section and year) fixes it.

A partial dependency is a column that depends on only part of a composite key; 2NF removes it. A transitive dependency is a column that depends on the key only through another non-key column; 3NF removes it. Short test: a column must depend on "the key, the whole key, and nothing but the key".

Denormalize when a measured performance or history need justifies a copy, such as an invoice total stored on FeeInvoices. Keep it correct with one owner: write it in the same transaction as the source rows, block other updates, or rebuild it on a schedule. Without an owner, copies drift.

First measure: most slow joins are missing indexes or return too many rows, and copying the name fixes neither. If the report is still slow after indexing, consider a reporting view or summary table rebuilt on a schedule. Copying names onto payments creates a second place to update whenever a name is corrected.

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'...'.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

NOT NULL (a value is required), PRIMARY KEY (unique and not NULL), UNIQUE (no duplicates), CHECK (the value must pass a test), DEFAULT (a value when none is given), and FOREIGN KEY (the value must exist in another table). Together they keep data valid no matter which program writes it.

Both prevent duplicates. A table has only one primary key, and it never allows NULL. A table can have many unique constraints, and each allows one NULL. The primary key is the row's identity; unique constraints protect other business values such as a subject code.

A foreign key makes a column's values refer to an existing primary key in another table. SQL Server then refuses a value that does not exist there, such as a section for class 99, and refuses to delete a row that is still referenced. This is called referential integrity.

Use a filtered unique index: CREATE UNIQUE INDEX ... ON dbo.AcademicYears (IsCurrent) WHERE IsCurrent = 1. Uniqueness then applies only to rows that match the filter, so many past years can have 0 but only one year can have 1. A plain UNIQUE (IsCurrent) would wrongly allow only one past year.

The delete fails with error 547: "The DELETE statement conflicted with the REFERENCE constraint FK_Sections_Classes". By default a foreign key blocks deleting a referenced row. You can choose ON DELETE CASCADE or SET NULL instead, but for school records blocking is safer.

Both, for different reasons. The application checks it to give the user a friendly message. The CHECK constraint guarantees it for every path into the database, including imports and manual fixes. A simple, permanent rule about one row belongs in a CHECK.

First find and fix or archive the invalid rows, then add the constraint normally so SQL Server checks all existing data. WITH NOCHECK can add it without checking old rows, but the constraint is then marked untrusted, the optimizer cannot rely on it, and the bad data stays.

Filtered indexes require QUOTED_IDENTIFIER ON. SSMS turns it on by default, but sqlcmd does not, so the CREATE INDEX fails with error 1934. Run sqlcmd with -I, or put SET QUOTED_IDENTIFIER ON in the script. Later inserts and updates on that table need the setting too.

Rules that must always be true, whatever the context, belong in constraints: uniqueness, required values, valid ranges, and relationships. Rules that depend on who is acting, when, or on several steps belong in code: permissions, workflows, and time windows. Many important rules live in both places.

A guardian is a person who can have several children in the school. With their own table, each guardian is stored once, with one phone number, and the StudentGuardians junction table links them to each child. Parent columns would repeat the same data for siblings.

A composite primary key is made of two or more columns whose combination identifies the row. In StudentGuardians, (StudentId, GuardianId) is the key: one guardian can be linked to many students, but the same pair cannot appear twice.

Inline constraints are written inside CREATE TABLE, either after a column or after all columns, so the whole definition is in one place. ALTER TABLE ... ADD CONSTRAINT adds a rule to a table that already exists. The result is the same; inline is simpler for new tables, ALTER for changes.

The list of statuses is short, fixed, and drives code behaviour, so a CHECK constraint is the simplest way to keep values valid. A lookup table is better when users must add or rename values without a code change, or when each value needs extra data such as a display name.

Add a filtered unique index on StudentGuardians (StudentId) WHERE IsPrimaryContact = 1. Each student can then have any number of guardians, but only one row per student can be marked as the primary contact.

A foreign key can only reference a table that already exists. So tables that others point to, such as Students and Guardians, are created first, and tables that point to them, such as StudentGuardians, afterwards. Data must be inserted in the same order.

The student keeps the same Students row and admission number; their status changes to Withdrawn when they leave and back to Active when they return. The gap years simply have no enrollment, and the return year gets a new Enrollments row. History before and after stays connected to one person.

Collect only what the school needs, restrict who can read sensitive columns, and use made-up data in development and test. SQL Server features such as permissions on columns or Dynamic Data Masking can hide values from users who do not need them, and backups must be protected like the live database.

Separate tables, as in the SMS, are simpler: each has its own columns and rules, and queries stay clear. A single People table (a supertype) avoids repeating name and contact columns and suits people with several roles, such as a teacher who is also a parent, but it adds joins and nullable role-specific columns.

It makes the combination of the columns unique, not each column on its own. UNIQUE (StudentId, AcademicYearId) lets a student appear once per year, so the same student can still have one row for every year.

With UNIQUE (SectionId, AcademicYearId, RollNumber) on Enrollments. Roll number 3 can exist in 10-A and 10-B, and in 10-A next year, but only once in 10-A in the same year.

One student in one section for one academic year, with a roll number. Attendance, marks, and fee invoices all link to the enrollment, so every record knows which year and section it belongs to.

Because the facts they protect are true per year. A student is in one section per year, and roll numbers repeat each year. Without the year, UNIQUE (StudentId) would allow a student to be enrolled only once in their life.

The class teacher changes every year, while a section such as 10-A exists every year. A column on Sections would be overwritten each year and lose history. ClassTeachers keeps one row per section per year, protected by UNIQUE (AcademicYearId, SectionId).

It stores which teacher teaches which subject to which section in each academic year, with four foreign keys. UNIQUE (AcademicYearId, SectionId, SubjectId) ensures one teacher per subject per section per year, while one teacher can still teach many sections.

The simple design allows one enrollment per year, so you correct the section on the existing enrollment and record the change in the audit columns. If the school needs to know which section the student was in on each date, the design must change: add a "valid from" date and one row per move, and adjust the unique rules.

A constraint cannot count rows, so this needs logic: a stored procedure or the API checks the current count before enrolling, inside a transaction that locks the section, so two enrollments at the same moment cannot both pass. A trigger can act as a safety net.

Create the new academic year, then insert next year's enrollments in one set-based statement from this year's enrollments, moving each student up a class. Run it in a transaction, make it safe to re-run, and produce a review report first so the school can correct exceptions such as students who repeat a year or leave.

Attendance grows by one row per student per school day. One school of 10,000 students adds about 2 million rows a year, and many schools together pass the 2.1 billion limit of INT. BIGINT costs four more bytes per row and removes that risk.

With UNIQUE (EnrollmentId, AttendanceDate) on AttendanceRecords. A second record for the same enrollment and date is refused, so a double click or a network retry cannot create a duplicate.

Zero means "attended and scored nothing"; absent means "did not write the paper". Storing 0 would lower averages and show a misleading report card. MarksObtained is NULL and IsAbsent is 1, so reports can treat absence correctly.

CHECK ((IsAbsent = 1 AND MarksObtained IS NULL) OR (IsAbsent = 0 AND MarksObtained >= 0)) allows only two valid states: absent with no marks, or present with marks of zero or more. It refuses "absent with 45 marks" and "present with no marks". A CHECK can compare columns of the same row like this.

An exam such as "Unit Test 1" has many papers: one per class and subject, each with its own date, maximum marks, and pass marks. ExamSubjects stores those papers, and each mark belongs to one paper. Without it, every mark would have to repeat the paper's details.

A CHECK constraint can only see the row being saved. The maximum lives in ExamSubjects, another table. Cross-table rules need a trigger, a stored procedure, or application code; the SMS adds a trigger in Lesson 23 and the same check in the API.

Thousands of inserts arrive in a few minutes, so saves must be fast and safe to retry. Save a whole section in one set-based statement inside a transaction, rely on the unique constraint to stop duplicates, use an "insert or update" approach for corrections, and keep the table's indexes minimal so inserts stay cheap.

Allow the correction only for authorized roles, record who changed it and when in the audit columns, and keep the old value in an audit log or history table. Reprint the affected report card and keep the reason for the change. A silent overwrite would make the printed and stored marks disagree with no explanation.

That is billions of rows, so plan for size from the start: BIGINT keys, partitioning by date so old periods can be managed separately, archiving closed years to cheaper storage, and summary tables for reports that need totals rather than single days. Daily screens then only touch the current year's data.

Columns that record who created a row and when (CreatedAt, CreatedBy), and who last changed it and when (UpdatedAt, UpdatedBy). CreatedAt usually has a default of SYSUTCDATETIME(). They answer "who and when" for the latest change, not the full history.

Instead of removing a row with DELETE, you mark it as deleted with IsDeleted = 1 and a DeletedAt time. Screens and reports leave those rows out, and the row can be restored by setting the flag back.

Every query must filter IsDeleted = 0, and forgetting it shows deleted rows in reports. Unique constraints still see deleted rows, so a deleted record's unique value stays taken. Tables never shrink. Use it only where restore or history is required, and hide the filter in views.

Audit columns record only the latest change, which is enough for "who touched this last". When you must know every old value, such as for marks or payments, you need a history: an audit table filled by triggers, a system-versioned temporal table, or change data capture. Each keeps old versions at the cost of storage.

Add it with a default in the same statement, for example ALTER TABLE dbo.Students ADD IsDeleted BIT NOT NULL CONSTRAINT DF_Students_IsDeleted DEFAULT (0). Existing rows get the default value. Without a default, the statement fails because existing rows would have no value.

The header (FeeInvoices) holds facts about the whole bill: number, date, student, total, status. Each line (FeeInvoiceItems) holds one fee type and amount. One fact per row means any number of fee types per invoice, and reports can total tuition or exam fees across all invoices.

An invoice can have many rows in FeePayments. The balance is the invoice total, minus its discount, minus the sum of its payments. The invoice status (Unpaid, PartiallyPaid, Paid) is updated in the same transaction that records a payment.

Make the payment request idempotent: the client sends a unique payment reference, and a unique constraint on it refuses the second insert. Record the payment and update the invoice status in one transaction, and have the screen disable the button after the first click. The database rule is what makes it reliable.

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.

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".

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.

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.

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.

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.

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.

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.

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.

Logically: FROM (and joins), WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then TOP. That is why a column alias defined in SELECT cannot be used in WHERE, but can be used in ORDER BY.

NULL means unknown, and comparing anything with unknown gives unknown, which is never true. So = NULL matches nothing, even rows that are NULL. Use IS NULL or IS NOT NULL instead.

% matches any text of any length, including none, and _ matches exactly one character. LastName LIKE N'Sh%' matches Sharma and Sheikh; LIKE '_A' matches any two-character value ending in A.

Without ORDER BY, SQL Server returns whichever rows it reaches first, and that can change between runs, after an index change, or under load. TOP (3) ... ORDER BY DateOfBirth DESC always returns the three youngest students.

It returns columns the code does not need, which wastes network and memory and can stop SQL Server from using a narrower index. It also changes silently when someone adds a column, which can break code that reads columns by position. List the columns you need.

Run a SELECT with the exact WHERE first and check the rows. Then start a transaction, run the change with the same WHERE, check @@ROWCOUNT and the data, and only then COMMIT; otherwise ROLLBACK.

Stop further changes to that table, find out exactly what changed and when, and restore the correct values: from a backup restored to a separate database, from audit data, or from a point-in-time restore. Communicate early with the business. Afterwards, change the process so it cannot happen again.

A pattern that starts with % cannot use an index seek, because the matching text could be anywhere in the value; SQL Server has to scan every row. A pattern like N'Sharma%' can seek. For true "contains" search on large tables, use full-text search or a search index.

Data fixes are written as scripts, reviewed by a second person, tested on a copy of production, and run by the release process with a transaction and row-count checks. The script and its result are stored, so every change is traceable. Nobody types changes directly into production.

INNER JOIN returns only rows that match on both sides. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL when there is no match. Every teacher with their class (LEFT) shows teachers who are not class teachers; INNER would hide them.

The join condition, written after ON, says which rows of the two tables belong together. It is usually a foreign key equal to the primary key it references, such as ON e.StudentId = s.Id.

Join Students to Enrollments, then to Sections and AcademicYears, and filter on the class, the section name, and IsCurrent = 1, ordered by roll number. Four tables, each joined on its foreign key.

If each row on the left matches several rows on the right, the left row appears once for each match. Joining 12 students to their guardians gives 24 rows, because each has two. Totals calculated after such a join are multiplied too.

A self join joins a table to itself using two aliases. To find students who share a guardian, join StudentGuardians to itself on the same GuardianId, with a condition like sg2.StudentId > sg1.StudentId so a student does not match themselves.

WHERE runs after the join. A condition there on the right table removes the rows where that table is NULL, which turns the LEFT JOIN into an INNER JOIN. In the ON clause, it only limits which right rows can match, and unmatched left rows are kept.

Doubled totals almost always come from a one-to-many join added after the data was already at the right level. Remove joins one at a time and watch the row count, or count rows per key to find the join that matches twice. Then aggregate before joining, or join only what the report needs.

FULL JOIN compares two lists where either side can have rows the other lacks, such as reconciling an imported fee file with the payments table. CROSS JOIN builds every combination, such as creating exam papers for every class and subject. Both are rare in daily screens.

First check whether the joins are the real problem: missing indexes on the join columns are the usual cause. If the report is still too slow, or many reports repeat the same joins, a reporting table or indexed view refreshed on a schedule can serve them, accepting slightly delayed data.

WHERE filters rows before they are grouped, and cannot use aggregates. HAVING filters groups after GROUP BY, so it can use aggregates, such as HAVING COUNT(*) < 5 to find small sections.

COUNT counts rows or values, SUM adds them, AVG averages them, and MIN and MAX return the smallest and largest. With GROUP BY, each returns one value per group, such as students per section or fees per class.

Every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise one group could have several values for that column, and SQL Server cannot choose one. Add the column to GROUP BY or aggregate it.

COUNT(*) counts every row. COUNT(column) counts rows where that column is not NULL, so absent students without marks are skipped. COUNT(DISTINCT column) counts different values. Four 10-A Science papers give 4, 3, and 1 respectively.

Put a CASE inside SUM: SUM(CASE WHEN Status IN ('Present', 'Late') THEN 1 ELSE 0 END) adds 1 for each matching row and 0 for others. Divided by COUNT(*) and multiplied by 100.0, it gives an attendance percentage per student.

When both sides of a division are whole numbers, SQL Server returns a whole number and drops the fraction. Make one side decimal, for example 100.0 * 2 / 3, or CAST a value to DECIMAL, to keep the fraction.

AVG skips NULLs, so absent students do not count. That is usually right, but the school must decide the rule. If absent should count as zero, use AVG(ISNULL(MarksObtained, 0)); if not, show the number of papers averaged next to the average so readers know.

Compare the definitions first: which statuses count as present, which dates are included, and whether deleted or transferred students are counted. Then recompute the number by hand from raw rows. Agree one definition with the school and make both reports use it.

Pre-compute the numbers: a summary table updated as data changes or on a short schedule, or an indexed view that SQL Server keeps up to date. The dashboard then reads a few rows instead of millions, at the cost of extra work on writes or slightly delayed numbers.

A subquery is a query inside another query, in brackets. It can return one value (used with = or >), a list (used with IN), or be used with EXISTS to test whether any row matches.

A CTE (Common Table Expression) is a named query written with WITH name AS ( ... ) before a statement. That statement can use the name like a table. It makes long queries readable as steps, and it exists only for that one statement.

Compare each mark with a subquery that returns the average for the same paper and section: WHERE m.MarksObtained > (SELECT AVG(MarksObtained) FROM dbo.Marks WHERE ...). In 10-A Unit Test 1 Mathematics, the average is 84.75, so Meera, Aarav, and Diya qualify.

If the subquery's list contains a NULL, x NOT IN (...) is unknown for every row, because "not equal to NULL" is never true. So the query returns nothing. Use NOT EXISTS, which is not affected by NULLs, or filter NULLs out inside the subquery.

IN checks whether a value is in a list returned by the subquery. EXISTS checks whether the subquery returns any row at all, and can stop at the first match. For positive checks they often perform the same; for negative checks, NOT EXISTS is safer than NOT IN because of NULLs.

A correlated subquery uses a column from the outer query, so it is evaluated for each outer row. Example: each student's last attendance date with (SELECT MAX(a.AttendanceDate) FROM dbo.AttendanceRecords AS a WHERE a.EnrollmentId = e.Id).

In a CTE, sum the payments per invoice. Then left join the invoices to it, compute total minus discount minus ISNULL(paid, 0), and keep rows where that is above zero. Summing first avoids multiplied totals, and the left join keeps invoices with no payments.

Most likely a NULL appeared in the column the NOT IN subquery returns, so the condition became unknown for every row and nothing qualified. Rewrite it with NOT EXISTS, fix or explain the NULL data, and add an alert when a reminder job processes zero rows.

A CTE for readable steps inside one statement. A temporary table when an intermediate result is reused several times, is large, or benefits from its own index. A view when many queries and reports need the same logic. Each adds reuse or speed at the cost of more objects to manage.

All three number rows in order. ROW_NUMBER always gives different numbers, even for ties. RANK gives ties the same number and then skips (1, 2, 3, 3, 5). DENSE_RANK gives ties the same number without gaps (1, 2, 3, 3, 4).

GROUP BY collapses each group into one row. A window function calculates across a group of rows but keeps every row, so you can show each student with their rank or the section average next to their mark.

It splits the rows into groups, and the window function restarts for each group. RANK() OVER (PARTITION BY SectionId ORDER BY Total DESC) ranks students within their own section, starting at 1 in every section.

Number the rows within each group of duplicates with ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY a tie-breaker) in a CTE. Rows numbered 1 are kept; delete or ignore the rest.

Use SUM(Amount) OVER (ORDER BY PaidOn, ReceiptNumber ROWS UNBOUNDED PRECEDING). Each row gets the total of all rows up to it. A unique ORDER BY and the explicit ROWS frame make the result predictable and fast.

LAG(column) returns the value from the previous row in the window, and LEAD(column) from the next row. They compare a row with its neighbours, such as this exam's marks against the previous exam's. The first row's LAG is NULL.

In a CTE, compute each student's total, then add ROW_NUMBER() OVER (PARTITION BY Section ORDER BY Total DESC, AdmissionNumber). In the outer query, keep rows numbered 1 to 3. Use RANK instead if tied students must both appear.

Move the ranking into SQL with a window function and return only the rows the page shows. The database does it in one pass with the data already there, and the network, memory, and page time drop sharply.

For large data sets, in the database: it avoids moving rows and uses set-based processing. For small results that the screen already holds, or rules that change often and need unit tests in C#, application code can be simpler. Keep one place responsible for each calculation.

A view is a saved SELECT with a name. It stores no data; every time you query it, SQL Server runs its query against the real tables. Views hide joins and filters, such as vw_CurrentEnrollments hiding four joins and the soft-delete filter.

A scalar function returns one value and is used in SELECT or WHERE, such as dbo.fn_GetGrade(92). A table-valued function returns a table and is used in FROM, such as dbo.fn_GetSectionResults(1, 19). An inline table-valued function is like a view that takes parameters.

So the IsDeleted = 0 filter is written once. Every report and API that reads the view automatically leaves deleted students out, and nobody can forget the filter. In the SMS, soft-deleting Meera drops the 10-A count from 4 to 3 through the view.

Both are saved queries used in FROM. A view takes no parameters, while an inline table-valued function does, such as an exam and section. SQL Server expands both into the calling query, so both perform like the query written out in full.

It runs once for every row it is applied to, and when used on a column in WHERE, it hides that column from indexes. SQL Server 2019 and later can inline simple scalar functions, but functions that read tables or use loops often cannot be inlined.

Yes, if the change affects one base table and the view has no DISTINCT, GROUP BY, or aggregates. Views with joins usually cannot accept changes to more than one table. In the SMS, views are for reading; changes go through tables or procedures.

Look at what the function does. If it reads tables, rewrite that logic as a join or an inline table-valued function so SQL Server processes all rows as one set. Compare the plans and timings before and after; this kind of rewrite often turns minutes into seconds.

Grant reporting users SELECT on views only, never on the tables. The view exposes only the columns and rows they may see, and because the view and tables have the same owner, SQL Server does not check table permissions (ownership chaining). The tables stay private.

When a frequently run aggregate over large tables is too slow and must be up to date, such as daily attendance totals. The indexed view stores its result and SQL Server maintains it on every change to the base tables, so reads are fast but writes become slower, and strict rules apply.

A stored procedure is a named block of T-SQL saved in the database, with parameters. It can read and change data and use transactions. The SMS uses usp_RecordFeePayment to check, save, number, and update a payment in one call.

A procedure can change data and use transactions, and is called with EXEC. A function cannot change data, and is used inside a query, returning one value or a table. Use procedures for business actions and functions for calculations and reusable reads.

A parameter that sends a value back to the caller. usp_RecordFeePayment returns the new receipt number through @ReceiptNumber OUTPUT, and the caller passes a variable with the OUTPUT keyword to receive it.

It stops SQL Server from sending a "rows affected" message after every statement. The application does not need them, they add network traffic, and some client libraries can confuse them with result sets.

Parameters are treated as values, never as code, so input like '; DELETE ... is just text. A procedure is still vulnerable if it builds dynamic SQL by joining strings with input. Dynamic SQL must pass values through sp_executesql parameters.

Two sessions running MAX + 1 at the same time can read the same maximum and produce the same number. A sequence hands out each number exactly once, without locking the table. Numbers used in rolled-back transactions are not returned, so gaps can appear.

It reads the invoice WITH (UPDLOCK) inside its transaction. The second payment waits until the first commits, then sees the updated balance, so both cannot pass the balance check. The receipt numbers come from a sequence, so they never collide.

Treat parameters and result columns as a contract. Use CREATE OR ALTER so permissions are kept, add new parameters at the end with defaults, and do not rename or remove result columns that callers read. Deploy the database change before the application change that uses it.

Data-heavy, transactional steps that must be atomic, such as recording a payment, fit procedures well. Workflows, validation messages, integrations, and logic that needs unit tests fit application code better. Most systems use both, with a clear rule for which goes where.

A trigger is code attached to a table that SQL Server runs automatically when rows are inserted, updated, or deleted. It runs inside the same transaction, so it can refuse a change with an error or record it in another table.

They are temporary tables with the same columns as the real table. inserted holds new rows (after an insert or update), and deleted holds old rows (after an update or delete). For an update, joining them shows each row before and after.

The maximum is in ExamSubjects, another table, and a CHECK constraint can only see the row being saved. The trigger joins inserted to ExamSubjects and throws error 50001 if any mark is too high.

Once. Triggers fire per statement, with all 500 rows in inserted and deleted. Code that assumes a single row, such as assigning a column to a variable, silently handles only one of them; trigger code must be set-based.

The statement that fired it fails, and its transaction is rolled back, including any rows the trigger itself wrote. In the SMS, updating two marks where one is 105 fails as a whole: both marks keep their old values.

The API stores the logged-in user's Id in SESSION_CONTEXT for the connection, with sp_set_session_context. The trigger reads it with SESSION_CONTEXT(N'UserId'), so it knows the user without the API passing it to every statement.

Read the trigger: loops, cursors, queries without indexes, or calls to other databases or services run inside every save. Check the plan of the trigger's statements and the wait types during saves. Rewrite it set-based and move any external work out of the transaction.

Use a constraint when the rule concerns one row and is always true. Use a procedure for a multi-step business action. Use a trigger only when the rule needs other tables and must hold for every way data is written, such as imports and manual fixes.

Record old and new values with who and when for the tables that matter, using triggers, temporal tables, or change data capture. Store audit data so it cannot be edited by the application, partition or archive it by date, and plan its size, retention, and access.

A group of changes that succeed together or fail together. BEGIN TRANSACTION starts it, COMMIT makes every change permanent, and ROLLBACK undoes them all. Recording a payment and updating the invoice status is one transaction.

Atomicity (all or nothing), Consistency (rules hold before and after), Isolation (others do not see half-done work), and Durability (committed changes survive a crash). Together they describe what a database transaction guarantees.

SQL Server's default: without BEGIN TRANSACTION, each statement is its own transaction and is committed as soon as it succeeds. Two related statements run this way are not protected; if the second fails, the first stays saved.

Not by default. SQL Server undoes only the failed statement; the transaction stays open with the first change in it, and a later COMMIT saves it. Use TRY...CATCH with ROLLBACK, or SET XACT_ABORT ON, so any error undoes everything.

With it on, any run-time error rolls back the whole transaction and stops the batch. It guarantees that a failure never leaves a half-done transaction, even when a CATCH block is missing or a client times out. Procedures that change data should use it.

BEGIN TRY BEGIN TRANSACTION; ...changes...; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH;. The check avoids an error when no transaction is open, and THROW; passes the original error to the caller.

Not really. Each BEGIN TRANSACTION increases @@TRANCOUNT, and an inner COMMIT only decreases it; nothing is saved until the outermost COMMIT. A ROLLBACK anywhere undoes everything and sets the count to 0. Savepoints are the only way to undo part of a transaction.

Run DBCC OPENTRAN in the database to see the oldest open transaction and its session, and query sys.dm_exec_requests for blocked sessions and who blocks them. Often it is an SSMS window with an uncommitted change, or an application path that never commits. Close or kill it, then fix the cause.

A database transaction cannot include an external service. Record the intent first in the database, call the service with an idempotency key, then record the result; a background job retries or reconciles anything left pending. The design accepts brief inconsistency and always ends in a known state.

A shared lock is taken for reading; many sessions can hold it at once. An exclusive lock is taken for changing data; only one session can hold it, and it blocks other writers and, by default, readers. Exclusive locks last until the transaction ends.

READ COMMITTED. Readers see only committed data, and wait for writers that hold locks on the rows they need. It prevents dirty reads but still allows non-repeatable reads and phantoms.

Two sessions each hold a lock the other needs, so neither can continue. SQL Server detects it within seconds, chooses one session as the victim, rolls it back, and returns error 1205, letting the other finish.

A dirty read sees a change that another transaction has not committed and may roll back. READ UNCOMMITTED and the NOLOCK hint allow it. In the SMS demo, a NOLOCK read returned a phone number that was rolled back a moment later.

Query sys.dm_exec_requests for rows where blocking_session_id <> 0, joined to sys.dm_exec_sql_text for the waiting query. It shows who waits, who blocks, the wait type such as LCK_M_S, and for how long.

A database setting that makes normal READ COMMITTED reads use the last committed version of each row instead of waiting for writers' locks. Readers stop blocking on writers, and still never see uncommitted data. The cost is row versions kept in tempdb.

Capture the deadlock details (from the system_health session) to see which statements and resources are involved. Then make every code path touch tables and rows in the same order, keep transactions short, add indexes so fewer rows are locked, and retry error 1205 a few times.

NOLOCK can return uncommitted rows, skip rows, or read rows twice when pages move, so reports can show wrong numbers. Find the real cause of the blocking, usually long transactions or missing indexes, and consider RCSI so reports read committed versions without waiting.

Turn on RCSI so reads do not block on writes, keep READ COMMITTED as the default, and use stronger locking only where needed, such as UPDLOCK on an invoice during payment. Test under realistic concurrency, and monitor blocking and deadlocks in production.

A sorted structure that lets SQL Server find rows without reading the whole table, like the index at the back of a book. In the SMS, an index on AttendanceDate cut one day's attendance query from 3,351 page reads to 38.

The clustered index is the table itself, stored in key order; there is only one. A nonclustered index is a separate sorted copy of some columns with a pointer to the row; a table can have many. Primary keys are clustered by default.

A seek jumps straight to the matching values in a sorted index and reads few pages. A scan reads the whole index or table. Scans are fine for small tables or when most rows are needed, but a scan on a big table for a few rows usually means a missing or unusable index.

A covering index contains every column a query needs, so no key lookups to the table are required. INCLUDE adds columns to the index's leaf level without sorting by them, such as INCLUDE (Status) on the attendance date index.

SQL Server can seek only on the leading column or columns. An index on (ExamSubjectId, EnrollmentId) helps a search by paper, but not a search by enrollment alone, which is why the SMS needed a separate index starting with EnrollmentId.

Every insert, update, and delete on the indexed columns must also update each index, so writes get slower, and each index takes storage and memory. Add indexes for frequent, measured queries, and drop indexes that are never used.

Measure the query with SET STATISTICS IO and the actual plan, and find the scan on the big table. Index the filtered column first, here AttendanceDate, and include the columns the query reads, here Status. Measure again: in the SMS, reads dropped from 3,351 to 38.

Check sys.dm_db_index_usage_stats over a representative period to find indexes with many updates and no reads, and look for indexes that duplicate or overlap others. Drop or merge them after review, keeping the ones that serve important queries.

Start with primary keys, unique constraints, and indexes on foreign keys used in joins. Then add indexes for the most frequent and most important queries, tested with realistic data volumes. Review usage and missing-index data after launch, and maintain indexes and statistics on a schedule.

The step-by-step recipe SQL Server chooses to run a query: which indexes to read, in what order, and how to join the results. You read it in SSMS to see why a query is slow.

The estimated plan is built without running the query and shows guessed row counts. The actual plan is captured while the query runs and adds real row counts. Use the actual plan when you can, because big gaps between estimated and actual rows point to problems.

It reports the logical reads (8 KB pages read) for each table a query touches. Logical reads do not change with caching, so they are a fairer before-and-after measure than duration.

A SARGable condition lets SQL Server seek in an index. YEAR(AttendanceDate) = 2026 is not SARGable because the function hides the column; in the SMS it read 1,567 pages, while AttendanceDate = '2026-07-01' read 38. Compare the bare column, using a date range if needed.

When a value's type differs from the column's, SQL Server converts one of them. If it must convert the column, as with a VARCHAR column compared to an NVARCHAR value, it cannot seek. In the SMS, N'ADM-2017-0001' caused a scan of 39 reads; 'ADM-2017-0001' was a 4-read seek.

A key lookup fetches columns missing from a nonclustered index, one row at a time. For many rows, SQL Server decides a full scan is cheaper: 668 matching Sharmas led to a scan of 126 reads. A covering index with INCLUDE turned it into a 6-read seek.

Check for blocking first, then compare today's plan with the old one, using Query Store if it is on. Common causes are parameter sniffing (a plan built for unusual values), outdated statistics after a big data load, and data growth past a tipping point.

SQL Server builds a procedure's plan for the first parameter values it sees and reuses it. That plan can be poor for very different values, such as a tiny section versus a huge one. Fixes include better indexes, OPTION (RECOMPILE) for a specific query, or forcing a good plan with Query Store.

Turn on Query Store to keep plan and runtime history, and track the top queries by total CPU and reads. Alert on regressions, blocking, and deadlocks, and review the top queries regularly, tuning with the measure, change, measure method.

A login is server-level and controls who can connect. A user is database-level, mapped to a login, and holds permissions inside one database. For example, SchoolApiLogin connects, and SchoolApiUser works in SchoolDb.

GRANT allows an action, DENY forbids it, and REVOKE removes an earlier grant or deny. DENY always wins over GRANT, which is how a role can delete in general but never delete from Students.

A full backup copies the whole database. A differential copies changes since the last full backup. A log backup copies changes since the last log backup and allows restoring to a point in time.

SIMPLE allows no log backups, so you can restore only to the last full or differential backup. FULL needs regular log backups, or the log keeps growing, and it allows restoring to any point in time. Use SIMPLE for development and FULL for production.

One bug or SQL injection would then have full control, including dropping the database. Least privilege gives the application its own login and a role with only the permissions it needs, such as running procedures and changing specific tables.

RPO (Recovery Point Objective) is how much data the business can afford to lose, such as 15 minutes. RTO (Recovery Time Objective) is how long the system may be down, such as 2 hours. Together they decide the backup types and schedule.

Use the FULL recovery model, a full backup on Sunday night, differentials on the other nights, and log backups every 15 minutes. To restore: Sunday's full backup, Tuesday night's differential, then every log backup up to 10:40 with STOPAT.

RESTORE VERIFYONLY checks that a backup file is readable, but only a real restore proves it. Restore regularly to a test server, run DBCC CHECKDB, check key row counts, and time it against the RTO.

Follow 3-2-1: three copies, on two kinds of storage, one off site, plus one immutable copy that cannot be changed or deleted for a set time. Limit who can reach backup storage, use separate credentials for it, and test restores from the immutable copy.

Use DENSE_RANK() OVER (ORDER BY MarksObtained DESC) and keep rank 2, or use a subquery: MAX(MarksObtained) WHERE MarksObtained < (SELECT MAX(...)). For 10-A's Mathematics marks of 95, 92, 85, and 67, both return 92.00, and DENSE_RANK handles ties and any N.

Group by the column and keep groups with more than one row: SELECT LastName, COUNT(*) FROM dbo.Students GROUP BY LastName HAVING COUNT(*) > 1;. To delete duplicates while keeping one, use ROW_NUMBER() partitioned by the column.

UNION ALL keeps every row; UNION removes duplicates, which needs a sort or hash. In the SMS, 8 teacher and 22 guardian last names give 30 rows with UNION ALL but 19 with UNION. Prefer UNION ALL when duplicates are impossible or wanted.

Use NOT EXISTS with a correlated subquery, or a LEFT JOIN and WHERE right.Id IS NULL. Avoid NOT IN when the subquery's column can be NULL, because one NULL makes it return no rows.

Use conditional aggregation: SUM(CASE WHEN Status = 'Present' THEN 1 ELSE 0 END) AS Present, one expression per status, grouped by student. SQL Server's PIVOT keyword does the same, but SUM(CASE ...) is easier to read and works in every database.

DATEDIFF counts year boundaries crossed, not birthdays. Someone born 23 August 2011 gets 15 on 1 July 2026, but is really 14. Subtract 1 when the birthday has not yet happened in that year.

In about two minutes, cover what it is, the key design choices, the rules enforced in the database, the performance work with numbers, and safety. For the SMS: 22 tables, enrollments per year, a trigger and transactional procedures, indexes cutting 3,351 reads to 38, and least-privilege roles with tested backups.

Restate the symptom and ask what changed, then measure: actual plan, logical reads, and blocking. List the likely causes in order, such as a plan change, outdated statistics, or a missing index after data growth. Make the smallest fix, measure again, and add monitoring.

Mix a short live task on a realistic schema, a design discussion, and a scenario question. Judge how they clarify requirements, handle NULLs and ties, test edge cases, and explain trade-offs, not just whether the query runs.

A table whose name starts with #, stored in tempdb and visible only to your session. It lives until the session ends or you drop it. You use it to save an intermediate step of a report and reuse it in later queries.

#Name is a local temp table, visible only to the session that created it. ##Name is a global temp table, visible to every session. Reports should use local tables, so two users never share or overwrite each other's data.

A cursor reads a query's rows one at a time. The steps are DECLARE, OPEN, FETCH NEXT, a WHILE @@FETCH_STATUS = 0 loop, and finally CLOSE and DEALLOCATE.

A CTE is not stored and belongs to one statement. A temp table lives in tempdb until the session ends, has statistics, and can have any index. A table variable lives for one batch, has no column statistics, and is not undone by ROLLBACK. Use temp tables for big or reused steps.

A cursor runs one small query per row, each with its own overhead. A set-based query reads the data once in one plan. In the SMS, a cursor over 10,012 students took about 2.9 seconds, and one GROUP BY gave identical results in about 0.2 seconds.

They stay. Table variables are not part of the transaction, so ROLLBACK does not undo them. This is useful in a CATCH block: save error details in a table variable, roll back, and then write them to a log table.

Find what each loop iteration does, and express the whole job as a few set-based statements, using temp tables for intermediate steps. Prove the new version gives the same rows with EXCEPT, then measure. Keep a loop only for work that truly must happen one row at a time.

When each row must go through logic that handles one row at a time, such as calling a procedure like usp_RecordFeePayment for every payment in an uploaded file. Admin scripts that loop over databases or tables are another fair use. Use LOCAL FAST_FORWARD.

Set a review rule: set-based first, cursors only with a written reason. Provide patterns (temp-table steps, window functions, conditional aggregation), pair on rewriting the slowest jobs, and track their run times so the gains are visible.

Updating a row if it already exists and inserting it if it does not, in one operation. Loading a revised fee list, where some fees change and some are new, is an upsert.

WHEN MATCHED (in both, usually update), WHEN NOT MATCHED BY TARGET (only in the source, usually insert), and WHEN NOT MATCHED BY SOURCE (only in the target, usually delete). Each is optional, and the statement must end with a semicolon.

A table that holds incoming data, such as an uploaded file, before it touches the real tables. You can check and clean the data there, then apply it with a merge.

Add an OUTPUT clause. $action returns INSERT, UPDATE, or DELETE for each row, deleted holds the old values, and inserted the new ones. You can also send the output into a log table with OUTPUT ... INTO.

One target row matched more than one source row, so SQL Server would update or delete it twice. Prevent it with a primary key on the staging table, or remove duplicates before the merge.

Without it, two sessions running the same merge can both decide a row is missing and both insert it, causing a duplicate key error. HOLDLOCK keeps the range locked for the whole statement, so the second session waits.

The merge had a WHEN NOT MATCHED BY SOURCE THEN DELETE branch on the whole table, so every row missing from the file counted as deleted. Limit the target to the rows the file covers, preview deletes before running, and require confirmation. Foreign keys and backups are the last line of defense.

Both work when written carefully. MERGE is shorter and can also delete, but has more traps: duplicates, scoping, and concurrency. UPDATE plus INSERT in a transaction with UPDLOCK, HOLDLOCK is longer but easier to review. Follow the team standard.

Upload into a staging table with keys and validation, show a preview of inserts, updates, and deletes, and require a person to confirm. Apply in a short transaction with locking hints, log every change with OUTPUT, and keep the import idempotent so retries are harmless.

JSON is a text format of objects (name and value pairs), arrays, and values, used by web and mobile apps. SQL Server 2022 stores it as text in NVARCHAR(MAX); SQL Server 2025 adds a native json type.

FOR JSON PATH turns query results into JSON text. OPENJSON does the opposite: it turns JSON text into rows, which you can select or insert into a table.

ISJSON(@text) returns 1 for valid JSON and 0 otherwise. A trailing comma, for example, makes it return 0.

Use FOR JSON PATH. A dot in a column alias, like [currentClass.section], creates a nested object. A correlated subquery that ends with FOR JSON PATH creates an array. Add WITHOUT_ARRAY_WRAPPER to return a single object.

JSON_VALUE returns a single value, such as a number or text. JSON_QUERY returns an object or array. JSON_VALUE on an object returns NULL, which is a common surprise.

Use INSERT ... SELECT ... FROM OPENJSON(@json) WITH (Column TYPE '$.path', ...). The WITH clause names each column, its type, and its path; missing properties become NULL. Paths are case-sensitive.

Send the whole section as one JSON array and save it with one INSERT ... OPENJSON (or a bulk insert from C#) inside a transaction. The section is then saved completely or not at all, and the app can retry safely because the unique key prevents duplicates.

Use JSON for data with a varying shape that you store and return whole, such as audit details or extra settings. Use normal columns for anything you filter, join, sort, or must check with constraints.

Usually in the application: the API controls its own response format, and C# serializes objects automatically. FOR JSON is a good option for large nested reports read straight from the database, and OPENJSON for saving many rows sent in one call.

A table for which SQL Server automatically keeps every old version of every row in a history table. Two period columns, ValidFrom and ValidTo, record when each version was valid.

Query it with FOR SYSTEM_TIME AS OF @moment. SQL Server combines current and history rows to rebuild the table at that moment. FOR SYSTEM_TIME ALL returns every version.

UTC. SQL Server fills them itself, so a local time must be converted to UTC before using it in AS OF.

Add two DATETIME2 columns GENERATED ALWAYS AS ROW START and ROW END with defaults and a PERIOD FOR SYSTEM_TIME, then SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...)). HIDDEN keeps the new columns out of SELECT *.

Not while versioning is on: updates fail with error 13561 and deletes are blocked too. Only someone with ALTER TABLE permission can turn versioning off, which makes the history trustworthy.

No, only when. Keep an UpdatedBy column in the row; because it is versioned with the rest of the row, the history then shows who made each change.

Query the fee table FOR SYSTEM_TIME AS OF the payment time, converted to UTC, and show the amount that was valid then, plus who changed it and when from the versions list. The history cannot be edited, so the answer is reliable.

Audit triggers keep exactly what you choose and need code. Temporal tables keep every version automatically and answer "as of" questions. Change data capture reads changes from the log for other systems and needs SQL Server Agent.

Version tables whose past values matter and change rarely: fees, prices, rates, and settings. Avoid busy tables like attendance. Set a retention period that matches the legal rule, watch history size, and include history tables in backup and archiving plans.

The runtime only runs .NET applications that are already built. The SDK (Software Development Kit) contains the runtime plus the compiler and the dotnet command-line tools to create, build, test, and publish applications. Developers install the SDK; servers that only run the app need just the runtime.

LTS (Long Term Support) releases, such as .NET 10, get fixes and security patches for 3 years. STS (Standard Term Support) releases get them for 2 years. For a production system that must run for years, like a school system, choose LTS and plan upgrades from one LTS to the next.

The Common Language Runtime is the part of .NET that runs your compiled code. It loads the IL, turns it into machine code with the JIT compiler, manages memory with the garbage collector, and handles exceptions.

IL (Intermediate Language) is what the C# compiler produces: a platform-neutral set of instructions stored in the .dll file. The CLR turns it into machine code when the program runs.

.NET Framework is the original, Windows-only platform; version 4.8.1 is the last, and it only gets maintenance. .NET (Core, then .NET 5 and later) is cross-platform, faster, and where all new development happens.

Just-In-Time compilation turns IL into machine code the first time each method runs, on the machine that runs it. The same .dll can therefore run on Windows, Linux, and macOS, and the JIT can optimize for the exact processor.

Code written directly in Program.cs without a class or Main method. The compiler generates the Main method for you. Only one file in a project can have them, and they are ideal for small programs and for the startup code of ASP.NET Core apps.

In bin/Debug/net10.0: the .dll with your IL, an .exe launcher on Windows, a .pdb with debug information, runtimeconfig.json naming the .NET version to use, and deps.json listing dependencies. They are rebuilt every time and never committed.

Read the app's runtimeconfig.json to see which framework and version it needs, then run dotnet --list-runtimes on the server. If the matching runtime is missing, install it, or publish the app self-contained so it carries its own runtime.

One solution with projects in src (for example SchoolApp.Api, SchoolApp.Core, SchoolApp.Data) and tests. Core holds models and rules with no dependencies, Data references Core, and the API references both. Tests reference what they test.

Framework-dependent apps are small and use the runtime installed on the server, which the operations team patches. Self-contained apps carry their own runtime: larger, but independent of the server, and you must ship updates for security fixes yourself. Containers make this choice simpler, because the image includes the runtime.

int, long, decimal, string, DateOnly, and bool. Matching the types avoids conversion errors and silently wrong values when data moves between the database and C#.

It lets the compiler work out the variable's type from the value on the right, for example var tuition = 42000.00m; is a decimal. The type is still fixed at compile time; var only saves typing, so use it when the type is obvious.

int.Parse converts text to a number and throws an exception if it cannot. int.TryParse returns true or false and gives the number through an out parameter, so bad input never crashes the program. Use TryParse for anything a person types.

double stores numbers in binary, so most decimal fractions are approximations: 0.1 + 0.2 gives 0.30000000000000004. decimal stores base-10 numbers exactly, so sums of fees and payments are exact. double is for measurements where tiny errors do not matter.

An implicit conversion is always safe and needs no code, such as int to long. An explicit cast may lose data, so you must write it, such as (int)92.75m, which gives 92 because the fraction is cut off, not rounded.

A value type variable holds the value itself (numbers, bool, char, DateOnly, structs). A reference type variable holds a reference to an object stored elsewhere (string, classes, arrays), and can be null. Copying a value type copies the value; copying a reference copies only the reference.

The types used for money. If payments are summed in double, the balance can be a tiny non-zero number such as 0.00000000003, so the status check never sees zero. Change money variables to decimal, then correct the stuck invoices with a one-off script.

By default it wraps around silently: a byte at 255 plus 1 becomes 0. Choose types large enough for the data, such as long for Ids that can grow, and use a checked block or the project's overflow-checking setting where a wrong value would be dangerous, so overflow throws an exception instead.

Document one type mapping (for example DECIMAL to decimal, DATE to DateOnly), generate or scaffold entity classes from the database where possible, and check mismatches in code review and integration tests that round-trip real data.

= assigns a value to a variable; == compares two values and returns true or false. Writing if (mark = 33) does not compile in C#: error CS0029, because an int is not a bool.

for repeats a known number of times with a counter; while repeats while a condition is true and may run zero times; foreach visits each item in a collection; do...while runs at least once and then checks its condition.

break leaves the loop immediately. continue skips the rest of the current round and starts the next one. In a switch statement, break ends each case.

A switch statement runs a block of code per case, with case, break, and default. A switch expression produces a value from arms like >= 91 => "A1", checked from top to bottom, with _ for anything else. Switch expressions are shorter for mapping one value to another.

The compiler warns with CS8509 that the switch is not exhaustive, and at run time an unmatched value throws SwitchExpressionException. Always end with a _ discard arm.

condition ? a : b returns a when the condition is true, otherwise b, as a one-line if. Avoid it when the condition or values are complex, or when nested ternaries make the code hard to read; use if or a switch expression instead.

It is a boundary error: the condition uses > where the rule says "33 or more", so it should be >=. Fix the operator, use a named constant such as PassMark, and add tests at the exact boundaries (32, 33, 34) for every rule.

When input comes from a file or a job, it can end; Console.ReadLine then returns null every time, so a validation loop never gets valid input. Check for null and break, and design batch tools to read arguments or files instead of prompting.

Keep each rule in one authoritative place where possible, for example in a shared C# library used by every application, or in the database when reports depend on it. If a rule must exist twice, document it, test both against the same boundary cases, and change them together.

A fixed-size collection of items of one type, stored under one name. Positions (indexes) start at 0, so the last item is at Length - 1, also written ^1.

The program throws IndexOutOfRangeException: Index was outside the bounds of the array. For a six-item array, valid positions are 0 to 5.

$"..." inserts values into text inside braces, such as $"Topper: {name}". Format specifiers control the output: {average:F2} for two decimals, {serial:D5} for zero padding, {name,-8} for column width.

A string can never change after it is created. Methods such as Trim and ToUpper return a new string, so name.Trim(); without assigning the result does nothing. Immutability makes strings safe to share between threads and use as dictionary keys.

When building text in a loop. Each += copies all the text so far into a new string, so the cost grows with every line; StringBuilder appends to one buffer. In the SMS, 10,000 CSV lines took about 620 ms with += and 2 ms with StringBuilder.

string.Equals(a, b, StringComparison.OrdinalIgnoreCase), or methods like Contains and StartsWith with a StringComparison argument. Avoid a.ToUpper() == b.ToUpper(), which creates extra strings and can behave differently across cultures.

How the file text is built. Building it with += in a loop has a cost that grows with the square of the size, so small schools are fine and big ones time out. Switch to StringBuilder or write lines straight to a stream, then measure with production-sized data.

Two arrays kept in step, such as names and marks, break when one is sorted, filtered, or resized without the other, so values attach to the wrong student. Keep related values together in one object, such as a Student class or record, in one collection.

Ordinal, case-insensitive comparison for codes, user names, and emails; culture-aware formatting only for text shown to people; a fixed format such as yyyy-MM-dd for files and APIs; Unicode everywhere (NVARCHAR, UTF-8); and StringBuilder or streaming for large output.

A parameter is the named input in the method's definition, such as decimal maxMarks. An argument is the actual value passed in a call, such as 50m.

A parameter with a default value, such as decimal maxMarks = 100m. Callers can leave it out, and the default is used. Optional parameters must come after the required ones.

Several methods with the same name but different parameter lists, such as CalculateAttendancePercentage(int, int) and CalculateAttendancePercentage(string). The compiler picks one from the argument types. A different return type alone is not enough.

Both pass the caller's own variable. With ref, the variable must be assigned before the call, and the method may read and change it. With out, it need not be assigned before, and the method must assign it before returning. Both keywords appear in the definition and the call.

A method named TryX that returns true or false for success and hands back the result through an out parameter, like int.TryParse. It handles expected failures, such as bad input, without exceptions.

It lets callers pass any number of arguments, which C# collects into an array (or another collection type), such as Average(92m, 85m, 67m). It must be the last parameter.

Too many parameters usually mean the method does too much or the values belong together. Split it into smaller methods, or group related values into an object, such as a request class with properties. Named arguments help readability in the meantime.

Move the calculation into one well-named method with tests at its boundaries, and make every screen call it. Remove the copies. In reviews, treat duplicated business logic as a defect.

Use the Try pattern when failure is expected in normal use, such as parsing what a user typed. Throw an exception when failure means something is truly wrong, such as a missing database record that must exist. Many APIs offer both, like int.Parse and int.TryParse.

A value type with ?, such as int? or decimal?, that can hold either a value or null. It has HasValue and Value. It maps to SQL columns that allow NULL, such as a mark for an absent student.

?? returns the left value unless it is null, then the right value, like SQL's COALESCE. ?. accesses a member only if the object is not null, and returns null otherwise, instead of throwing NullReferenceException.

Using a member, such as .Length or a method, on a variable that holds null. The message is "Object reference not set to an instance of an object." Check for null first, or use ?. and ??.

A compiler feature, turned on with <Nullable>enable</Nullable>, where string means "never null" and string? means "may be null". The compiler warns (CS8600, CS8602, and others) when code might use a null unsafely. It changes warnings, not run-time behavior.

x! tells the compiler to treat x as not null and silences the warning. It does nothing at run time, so if x is null the program still crashes. Use it only when you truly know better than the compiler, with a comment.

Skip them: add only marks that are not null and divide by how many were counted, as SQL's AVG does. Treating an absent student's null as 0 lowers the average unfairly: 395 ÷ 5 is 79, but 395 ÷ 6 is 65.83.

Read the stack trace for the exact line, then find which value is null and why, for example a guardian with no email. Fix it where the value is used, with a null check and a sensible fallback, and make the type string? so the compiler warns at every other use.

Enable it project by project or file by file (#nullable enable), starting with shared core code. Fix warnings in small batches, annotate public APIs first, and turn warnings into errors once a project is clean, so it stays clean.

Decide per field: NULL-able columns map to ? types in C#, and the API either returns null in JSON or omits the field, documented in the API contract. Never turn missing data into fake values such as 0 or empty strings, which clients cannot tell apart from real data.

try holds code that might fail. catch runs when a matching exception is thrown in try. finally always runs afterwards, success or failure, so it is used for cleanup.

It travels up through every calling method. If none catches it, the program stops and prints "Unhandled exception" with the exception type, message, and stack trace.

A breakpoint (F9) pauses the program at a line so you can inspect variables. F10 steps over the current line; F11 steps into the method called on that line. The Locals and Call Stack windows show the values and the chain of calls.

throw; rethrows the current exception and keeps its original stack trace. throw ex; restarts the stack trace from the rethrow, so the place where the error really happened is lost. The analyzer warns with CA2200. Always use throw;.

C# uses the first catch block whose type matches. If catch (Exception) comes first, it matches everything, and later specific blocks never run. Put the most specific types first.

Return a result for expected situations, such as invalid user input, using the Try pattern. Throw when a rule is broken in a way the method cannot handle, such as a mark above the maximum reaching the save method. Exceptions are for exceptional cases, not normal flow.

A swallowed exception: an empty catch or one that logs nothing. Search for empty catch blocks around the save path, use the debugger's Exception Settings to stop where exceptions are thrown, and check database logs. Remove the empty catch so the real error appears, then fix the cause.

Read the exception type and message first, then the at lines from the top, skipping framework methods (System.) until you reach your own code. The first line in your code with a file and line number is usually where to start; the lines below show who called it.

Validate input early and return clear validation results. Let lower layers throw specific exceptions for broken rules, and do not catch what you cannot handle. Handle everything once, at the edge: global middleware logs the full exception with a correlation id and returns a safe, consistent error response, without exposing internal details.

A class is a design that describes what something has (properties) and can do (methods). An object is one instance created from it with new. Student is a class; Aarav and Diya are objects with their own values.

A special method with the class's name and no return type that runs once when an object is created. It sets up the object, and by requiring parameters it ensures no object exists without the values it needs, such as a student's name.

Encapsulation (a class protects its data and rules), inheritance (a class reuses and extends another), polymorphism (one call behaves differently for different types), and abstraction (code depends on what something does, not how).

The change. Classes are reference types: assignment copies the reference, not the object, so both variables point to the same object. To get an independent copy, create a new object.

For classes, == compares references by default: it asks whether both variables point to the same object. Two separately created objects are different objects. Records, or overriding Equals, give value-based equality.

Create a class that inherits from Exception and passes the message to base(message), adding any extra data, such as an ErrorNumber. Callers can then catch your application's rule failures separately from system errors and react to them specifically.

The parallel arrays went out of step, probably after sorting one of them. Replace them with one class that holds the student and the mark together, such as ExamResult, and keep a single collection of those objects.

A class that knows and does too much, such as a SchoolManager with hundreds of methods for students, fees, and exams. It is hard to change and test. Split it by responsibility into smaller classes, each with its own data and the rules that use that data.

Put the domain model and business rules that every application needs (entities, rule exceptions, interfaces) in the core library, with no dependency on UI, databases, or web frameworks. Keep presentation and infrastructure code in the projects that need them, which reference the core, never the other way round.

Keeping an object's data private and offering only safe, public ways to change it, so the object enforces its own rules. A fee invoice's status can change only through RecordPayment.

public: any code. private: only the same class (the default for members). protected: the class and classes that inherit from it. internal: any code in the same project (assembly).

A field is a variable inside a class. A property looks like a field from outside but runs code when read or written, so it can validate, calculate, or restrict access. Public data should be exposed through properties, not public fields.

{ get; } can be set only in the constructor. { get; private set; } can be changed by the class's own methods at any time. { get; init; } can be set in the constructor or an object initializer, and is fixed afterwards.

A rule that must always be true for an object. For a fee invoice: the status always matches the payments. The constructor checks the starting state and every method keeps the rule, so no sequence of calls can break it.

Instead of asking an object for its data and applying rules outside it, tell the object what to do and let it apply its own rules, such as invoice.RecordPayment(amount). This keeps each rule in one place.

Find every code path that writes the status, such as an admin screen or a direct SQL update. Make the status private set, changed only by the payment method, and restrict direct updates in the database. Correct existing invoices through proper adjustments that leave an audit trail.

No. Decide per property who legitimately changes it. Ids are set by data access code, simple descriptive fields may be freely editable, but values tied to rules, such as status or balance, should change only through methods.

It is deliberate defense in depth: C# gives fast, clear feedback, and the database protects the data if any path skips the application. Keep the rules consistent with shared error numbers and tests, and decide which layer is authoritative for each rule.

A class (the derived class) building on another (the base class), getting all its members and adding its own. Student : Person gets the names and FullName from Person and adds an admission number. Use it for true "is a" relationships.

: base(...) calls the base class's constructor with the given arguments, before the derived constructor's own code runs. It is required when the base class has no parameterless constructor.

Not for classes: a class has exactly one base class (error CS1721 otherwise). A class can implement many interfaces, which covers most needs for multiple "kinds" of behavior.

private members are visible only inside the class itself, even derived classes cannot use them. protected members are visible to the class and every class that inherits from it, but not to outside code.

is with a pattern (if (p is Student s)) checks and converts safely. as returns null when the conversion fails. A cast (Student)p throws InvalidCastException when the object is not a Student. Prefer the is pattern.

Because InvalidMarkException inherits from SchoolRuleException, so it is one. A catch block matches the exception's type or any base type, which lets callers handle a whole family of errors at once or a specific one separately.

A role that changes over time was modeled as a subclass, but an object cannot change its type. Replace the deep tree with one Teacher class plus data that records the role per year, such as a ClassTeachers table, and keep hierarchies shallow.

Inherit for true, permanent "is a" relationships, such as a guardian being a person. Use composition, holding another object, for "has a", such as an invoice having payments. Composition is more flexible, so prefer it when in doubt.

No. The class design shares code; the table design suits storage and queries. They may differ as long as the mapping is clear: each derived class maps to its own table, including the inherited columns. ORMs support several mapping strategies for hierarchies.

One call behaving differently depending on the object's real type. Calling Describe() on a Person variable runs the student, teacher, or guardian version, depending on which object it holds.

virtual in the base class allows derived classes to replace a method or property. override in the derived class provides the replacement. base.Method() inside an override runs the base version too.

By default it returns the class name, such as SchoolApp.Core.Entities.Student. Overriding it gives a readable description, which interpolation, logging, and the debugger then use. Never put private data such as passwords in it.

Overloading: same name, different parameters, usually in one class, chosen at compile time. Overriding: same signature, new body in a derived class, needs virtual and override, chosen at run time from the object's real type.

override replaces the base method for every caller. new hides it: callers using a base-type variable still get the base version, and only derived-type variables see the new one. Hiding is almost always a mistake; the compiler warns with CS0114 when neither keyword is used.

A non-virtual method defines a fixed process and calls virtual (or abstract) steps that derived classes fill in. FeeNotice.Send always formats the same way, while Channel and Compose differ per channel.

Create a base class or interface for payment modes with the varying behavior as virtual or abstract members, and one class per mode. Choose the class in one place, then call it through the base type. A new mode becomes one new class, with no edits to existing logic.

When only a value differs, such as a rate or a limit: use a property set in the constructor instead. Use subclasses for different behavior, and interfaces when behavior must be swapped or combined at run time.

New behavior is added as new classes behind an existing base type or interface, so tested code is not edited. Only the place that chooses the implementation changes. This keeps changes small, reviewable, and low-risk, and lets teams work on different implementations independently.

A class marked abstract that cannot be created with new, only inherited from. It can hold shared code and abstract members without a body, which every derived class must override, such as ReportCard.FormatScore.

A contract listing members a class promises to provide, such as INotificationSender with Channel, CanReach, and Send. It has no constructors or instance fields; a class implements it with : IName and must provide every member.

Yes, any number, while having only one base class. A Teacher inherits from Person and is also an ISoftDeletable; a class could implement several capability interfaces at once.

An abstract class can hold fields, constructors, and shared code, and a class can have only one; it models "is a" for related classes. An interface is a contract a class can implement alongside others; it models "can do". Use an abstract class for shared code, an interface for a capability with possibly unrelated implementations.

Since C# 8, an interface member can have a body that implementing classes inherit, such as Describe() in INotificationSender. They are reachable only through the interface type, and they let you add members to an interface without breaking existing implementations.

Depending on what something does, not how it does it, and hiding the details. Notify knows only that a sender can reach a guardian and send a message; email servers and phone gateways stay hidden in the implementations.

Introduce an interface such as INotificationSender, wrap the current provider in one class that implements it, and change the forty call sites to use the interface. Then the new provider is one new class plus one line of setup, and both can be tested with fakes.

No. Add an interface when you need to swap implementations, fake a dependency in tests, or define a contract between teams or layers. An interface with one implementation and no such need only adds files and indirection.

Adding an abstract member to either breaks every implementation. Adding a normal or virtual method to an abstract class, or a default method to an interface, does not. Design contracts small and stable, version them deliberately, and prefer adding new interfaces over changing widely used ones.

A static member belongs to the class and has one shared copy, called on the class name, such as SchoolRules.GetGrade(92m). An instance member belongs to each object and is called on an object, such as diya.GetAgeOn(date).

A class that holds only static members and can never be instantiated (error CS0712). It suits pure helpers and rules with no stored state, such as SchoolRules or Math.

No other class may inherit from it; trying fails with error CS0509. Seal concrete classes that were not designed for extension, such as Student and Teacher.

const is fixed at compile time and allowed only for simple types such as numbers and strings; other projects copy the value when they compile. static readonly is set once at run time and works for any type, such as DateOnly, and callers always read the current value.

Splitting one class across several files that the compiler joins. The main use is generated code: a tool such as EF Core scaffolding writes one file, and your own code in another file survives regeneration.

When it has dependencies or behavior you may want to replace, such as sending messages or reading the database. Static code cannot be swapped through an interface or faked in tests. Keep static for pure, deterministic rules.

A static field is shared by every request in the app and resets when the app restarts; with several copies of the API, each has its own counter. Concurrent requests and restarts produce duplicates. Move the counter to the database, such as a SQL SEQUENCE, protected by a unique constraint.

Many teams seal concrete classes by default and unseal deliberately. Sealing documents that a class was not designed for inheritance, prevents fragile overrides, and allows small performance gains. Leave classes open only when extension is part of the design.

Never in static fields or memory of one instance. Durable shared state belongs in the database (sequences, rows with constraints) or a shared store such as a distributed cache; per-request state lives in objects created for that request. Static members hold only constants and pure logic.

A type with a fixed list of named values, such as InvoiceStatus { Unpaid, PartiallyPaid, Paid, Cancelled }. It replaces magic strings: invalid values and typos fail the build, and editors list the valid options.

A type, written in one line such as record MarkEntry(int EnrollmentId, int ExamSubjectId, decimal? MarksObtained), with init-only properties, value equality, a readable ToString(), and with expressions for copies with changes.

status.ToString() returns the member name, such as "PartiallyPaid". Enum.Parse<InvoiceStatus>("Paid") converts text back, and Enum.TryParse does it safely, returning false for unknown text.

A struct is a value type: assignment copies the whole value, it cannot be null (unless nullable), and it cannot inherit. A class is a reference type: assignment copies a reference to one shared object. Use structs for small immutable values, classes for entities.

0, whether or not a member has that value. If no member is 0, an unset enum field holds an undefined value, as with Gender. Make 0 a safe default, or require the value in the constructor, and use Enum.IsDefined for values from outside.

It creates a copy of a record (or struct) with some properties changed, leaving the original untouched: entered with { MarksObtained = 90m }. It is how you "change" immutable data.

Replace status strings with an enum whose member names match the database CHECK constraint exactly, and convert with ToString() and Enum.Parse. Typos then fail the build, and a test can check that every enum name is allowed by the constraint.

Usually not. Entities have identity: two students with identical names and birth dates are still different people, distinguished by Id, and they change over time. Records compare by value and are meant to stay fixed, which suits DTOs, messages, and value objects.

Classes for entities with identity and behavior; records for data passed between layers and compared by value; readonly record structs for tiny, frequently created values; enums for fixed option lists. Document the convention so the whole team chooses the same way.

A class that represents one row of a database table, with one property per column, such as Mark for the Marks table. Data access code reads rows into entities and saves entities back as rows.

Introduced in C# 11, it forces the property to be set in the object initializer when the object is created; otherwise the build fails with CS9035. It suits entities without constructors, such as Subject.Name.

Add ? to the type: decimal? for MarksObtained, string? for Remarks, DateOnly? for a return date. NOT NULL columns use the plain type, so the class tells callers exactly what may be missing.

The foreign key property, such as StudentId, holds the stored Id and is always present. The navigation property, such as Student, holds the linked object and is filled only if the code loaded it, so it is nullable.

Put them in an abstract base class, such as AuditableEntity with CreatedAt, CreatedBy, UpdatedAt, and UpdatedBy, and a SoftDeletableEntity for soft-delete columns. Entities inherit what their table has.

As a class with the two foreign keys, which together form the key, plus any data about the link, such as Relationship and IsPrimaryContact, and optional navigation properties to both sides. There is no separate Id.

The entity property type: it is probably int, which drops the fraction. Change it to decimal, fix affected data, and add an automated test that compares every entity's property types and nullability with the database columns, so a mismatch fails the build.

A mix. Put rules inside entities where breaking them is expensive, such as payments and identity, so invalid objects cannot exist. Keep simple lookup tables plain, with rules in services. Fully anemic models scatter rules; fully rich models can fight data access tools.

For a database-first system with many tables, generate (scaffold) them so they always match the schema, and keep custom code in partial classes. Hand-write them when the domain model must differ from the tables. Either way, verify them automatically against the schema.

A data transfer object: a simple class or record that carries exactly the data one API call or screen needs, such as StudentDto. It has no business rules and no database-only fields.

Entities mirror the database and expose everything: password hashes, audit columns, soft-delete flags, and any column added later. DTOs send only what the client needs, so internal data cannot leak and database changes do not break clients.

A static method in a static class whose first parameter has this, such as ToDto(this Student student). It can be called like an instance method, student.ToDto(), without changing the original class. Its namespace must be imported with using.

Each direction needs different fields: a response has an Id and readable values; a create request has no Id; an update request contains only fields that may change. One shared DTO would accept or expose fields it should not.

A client adds extra fields to a request, such as a status or role, and the server saves them because it bound the request to an entity. Request DTOs contain only the allowed fields, so anything else in the JSON is ignored.

Building one simple DTO from values spread across linked entities, such as an invoice summary that includes the student's name from Enrollment.Student. Clients get a small, ready-to-show shape without knowing how tables are joined.

Introduce a DTO with only the needed fields and map the entity to it before returning. Check all other endpoints for returned entities, add a review rule or analyzer that forbids entities in controller responses, and consider rotating credentials if hashes were exposed.

Hand-written mapping is explicit, compiler-checked, and easy to debug; renamed properties break the build. Libraries reduce repetitive code for many similar mappings but can silently skip properties. Choose per project size, and test mappings either way.

The DTO is the public contract; the database and entities can change behind it while mapping adapts. Breaking changes to the DTO are made deliberately, with versioning, so apps already on phones keep working.

An array has a fixed size set when it is created. A List<T> grows and shrinks with Add and Remove, using an array inside that it replaces with a bigger one when full. Use arrays for fixed data and lists for data that changes.

When you look values up by a unique key, such as students by admission number. Lookup time stays about the same however many items it holds, unlike searching a list.

A Queue<T> is first in, first out: Enqueue adds at the back, Dequeue takes from the front, like a fee counter line. A Stack<T> is last in, first out: Push and Pop work on the top, like undo.

A list search checks items one by one, so on average half the list per search. A dictionary hashes the key to jump almost directly to its bucket, taking about constant time. In the SMS, finding 1,006 students took about 24 ms with a list and 0.1 ms with a dictionary.

The indexer throws KeyNotFoundException when the key is missing. TryGetValue returns false instead and gives the value through out when found. Use TryGetValue whenever the key may be absent.

The list was changed, for example by Remove, while a foreach was looping over it. Collect the items to change and apply the changes after the loop, or loop backwards with a for loop.

The nested search does students times marks comparisons. Group the marks into a Dictionary keyed by student once, then look each student up in constant time. The work drops from millions of comparisons to one pass plus one lookup per student.

Keep the real list private and expose it as IReadOnlyList<T> or IReadOnlyCollection<T>, so callers can read but not add or remove. Changes go through the class's own methods, which keep its rules. Initialise collections to empty, never null.

Start from how the data is used: in order and by position (List), by key (Dictionary), uniqueness checks (HashSet), arrival order (Queue), last-first (Stack), and whether many threads share it (concurrent collections). Measure with realistic data volumes before optimizing further.

Classes, records, interfaces, and methods written with a type parameter, such as T, that the caller fills in. List<Student> and PagedResult<StudentDto> reuse one definition for many types, with full compile-time type checking.

A method with its own type parameter, written after its name, such as ToPage<T>(List<T> all, int page, int pageSize). The compiler usually infers T from the arguments, so callers rarely write it.

ArrayList holds object, so wrong types are accepted and fail later with InvalidCastException, and value types are boxed. List<decimal> rejects a string at compile time and stores decimals directly, which is safer and faster.

Rules on what a type parameter may be, written with where: where T : ISoftDeletable, where T : class, where T : struct, where T : new(). They let the generic code use the promised members, and the compiler rejects types that do not qualify (CS0311).

Boxing wraps a value type, such as a decimal, in an object on the heap so it can be stored as object; unboxing casts it back. It costs memory and time. Generic collections store the value type directly, so no boxing happens.

Through a List<Person>, code could add a Teacher into a list of students, so C# forbids it (CS0029). IEnumerable<T> only reads, so treating students as persons is safe; this is covariance, marked by out T on the interface.

Define one generic PagedResult<T> in the shared library with agreed names, 1-based pages, and a maximum page size, and migrate endpoints to it gradually, keeping old shapes until clients move. Clients then need one paging routine.

Pros: less repetitive code and a uniform interface. Cons: every entity gets the same methods, including Delete where rules forbid it; special queries are awkward; and it often duplicates what an ORM already provides. Specific repositories, possibly sharing a small generic base, are a common choice.

Generalize proven, repeated shapes, such as paging, result wrappers, and soft-delete filters, with minimal constraints, in a shared library. Keep domain logic specific. Make something generic only when a second real use appears, and document the conventions so teams use them consistently.

The interface for anything you can walk through one item at a time. It provides an enumerator, which foreach uses. Arrays, lists, dictionaries, and LINQ queries all implement it.

It hands back one item from a method that returns IEnumerable<T>, then pauses the method until the caller asks for the next item. Data is streamed: reading the marks file with Take(3) read only 71 of 12,012 lines.

Building a query with methods like Where does not run it; it creates a plan. The plan runs only when results are requested, by foreach, Count(), First(), ToList(), and similar calls.

An IEnumerable query holds compiled code and runs in your application's memory. An IQueryable query holds an expression tree that a provider translates, for example EF Core into SQL, so filtering happens in the database before rows are sent.

Enumerating the same deferred query more than once repeats all its work each time; two Count() calls read the marks file twice. If the results are needed several times, call ToList() once and reuse the list.

Any() stops at the first matching item; Count() must examine every item. Against a database, Any() becomes an EXISTS check while Count() counts every matching row.

ToList() runs the query immediately, so every attendance row is loaded into memory before filtering. Apply Where while the query is still IQueryable, then ToList(), so SQL Server returns only today's rows.

Usually not. Returning IQueryable lets callers compose any query against the database, spreading data access logic and making performance unpredictable. Return materialized results such as IReadOnlyList<T>, and add specific methods for the queries you need.

Stream instead of materializing: read rows one at a time with an iterator, a data reader, or IAsyncEnumerable<T>, process each, and keep only aggregates. Filter and aggregate in the database where possible, and batch writes.

Language Integrated Query: a set of C# methods and syntax for querying any collection, such as Where, Select, OrderBy, and GroupBy. The same queries work on lists, files, and, through EF Core, databases.

Where filters: it keeps items that match a condition. Select projects: it turns each item into a new shape, such as a name and a mark. Filter first, then shape.

First returns the first match and throws if there is none. FirstOrDefault returns default (null for classes) instead of throwing. Single expects exactly one match and throws for none or for more than one.

For nullable values, Average, Max, and Min skip nulls, like SQL's AVG. The Class 10 Mathematics average of 60.23 ignored 20 absent students, matching SQL Server exactly.

GroupBy(m => m.Subject) returns groups; each has a Key (the subject) and is itself the sequence of items with that key, so g.Average(...) and g.Count() work per group, like SQL's GROUP BY.

Select returns one result per item, so a list of sections with enrollments becomes a list of lists. SelectMany flattens those inner lists into one sequence: 22 sections become 1,006 enrollments.

Several students share the top mark and the sort has no tie-breaker, so their order is not fixed. Add ThenBy on a stable key, and agree a ranking rule with the school, such as equal marks sharing a rank (DENSE_RANK).

Both compile to the same calls. Method syntax is shorter for simple chains and offers every operator; query syntax reads more easily for joins and multiple sources. Many teams default to method syntax and use query syntax for complex joins.

Through searches inside loops instead of dictionaries, repeated enumeration of deferred queries, materializing database queries too early, and sorting before filtering. Prevent it with code review rules, analyzers for multiple enumeration, logging of generated SQL, and measuring hot paths.

A type-safe reference to a method, stored in a variable and passed around like a value. Func, Action, and EventHandler<T> are built-in delegate types; LINQ's Where takes a delegate.

Func returns a value; its last type parameter is the return type, as in Func<decimal, string>. Action returns nothing, as in Action<string>.

An inline, unnamed method, such as m => m.MarksObtained is null. It is converted into a delegate (or an expression tree for IQueryable), with parameter types inferred from where it is used.

An event is a delegate wrapped so that outside code can only subscribe (+=) and unsubscribe (-=); only the owning class can raise it. A public delegate field could be overwritten or invoked by anyone, faking notifications (CS0079 prevents this for events).

A lambda that captures variables from its surrounding scope. It captures the variable itself, not a copy, so changes are shared and the captured objects stay alive as long as the lambda does.

With no subscribers the event is null, so ?. avoids a NullReferenceException. Each inline lambda creates a new delegate, so -= with another identical-looking lambda removes nothing; keep a reference or use a named method.

Have the payment raise one "payment recorded" event, or publish a message, after the payment is saved, and let each feature subscribe independently. Failures in optional reactions should not undo or block the payment; isolate them and log errors.

An event for optional listeners that come and go; an interface for a required collaborator with several methods, injected through the constructor; a Func parameter for a single rule or formula chosen by the caller.

Both announce that something happened and let others react. In-process events run synchronously in the same program and are lost if it crashes. Messaging (queues, an outbox) is durable, crosses services, retries on failure, and runs asynchronously, at the cost of eventual consistency.

C# 12 syntax for creating collections with square brackets: string[] subjects = ["English", "Science"];. It works for arrays, lists, and other collection types, and .. spreads another collection's items in.

Text between three double quotes ("""), where quotes and backslashes need no escaping and the closing quotes' indentation is removed. It suits SQL, JSON, and other text with quotes; values still belong in SQL parameters.

Inside a property accessor, field refers to the property's compiler-made backing field, so a property with a custom setter, such as trimming or validation, no longer needs a hand-written private field.

Patterns that match the shape of a sequence: ["Absent", ..] (first item is Absent), [.., "Absent", "Absent"] (last two), [_, _, _] (exactly three items). _ matches one item and .. any number of items.

Both declare parameters after the type name. A record turns them into public init-only properties with value equality. A class with a primary constructor only makes the parameters available inside the class body; they are not properties, and they are not read-only.

An extension(Type receiver) { ... } block inside a static class that can declare extension properties as well as methods, such as student.Initials. Before C# 14, only extension methods were possible.

Ask to split it: a large mechanical change hides real changes and is hard to review. Agree on which features the team adopts, apply them in new and changed code, and convert old code gradually or with automated, separately reviewed refactorings.

Without _, the compiler warns about missing statuses (CS8509) and unnamed values (CS8524), which helps when new statuses are added. A quiet default hides new statuses. A common choice is to list every status and end with _ => throw, so unexpected values fail loudly.

Upgrade to each LTS release on a plan, update the target framework and packages, fix analyzer warnings, and adopt new language features through agreed coding standards in new and changed code. Automated tests make upgrades safe, and editorconfig or analyzers keep the style consistent.

async marks a method that can use await and returns Task or Task<T>. await pauses the method until a task completes, giving the thread back in the meantime, and continues from the same line afterwards.

Both represent work that finishes later. Task has no result, like writing a log file; Task<T> produces a value of type T, like Task<string> for a send status.

Not by itself. It makes waiting for I/O cheaper by freeing threads, which improves scalability and lets independent waits overlap. CPU-bound calculations need parallelism instead.

Start them without awaiting, collect the tasks, and await Task.WhenAll(tasks). Ten 250 ms sends took about 260 ms together instead of 2.6 seconds one by one. Results come back in the same order.

A token that signals that work should stop, created by a CancellationTokenSource, for example with a time limit. Passing it down to every async call lets the whole operation stop early; ASP.NET Core cancels it when the client disconnects.

An async void method cannot be awaited, so callers cannot know when it ends, and exceptions it throws cannot be caught by the caller and can crash the process. Return Task instead; use async void only for event handlers.

Thread pool starvation: each request blocks a thread while waiting for the database, so the pool runs out and new requests queue. Make the call chain async all the way with await, so threads are released during waits.

WhenAll waits for all tasks to finish, then await rethrows the first exception; the other tasks still complete. Inspect each task's status or the aggregate Exception to see all failures, and design batches so one failure does not stop the rest.

Send asynchronously with limited concurrency (for example batches or a semaphore) so the provider is not overwhelmed, with timeouts and cancellation, retries for temporary failures, and a record of what was sent so a restart does not send twice. Run it as a background job, not inside a web request.

A thread is a path of execution managed by the operating system. A task is a unit of work that .NET schedules, usually on a reusable thread-pool thread. Modern code uses tasks, Parallel, and async instead of creating threads by hand.

A bug where the result depends on the timing of threads that share data. Two threads doing count++ can both read the same value and write the same result, losing an update; one million increments gave about 62,000.

It processes the items of a collection on several thread-pool threads at once, using multiple cores. It suits CPU-bound, independent work, such as building 1,006 report cards about three times faster on 16 cores.

Interlocked performs a single update on one number, such as increment or add for int and long, as one uninterruptible step, without blocking. Use lock when several fields change together, for types like decimal, or for collections.

Splitting and combining work costs time, cores share memory bandwidth, locks make threads wait, and parts of the program run on one thread only (Amdahl's law). Very small work items can even be slower in parallel.

No: adding from several threads at once can lose items or corrupt it. Use ConcurrentDictionary (or other System.Collections.Concurrent types), or let each thread build its own results and combine them afterwards.

Threads add to one shared total without protection, so updates are lost in a race. Fix it by computing independent partial totals per section or thread and adding them at the end, or by protecting the shared total with a lock. Then test repeatedly with production-sized data.

Two threads each hold one lock and wait for the lock the other holds, so both wait forever. Avoid it by taking locks in a fixed order, keeping locked sections small, avoiding nested locks, and never awaiting or calling unknown code while holding a lock.

Keep request handling stateless: per-request objects, no mutable static fields, data in the database with transactions and constraints. Where in-memory sharing is needed, such as caches, use thread-safe types and immutable data, and keep heavy CPU work in background jobs.

The stack holds method-local value-type variables and references, freed automatically when a method returns. The heap holds objects such as class instances, arrays, and strings, freed by the garbage collector when nothing references them.

It periodically finds objects that running code can no longer reach and reclaims their memory, then compacts the surviving objects. Developers do not free managed memory by hand.

IDisposable has one method, Dispose, which releases resources the GC does not manage, such as files and connections. using calls Dispose automatically at the end of a block, even if an exception is thrown.

Generation 0 holds new objects and is collected often; survivors move to generation 1, then to generation 2, which is collected rarely. Most objects die young, so this makes collection cheap. Objects of 85,000 bytes or more start on the large object heap, counted as generation 2.

Dispose releases resources at a known moment, called by your code or using. A finalizer runs when the GC collects the object, at an unknown time, on a special thread, and delays collection. Prefer Dispose; modern code rarely writes finalizers because SafeHandle covers them.

An event holds delegates, and each delegate references its subscriber. If a long-lived object's event keeps a short-lived subscriber, the subscriber cannot be collected until it unsubscribes. Unsubscribe with -=, often in the subscriber's Dispose.

Watch memory and GC counters over time, take memory snapshots at two points, and compare which object types grow and what keeps them alive. Typical culprits are event subscriptions on long-lived services, static collections, unbounded caches, and undisposed resources.

Almost never. The GC tunes its own timing, and forcing collections usually costs performance by promoting objects and pausing the program. It is useful in experiments and tests that measure memory, as in this lesson.

Stream large data instead of loading it, avoid many short-lived large objects, reuse buffers (pooling), bound every cache, dispose resources promptly, avoid long-lived references to per-request objects, and monitor GC metrics in production.

ADO.NET is the low-level part of .NET for talking to databases. For SQL Server, the main classes come from Microsoft.Data.SqlClient: SqlConnection (the connection), SqlCommand (SQL text or a procedure name), SqlParameter (a value), SqlDataReader (rows, forward only), SqlTransaction, and SqlException. Dapper and EF Core are built on top of it.

ExecuteScalar returns one value, the first column of the first row, such as a COUNT(*). ExecuteReader returns a reader over many rows. ExecuteNonQuery runs INSERT, UPDATE, or DELETE and returns the number of rows changed. Each has an Async version that should be preferred.

SQL injection is user input changing what a query does, because the input was joined into the SQL text. Typing x' OR '1'='1 into an unsafe student search returned all 10,012 students. Prevent it with parameters: the value travels separately from the SQL, so it is only ever a value.

ADO.NET keeps a pool of open connections per connection string. Disposing a connection returns it to the pool, and the next open takes it back almost instantly: 100 opens took 3 ms after a first open of 472 ms. So open as late as possible, dispose as early as possible, and never keep a connection in a field.

A NULL column arrives as DBNull.Value, not null, and calling GetString or GetInt32 on it throws. Check reader.IsDBNull(ordinal) first, then read the value, for example Address = reader.IsDBNull(i) ? null : reader.GetString(i).

Set CommandType = CommandType.StoredProcedure and use the procedure name as the command text. Add input parameters normally, and add the output parameter with Direction = ParameterDirection.Output and a size. After ExecuteNonQueryAsync, read its Value, as with @ReceiptNumber from usp_RecordFeePayment.

AddWithValue guesses the SQL type from the C# value, and a string becomes NVARCHAR. Against a VARCHAR column, SQL Server converts the column and can scan instead of seek: finding a student by admission number read 37 pages instead of 2. Use Parameters.Add with an explicit SqlDbType and size that match the column.

The pool is exhausted: connections are being opened and not returned. Look for connections, readers, or commands that are not disposed, especially on exception paths, and for connections held during slow work. Fix with await using everywhere, short-lived connections, and async calls; only then consider raising Max Pool Size.

When you need full control or maximum throughput with no mapping layer: bulk loading with SqlBulkCopy, streaming huge result sets, table-valued parameters, or a small hot path measured as too slow. For ordinary CRUD, the hand-written mapping costs more in time and bugs than it saves, so most teams use EF Core or Dapper and drop to ADO.NET only where measured.

Dapper is a micro-ORM: you write the SQL, and it adds parameters and maps result rows to objects. EF Core is a full ORM: it also writes the SQL from LINQ, tracks changes to entities, and saves them. Dapper gives more control with less magic; EF Core gives more productivity for everyday CRUD.

QueryAsync<T> for a list, QuerySingleOrDefaultAsync<T> for one row by a unique key (null if none, an exception if two), and ExecuteScalarAsync<int> for a count. ExecuteAsync runs INSERT, UPDATE, DELETE, or a procedure and returns the rows changed.

Pass an object, often anonymous: new { LastName = lastName } fills @LastName. Dapper creates real parameters, so the values never become part of the SQL text and injection is not possible. Joining user input into the SQL string yourself is still unsafe.

By name, ignoring case. A column with no matching property is ignored, and a property with no column keeps its default value; neither raises an error. That makes renamed columns or missing aliases a silent bug, so use explicit column lists with aliases and test each query.

Multi-mapping reads one joined row into several objects, for example an Enrollment and its Student, and a function you supply connects them. splitOn names the column where each next object starts; it defaults to Id. For one-to-many results, a dictionary keeps one parent per key.

Use DynamicParameters: add the inputs, and add the output with a type, a size, and direction: ParameterDirection.Output. Call ExecuteAsync with commandType: CommandType.StoredProcedure, then read it with Get<string>("@ReceiptNumber").

Dapper sends C# strings as NVARCHAR(4000). Against the VARCHAR column, SQL Server converts the column and may scan the index instead of seeking. Pass new DbString { Value = ..., IsAnsi = true, Length = 15 } so the parameter matches the column type, then confirm the seek in the plan.

Send them together with QueryMultipleAsync and read each result in order from the grid reader: one round trip instead of six. Select only the columns the dashboard shows, mapped to DTOs or tuples. If it is still slow, look at the queries' plans and consider caching (Module 12).

Yes, commonly: EF Core for writes and ordinary reads of entities, where change tracking and LINQ save time, and Dapper for heavy reports and dashboards, where hand-tuned SQL matters. Both use the same connection string and can share a transaction. Keep each behind a repository or query interface so callers do not know which tool answers.

EF Core is Microsoft's ORM for .NET: you query with LINQ, and it writes the SQL, creates objects, tracks changes, and saves them. A DbContext is one session with the database; each DbSet<T> is a table you can query. A context should be short-lived, such as one per web request.

Database-first starts from an existing database: schema changes are made in SQL, and EF Core maps to the result, often helped by scaffolding. Code-first starts from C# classes, and EF Core migrations create and change the database. Database-first fits shared or older databases; code-first fits new apps that own their database.

It looks at every tracked entity, finds the added, modified, and deleted ones, and sends the matching INSERT, UPDATE, and DELETE statements in one transaction. Changing one property of a loaded student produced an UPDATE of that single column.

It reads a database and writes one class per table and view, plus a DbContext with the full mapping: lengths, types, keys, indexes, defaults, and relationships. Without --no-onconfiguring, it also puts the connection string in the generated code, which leaks credentials into source control.