SQL Server 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.