Part 4: SQL Interview Notes

Detailed, practical preparation notes for C#/.NET backend interviews

Covers questions 61-85 from your SQL section: joins, grouping, CTE, window functions,
stored procedures, functions, triggers, indexes, normalization, transactions, deadlocks,
execution plan, and ACID.

How to use this PDF

- Read the direct answer first and practice speaking it naturally.
- Use key points for quick revision before interview.
- Use SQL examples only to understand the concept; do not memorize blindly.
- For every topic, connect your answer with real project usage like reports, APIs, dashboards, billing, orders, or inventory.

Interview strategy for SQL

For SQL interviews, do not answer only definitions. Interviewers usually check whether you understand data
relationships, query behavior, performance impact, and real production usage. Always mention joins, indexes,
transactions, and execution plan when relevant.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 1

 Quick SQL Revision Map
Topic What to remember

Joins Combine related tables - customer/order/report screens

GROUP BY / HAVING Create summaries and filter grouped data

CTE Make complex queries readable

Window Functions Ranking, running totals, previous/next row comparison

Stored Procedure Reusable multi-step DB operation

Index Improve query search/join/sort performance

Execution Plan Understand how database executes query

Transaction / ACID Keep critical operations reliable

61. What is SQL?

Direct interview answer
SQL is the language we use to store, read, filter, join, update, and analyze data in relational databases. In a .NET project,
most business data like users, orders, payments, invoices, and reports usually sits in SQL Server, PostgreSQL, or
MySQL, and the application talks to it through SQL queries, EF, or stored procedures.

Key points to remember

- Structured Query Language
- Works with relational tables
- CRUD: SELECT, INSERT, UPDATE, DELETE
- Used for filtering, joining, grouping, reporting
- Important for backend performance

Real project usage

In an [Link] application, the API may receive a request like "show active customers with last order date". SQL is used
behind the scenes to fetch the exact data efficiently from customer and order tables.

Example SQL

SELECT CustomerId, Name, Email

FROM Customers
WHERE IsActive = 1
ORDER BY Name;

Common follow-up questions

- What is DDL, DML, DCL, TCL?
- How is SQL different from NoSQL?
- How do you improve a slow SQL query?

Common mistakes to avoid

- Saying SQL is only for SELECT queries
- Not knowing joins and indexes
- Writing SELECT * everywhere in production code
SQL is not just a database language. For backend developers, SQL is needed to fetch correct data, build reports,
debug production issues, and improve performance.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 2

 62. What are joins?
Direct interview answer
Joins are used to combine rows from two or more tables based on a related column. In real projects, data is normalized,
so customer data, order data, and payment data are stored separately. Joins help us bring them together when we need
a meaningful result.

Key points to remember

- Combines related tables
- Usually uses primary key and foreign key
- Common types: INNER, LEFT, RIGHT, FULL, CROSS, SELF
- Very common in reports and dashboards
- Wrong join can duplicate or miss data

Real project usage

For an order list screen, we may need order number from Orders, customer name from Customers, and payment status
from Payments. We join these tables instead of storing everything in one table.

Example SQL

SELECT [Link], [Link] AS CustomerName, [Link]

FROM Orders o
JOIN Customers c ON [Link] = [Link];

Common follow-up questions

- What is inner join?
- What is left join?
- How do joins affect performance?

Common mistakes to avoid

- Joining without ON condition
- Using wrong key column
- Not checking one-to-many duplication
Joins are used whenever the required data is spread across multiple tables. A good developer should know which
join to use and how it affects the final result.

63. Inner join vs left join

Direct interview answer
INNER JOIN returns only matching records from both tables. LEFT JOIN returns all records from the left table and
matching records from the right table. If there is no match, right-side columns will be NULL. In interviews, explain this with
a customer-order example.

Key points to remember

- INNER JOIN = only matching data
- LEFT JOIN = all left-side data
- LEFT JOIN is useful for missing data checks
- NULL appears when right table has no match
- Common in reports

Real project usage

If we need only customers who placed orders, use INNER JOIN. If we need all customers, including customers who have
not placed any order, use LEFT JOIN.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 3

 Key differences / quick comparison

INNER JOIN LEFT JOIN

Returns only matching rows from both tables Returns all rows from left table

Non-matching rows are removed Non-matching right-side data becomes NULL

Useful for confirmed relationships Useful for optional relationships

Example: customers with orders Example: all customers with order details if available

Example SQL

-- Customers who placed orders

SELECT [Link], [Link]
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link];

-- All customers, even without orders

SELECT [Link], [Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link];

Common follow-up questions

- How do you find customers with no orders?
- What happens when right table has multiple matching rows?

Common mistakes to avoid

- Saying left join returns all rows from both tables
- Forgetting NULL handling after left join
I use INNER JOIN when both table data must exist. I use LEFT JOIN when the main table data should come even if
related data is missing.

64. Self join

Direct interview answer
A self join means joining a table with itself. We use it when rows in the same table are related to other rows in that same
table. A common example is an Employees table where each employee has a ManagerId pointing to another employee.

Key points to remember

- Same table joined twice
- Uses table aliases
- Useful for hierarchy data
- Employee-manager example
- Can use INNER or LEFT join

Real project usage

In an HR system, both employee and manager are stored in the Employees table. To show employee name with
manager name, we join Employees with Employees.

Example SQL

SELECT [Link] AS EmployeeName, [Link] AS ManagerName

FROM Employees e
LEFT JOIN Employees m ON [Link] = [Link];

Common follow-up questions

- Why do we need aliases in self join?
- Can a self join be used for category-parent category?

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 4

 Common mistakes to avoid
- Not using aliases clearly
- Using INNER JOIN and losing top-level employees who have no manager
Self join is useful when a table has a relationship with itself, especially for hierarchy-like data such as employees
and managers or categories and parent categories.

65. Cross join

Direct interview answer
CROSS JOIN returns every possible combination between two tables. If table A has 3 rows and table B has 4 rows, the
result will have 12 rows. It is not used frequently in normal CRUD, but it is useful for combinations and test data
scenarios.

Key points to remember

- Cartesian product
- No ON condition needed
- Rows multiply
- Used carefully
- Can create very large results

Real project usage

If we have Sizes and Colors tables, CROSS JOIN can generate all possible product variants like Small-Red, Small-Blue,
Medium-Red, and so on.

Example SQL

SELECT [Link], [Link]

FROM Sizes s
CROSS JOIN Colors c;

Common follow-up questions

- Why can cross join be dangerous?
- How many rows are returned if table A has 10 and table B has 20 rows?

Common mistakes to avoid

- Using CROSS JOIN accidentally by missing JOIN condition
- Running it on large tables without understanding row multiplication
Cross join is used when we intentionally need all combinations. In normal business queries, we should be careful
because it can create huge result sets.

66. What is GROUP BY?

Direct interview answer
GROUP BY is used to combine rows into groups and calculate aggregate values like count, sum, average, min, or max. It
is very common in dashboards, reports, and summary screens.

Key points to remember

- Groups rows by one or more columns
- Used with aggregate functions
- COUNT, SUM, AVG, MIN, MAX
- SELECT columns should be grouped or aggregated
- Useful for reports

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 5

 Real project usage
In an order dashboard, we may need total sales per customer, order count per month, or total revenue per product
category. GROUP BY helps build these summaries.

Example SQL

SELECT CustomerId, COUNT(*) AS TotalOrders, SUM(TotalAmount) AS TotalSales

FROM Orders
GROUP BY CustomerId;

Common follow-up questions

- Can we use WHERE with GROUP BY?
- What is HAVING?
- Why do we get error when selecting non-grouped columns?

Common mistakes to avoid

- Selecting columns that are not part of GROUP BY or aggregate
- Using GROUP BY when DISTINCT is enough
GROUP BY is used to create summary data from detailed rows. It is one of the most important SQL concepts for
reporting and analytics.

67. HAVING vs WHERE

Direct interview answer
WHERE filters rows before grouping. HAVING filters groups after GROUP BY. Simple rule: use WHERE for normal
columns, and HAVING for aggregate conditions like COUNT, SUM, or AVG.

Key points to remember

- WHERE runs before GROUP BY
- HAVING runs after GROUP BY
- WHERE filters individual rows
- HAVING filters aggregated groups
- HAVING is used with COUNT/SUM/AVG conditions

Real project usage

If we want orders from 2026 only, use WHERE. If we want only customers whose total sales are more than 1 lakh, use
HAVING.

Key differences / quick comparison

WHERE HAVING

Filters raw rows Filters grouped result

Used before GROUP BY Used after GROUP BY

Works on normal columns Works on aggregate values

Example: WHERE IsActive = 1 Example: HAVING COUNT(*) > 5

Example SQL

SELECT CustomerId, COUNT(*) AS TotalOrders

FROM Orders
WHERE OrderStatus = 'Completed'
GROUP BY CustomerId
HAVING COUNT(*) > 5;

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 6

 Common follow-up questions
- Can HAVING be used without GROUP BY?
- Which one improves performance more?

Common mistakes to avoid

- Using HAVING for simple row filtering
- Trying to use aggregate function directly in WHERE
I use WHERE to reduce raw data first, then GROUP BY for summary, and HAVING to filter the final summary
result.

68. What is CTE?

Direct interview answer
CTE means Common Table Expression. It is a temporary named result set used inside a single query. It makes complex
queries easier to read, especially when we need step-by-step filtering, ranking, or recursion.

Key points to remember

- CTE = Common Table Expression
- Temporary result for one query
- Improves readability
- Useful with window functions
- Supports recursive queries

Real project usage

If a query first calculates customer sales and then filters top customers, a CTE makes it cleaner than writing a deeply
nested subquery.

Example SQL

WITH CustomerSales AS (
SELECT CustomerId, SUM(TotalAmount) AS TotalSales
FROM Orders
GROUP BY CustomerId
)
SELECT *
FROM CustomerSales
WHERE TotalSales > 100000;

Common follow-up questions

- CTE vs temp table?
- Can CTE improve performance?
- What is recursive CTE?

Common mistakes to avoid

- Thinking CTE always stores data physically
- Using too many CTEs without checking execution plan
CTE is mainly used to make complex SQL more readable and maintainable. It is very useful for reporting queries
and step-by-step transformations.

69. What is window function?

Direct interview answer
A window function calculates a value across a set of related rows without collapsing the rows like GROUP BY. This
means we can show row-level data and summary/ranking data in the same result.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 7

 Key points to remember
- Works over a window of rows
- Uses OVER()
- Does not reduce row count
- Useful for ranking, running totals, comparisons
- Common functions: ROW_NUMBER, RANK, SUM OVER, LEAD, LAG

Real project usage

In a sales report, we can show each order and also the customer-wise total sales or rank of each order without grouping
away the order rows.

Example SQL

SELECT OrderId, CustomerId, TotalAmount,

SUM(TotalAmount) OVER(PARTITION BY CustomerId) AS CustomerTotal
FROM Orders;

Common follow-up questions

- Window function vs GROUP BY?
- What is PARTITION BY?
- What is ORDER BY inside OVER()?

Common mistakes to avoid

- Confusing PARTITION BY with GROUP BY
- Forgetting ORDER BY when ranking matters
Window functions are powerful for reports because they allow ranking, totals, and comparisons while keeping
detailed rows visible.

70. ROW_NUMBER vs RANK vs DENSE_RANK

Direct interview answer
These are ranking window functions. ROW_NUMBER gives a unique sequence number. RANK gives the same rank for
ties but skips the next rank. DENSE_RANK gives the same rank for ties but does not skip numbers.

Key points to remember

- ROW_NUMBER = unique number for every row
- RANK = same rank for ties, skips rank
- DENSE_RANK = same rank for ties, no skip
- Requires ORDER BY
- Useful for top-N queries

Real project usage

For a leaderboard, if two users have the same score, RANK may show both as rank 1 and next as rank 3. DENSE_RANK
will show next as rank 2. ROW_NUMBER will still force 1 and 2.

Key differences / quick comparison

Function Behavior

ROW_NUMBER Always unique: 1, 2, 3, 4

RANK Ties share rank and skip next: 1, 1, 3

DENSE_RANK Ties share rank without skip: 1, 1, 2

Example SQL

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 8

 SELECT Name, Score,
ROW_NUMBER() OVER(ORDER BY Score DESC) AS RowNo,
RANK() OVER(ORDER BY Score DESC) AS RankNo,
DENSE_RANK() OVER(ORDER BY Score DESC) AS DenseRankNo
FROM CandidateScores;

Common follow-up questions

- Which one is best for pagination?
- Which one is best for leaderboard?
- How do you get top 3 per department?

Common mistakes to avoid

- Using ROW_NUMBER for ranking when ties matter
- Not specifying deterministic ORDER BY
I choose ROW_NUMBER when every row needs a unique sequence. I choose RANK or DENSE_RANK when equal
values should share the same rank.

71. LEAD and LAG

Direct interview answer
LEAD and LAG are window functions used to compare the current row with the next or previous row. LAG reads a
previous row value. LEAD reads a future row value.

Key points to remember

- LAG = previous row value
- LEAD = next row value
- Uses OVER with ORDER BY
- Good for trend comparison
- Useful in reports and audits

Real project usage

In a monthly sales report, LAG can compare this month sales with previous month sales. In audit logs, LEAD can
compare current status with next status.

Example SQL

SELECT MonthName, SalesAmount,

LAG(SalesAmount) OVER(ORDER BY MonthNumber) AS PreviousMonthSales,
SalesAmount - LAG(SalesAmount) OVER(ORDER BY MonthNumber) AS Difference
FROM MonthlySales;

Common follow-up questions

- What happens for the first row with LAG?
- Can we provide default value?
- Why is ORDER BY important?

Common mistakes to avoid

- Using LEAD/LAG without correct ordering
- Not handling NULL for first or last row
LEAD and LAG are useful when we need row-to-row comparison, like previous month sales, next status, or
previous login time.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 9

 72. What is stored procedure?
Direct interview answer
A stored procedure is a saved SQL program inside the database. It can contain multiple SQL statements, parameters,
validations, transactions, and business logic. We call it from the application when we want the database to execute a
predefined operation.

Key points to remember

- Precompiled/saved database routine
- Can accept input parameters
- Can return result sets or output values
- Good for complex DB operations
- Can improve security and consistency

Real project usage

In a billing system, creating an invoice may require inserting invoice header, invoice items, stock update, and payment
entry. A stored procedure can handle all these steps inside one transaction.

Example SQL

CREATE PROCEDURE GetCustomerOrders

@CustomerId INT
AS
BEGIN
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;
END;

Common follow-up questions

- Stored procedure vs function?
- When do you prefer stored procedure over EF?
- Can stored procedures improve performance?

Common mistakes to avoid

- Putting all business logic blindly in stored procedures
- Not using parameters and creating SQL injection risk
Stored procedures are useful for complex, reusable, and transaction-heavy database operations. But I avoid
overusing them for simple CRUD if ORM can handle it cleanly.

73. Function vs stored procedure

Direct interview answer
A function is mainly used to calculate and return a value or table. A stored procedure is used to perform an operation and
can include multiple actions. In simple words, function is more for reusable calculation, procedure is more for process
execution.

Key points to remember

- Function must return a value/table
- Procedure may or may not return data
- Function can often be used inside SELECT
- Procedure is executed using EXEC
- Procedure is better for multi-step operations

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 10

 Real project usage
Use a function to calculate GST amount or full name. Use a stored procedure to create invoice, update stock, and insert
payment records together.

Key differences / quick comparison

Function Stored Procedure

Returns value or table May return result set/output, or just perform action

Used inside SELECT in many cases Called using EXEC

Best for calculations Best for business operations

Usually limited side effects Can perform inserts, updates, deletes

Example SQL

-- Function style usage

SELECT [Link](Amount) AS GSTAmount
FROM InvoiceItems;

-- Stored procedure style usage

EXEC CreateInvoice @CustomerId = 10;

Common follow-up questions

- Can functions modify data?
- Can stored procedure return multiple result sets?
- Which one is better for performance?

Common mistakes to avoid

- Using function for large row-by-row calculations without performance check
- Using procedure when a simple query is enough
I use functions for reusable calculations and procedures for complete database workflows that may involve
multiple steps or transactions.

74. Types of functions

Direct interview answer
In SQL, functions are commonly scalar functions, inline table-valued functions, and multi-statement table-valued
functions. Scalar returns one value. Table-valued functions return a table result.

Key points to remember

- Scalar function returns single value
- Inline table-valued function returns table using one SELECT
- Multi-statement table-valued function builds table step by step
- Built-in functions also exist
- Use carefully for performance

Real project usage

A scalar function can format a full name. A table-valued function can return active orders for a customer and can be
joined with other queries.

Key differences / quick comparison

Type Use

Scalar function Returns one value like tax, full name, age

Inline table-valued function Returns table from a single SELECT

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 11

 Type Use

Multi-statement table-valued function Returns table after multiple steps

Built-in function GETDATE, ISNULL, COALESCE, COUNT, SUM etc.

Example SQL

CREATE FUNCTION GetFullName(@FirstName VARCHAR(50), @LastName VARCHAR(50))

RETURNS VARCHAR(120)
AS
BEGIN
RETURN CONCAT(@FirstName, ' ', @LastName);
END;

Common follow-up questions

- Scalar function performance issue?
- Inline TVF vs view?
- What are built-in functions?

Common mistakes to avoid

- Calling scalar functions on millions of rows without checking performance
- Using function when a computed column or query expression is better
Functions are useful for reusable calculations and reusable table results, but in performance-critical queries we
should test their impact.

75. What is trigger?

Direct interview answer
A trigger is database logic that automatically executes when an event happens on a table, like INSERT, UPDATE, or
DELETE. It is commonly used for audit logging, validation, or maintaining related data, but it should be used carefully
because it runs implicitly.

Key points to remember

- Automatically fires on table event
- Events: INSERT, UPDATE, DELETE
- Useful for audit logs
- Can enforce DB-level rules
- Can make debugging harder if overused

Real project usage

When employee salary is updated, a trigger can insert old salary, new salary, changed date, and changed by into an audit
table.

Example SQL

CREATE TRIGGER trg_AuditCustomerUpdate

ON Customers
AFTER UPDATE
AS
BEGIN
INSERT INTO CustomerAudit(CustomerId, ChangedAt)
SELECT CustomerId, GETDATE()
FROM inserted;
END;

Common follow-up questions

- AFTER trigger vs INSTEAD OF trigger?
- What are inserted and deleted tables?

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 12

 - Why can triggers cause performance issues?

Common mistakes to avoid

- Putting heavy business logic inside triggers
- Forgetting triggers run for multiple rows, not just one row
Triggers are useful for automatic database-level actions like audit logging, but I use them carefully because
hidden logic can affect performance and debugging.

76. Types of triggers

Direct interview answer
The common trigger types are AFTER triggers and INSTEAD OF triggers. AFTER trigger runs after the data operation
completes. INSTEAD OF trigger replaces the original operation, commonly used with views or special validation
scenarios.

Key points to remember

- AFTER trigger runs after INSERT/UPDATE/DELETE
- INSTEAD OF trigger runs instead of operation
- DDL triggers can track schema changes
- Logon triggers exist in SQL Server
- Most common use: DML triggers

Real project usage

AFTER UPDATE can create audit records. INSTEAD OF INSERT can control how insert happens through a view.

Key differences / quick comparison

Trigger Type When used

AFTER INSERT/UPDATE/DELETE Audit logging or post-action validation

INSTEAD OF trigger Custom behavior instead of normal insert/update/delete

DDL trigger Track schema changes like CREATE/ALTER/DROP

Logon trigger Control or audit login events

Example SQL

-- Conceptual example
CREATE TRIGGER trg_AfterOrderInsert
ON Orders
AFTER INSERT
AS
BEGIN
INSERT INTO OrderAudit(OrderId, CreatedAt)
SELECT OrderId, GETDATE() FROM inserted;
END;

Common follow-up questions

- What is the difference between inserted and deleted?
- Can a trigger rollback a transaction?
- Can trigger fire recursively?

Common mistakes to avoid

- Assuming trigger executes once per row
- Not considering transaction rollback impact
DML triggers are the most commonly used triggers in application databases. I mainly use them for audit and
DB-level rules, not for every business workflow.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 13

 77. What is index?
Direct interview answer
An index is a database structure that helps SQL find rows faster, similar to an index in a book. Without a useful index, the
database may scan many rows. With a proper index, it can directly locate matching rows.

Key points to remember

- Improves read/search performance
- Created on one or more columns
- Helps WHERE, JOIN, ORDER BY
- Too many indexes slow writes
- Needs maintenance and proper design

Real project usage

If Orders table has millions of rows and we frequently search by CustomerId, an index on CustomerId helps the database
find orders for one customer faster.

Example SQL

CREATE INDEX IX_Orders_CustomerId

ON Orders(CustomerId);

SELECT *
FROM Orders
WHERE CustomerId = 101;

Common follow-up questions

- Clustered vs non-clustered index?
- When index is not used?
- What is covering index?

Common mistakes to avoid

- Creating indexes on every column
- Ignoring write overhead
- Not checking execution plan
Indexes are one of the main tools for SQL performance tuning. The goal is to support frequent search, join, and
sort patterns without over-indexing.

78. Clustered vs non-clustered index

Direct interview answer
A clustered index decides the physical/logical order of data in the table. A table can have only one clustered index. A
non-clustered index is a separate structure that points to the actual data. A table can have multiple non-clustered indexes.

Key points to remember

- Clustered index controls data order
- Only one clustered index per table
- Non-clustered index is separate lookup structure
- Multiple non-clustered indexes allowed
- Primary key often creates clustered index by default in SQL Server

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 14

 Real project usage
For Orders, a clustered index may be on OrderId. Non-clustered indexes may be on CustomerId, OrderDate, or Status
depending on query patterns.

Key differences / quick comparison

Clustered Index Non-Clustered Index

Defines table data order Separate structure from table data

Only one per table Many can exist

Good for range queries on ordered key Good for search columns

Leaf level contains actual data Leaf level contains key pointer/row locator

Example SQL

CREATE CLUSTERED INDEX CX_Orders_OrderId

ON Orders(OrderId);

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId

ON Orders(CustomerId);

Common follow-up questions

- Can primary key be non-clustered?
- What is included column?
- What is index seek vs scan?

Common mistakes to avoid

- Saying clustered index means unique always
- Creating clustered index on frequently changing wide column
Clustered index is about how table data is organized. Non-clustered index is an additional fast lookup path for
common queries.

79. What is normalization?

Direct interview answer
Normalization is the process of designing tables to reduce duplicate data and improve data consistency. We split data
into related tables and connect them using keys.

Key points to remember

- Reduces duplicate data
- Improves consistency
- Uses primary key and foreign key
- Common forms: 1NF, 2NF, 3NF
- Good for transactional systems

Real project usage

Instead of storing customer name and address in every order row, we store customer details once in Customers table and
store CustomerId in Orders table.

Example SQL

-- Normalized structure
Customers(CustomerId, Name, Phone)
Orders(OrderId, CustomerId, OrderDate, TotalAmount)

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 15

 Common follow-up questions
- What is 1NF, 2NF, 3NF?
- Normalization vs denormalization?
- Can too much normalization affect performance?

Common mistakes to avoid

- Thinking normalization means always creating many tables
- Ignoring reporting performance needs
Normalization gives clean and consistent transactional data. It is important for systems like billing, inventory, HR,
and banking where data correctness matters.

80. What is denormalization?

Direct interview answer
Denormalization means intentionally keeping duplicate or precomputed data to improve read performance. It is usually
used in reporting, dashboards, analytics, or high-read systems where joins become expensive.

Key points to remember

- Adds controlled redundancy
- Improves read speed
- Reduces joins
- Used in reporting and analytics
- Needs sync strategy to avoid inconsistency

Real project usage

A dashboard table may store CustomerName and TotalSales together, even though CustomerName already exists in
Customers table. This makes dashboard reads faster.

Key differences / quick comparison

Normalization Denormalization

Reduces duplication Adds controlled duplication

Better consistency Better read performance

More joins may be needed Fewer joins needed

Best for transactional systems Best for reporting/read-heavy systems

Example SQL

-- Denormalized reporting table example

CustomerSalesReport(CustomerId, CustomerName, TotalOrders, TotalSales, LastOrderDate)

Common follow-up questions

- When do you denormalize?
- How do you keep denormalized data updated?
- Is denormalization bad?

Common mistakes to avoid

- Using denormalization without update strategy
- Using it for everything and creating inconsistent data
Denormalization is not bad if used intentionally. I use it when read performance matters and when we have a
proper way to keep the data updated.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 16

 81. What is performance tuning?
Direct interview answer
SQL performance tuning means identifying slow queries and improving them using better indexes, query changes,
execution plan analysis, reduced data scanning, proper joins, and sometimes schema changes.

Key points to remember

- Find slow query first
- Check execution plan
- Avoid SELECT *
- Use proper indexes
- Reduce rows early using WHERE
- Avoid unnecessary joins and functions on indexed columns

Real project usage

If an API endpoint is slow, I check which DB query is taking time, run it separately, review execution plan, check missing
indexes, reduce returned columns, and validate with actual runtime.

Example SQL

-- Bad: may scan many rows and return unnecessary columns

SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- Better: range filter can use index on OrderDate

SELECT OrderId, CustomerId, TotalAmount
FROM Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

Common follow-up questions

- What is index seek vs index scan?
- What is execution plan?
- How do you tune a slow stored procedure?

Common mistakes to avoid

- Adding indexes blindly
- Not measuring before and after
- Ignoring application-level pagination
For tuning, I first measure the problem, then check execution plan, data volume, indexes, joins, and query pattern.
I avoid guessing.

82. What is execution plan?

Direct interview answer
An execution plan shows how the database engine will execute a query. It shows table scans, index seeks, joins, sorts,
estimated cost, and missing index suggestions. It is one of the most useful tools for debugging slow SQL queries.

Key points to remember

- Shows query execution strategy
- Helps find scans and expensive operations
- Shows join type and order
- Can show missing index hints
- Estimated and actual plans are different

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 17

 Real project usage
When a report query is slow, I open the actual execution plan and check whether the database is scanning a huge table
or using an index seek. Then I tune the query or index based on evidence.

Example SQL

-- SQL Server examples

-- Enable actual execution plan in SSMS, or use:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderId, CustomerId

FROM Orders
WHERE CustomerId = 101;

Common follow-up questions

- What is index seek?
- What is table scan?
- Estimated vs actual execution plan?

Common mistakes to avoid

- Depending only on missing index suggestion
- Not checking actual row counts vs estimated row counts
Execution plan helps me understand what SQL Server is really doing. It is better than guessing when tuning
performance issues.

83. What is deadlock?

Direct interview answer
A deadlock happens when two or more transactions block each other permanently because each transaction is waiting
for a resource locked by the other. SQL Server detects this and kills one transaction as the deadlock victim.

Key points to remember

- Two transactions wait on each other
- Database chooses one victim
- Usually caused by inconsistent lock order
- Can happen in high concurrency systems
- Fix with shorter transactions and consistent access order

Real project usage

In an inventory system, Transaction A locks Product then Stock, while Transaction B locks Stock then Product. Each
waits for the other lock, causing a deadlock.

Example SQL

-- Practical prevention idea

-- Always update tables in the same order across code paths:
BEGIN TRANSACTION;
UPDATE Products SET UpdatedAt = GETDATE() WHERE ProductId = 10;
UPDATE Stock SET Quantity = Quantity - 1 WHERE ProductId = 10;
COMMIT;

Common follow-up questions

- Deadlock vs blocking?
- How do you debug deadlocks?
- How do you prevent deadlocks?

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 18

 Common mistakes to avoid
- Thinking deadlock means database server is down
- Keeping transactions open for too long
- Updating tables in different order in different procedures
Deadlocks are concurrency issues. I prevent them by keeping transactions short, accessing tables in consistent
order, indexing properly, and retrying safely when needed.

84. What is transaction?

Direct interview answer
A transaction is a group of database operations treated as one unit. Either all operations succeed and commit, or if
something fails, everything is rolled back. This keeps data consistent.

Key points to remember

- Group of operations as one unit
- COMMIT saves changes
- ROLLBACK cancels changes
- Protects data consistency
- Important for payment, stock, invoice, banking

Real project usage

When placing an order, we may insert order, insert order items, reduce stock, and create payment record. If payment
insert fails, the whole operation should rollback.

Example SQL

BEGIN TRANSACTION;

INSERT INTO Orders(CustomerId, TotalAmount) VALUES (101, 2500);

UPDATE Stock SET Quantity = Quantity - 1 WHERE ProductId = 55;

COMMIT;
-- In error case: ROLLBACK;

Common follow-up questions

- What are ACID properties?
- What is transaction isolation level?
- What is rollback?

Common mistakes to avoid

- Not using transaction for multi-step critical operations
- Keeping transaction open while waiting for user/API response
Transactions are used when multiple database changes must succeed or fail together. They are essential for
reliable business operations.

85. ACID properties

Direct interview answer
ACID describes the reliability rules of a database transaction: Atomicity, Consistency, Isolation, and Durability. These
properties make sure data remains correct even when failures or concurrent users exist.

Key points to remember

- Atomicity = all or nothing
- Consistency = valid state before and after transaction

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 19

 - Isolation = transactions should not wrongly affect each other
- Durability = committed data survives failure
- Important for reliable systems

Real project usage

In a money transfer, amount should be debited from one account and credited to another account together. If credit fails,
debit should not remain alone. ACID protects this kind of operation.

Key differences / quick comparison

Property Meaning

Atomicity All operations succeed or all rollback

Consistency Data remains valid according to rules

Isolation Concurrent transactions do not corrupt each other

Durability Committed changes are saved permanently

Example SQL

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountId = 2;
COMMIT;

Common follow-up questions

- Explain ACID with real example
- What is isolation level?
- What is dirty read?

Common mistakes to avoid

- Memorizing acronym but not explaining with example
- Not connecting ACID with transactions
ACID is the reason relational databases are trusted for critical operations. It ensures transactions are safe,
consistent, and reliable even with failures or concurrent users.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 20

 Final 10-Minute Revision
- INNER JOIN returns only matching records; LEFT JOIN keeps all left-side records.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- GROUP BY reduces rows into summary; window functions keep row-level detail.
- ROW_NUMBER is unique; RANK skips after ties; DENSE_RANK does not skip.
- Stored procedure is for process execution; function is for reusable calculation/result.
- Index improves reads but can slow inserts/updates if overused.
- Execution plan is the first place to check for slow query tuning.
- Transaction means commit all or rollback all; ACID makes it reliable.
- Deadlock is not just blocking; it is two transactions waiting on each other.
- Good SQL answer always includes project usage and performance awareness.

Strong closing line for SQL interviews

In real projects, I do not treat SQL as only query writing. I focus on correct data, clean relationships, safe
transactions, and performance using indexes and execution plans. That is what makes database code reliable in
production.

Part 4 - SQL Interview Notes | Practical .NET Developer Preparation Page 21