SQL Server Interview and Practice Questions

Beginner Level Questions

1. What does the SELECT statement do?
The SELECT statement retrieves data from one or more tables in the database. It specifies
which columns to return and can filter, sort or aggregate rows. For example,

SELECT * FROM Employees;

returns all columns of all rows from the Employees table 1 2 . The SELECT * syntax
means “select all columns” 1 , whereas listing specific columns (e.g. SELECT Name, Salary )
returns only those fields 2 .

2. How do you filter rows in a query?

You use the WHERE clause to restrict rows based on conditions. For instance,

SELECT * FROM Employees

WHERE DepartmentID = 3;

returns only employees in department 3. The WHERE clause follows FROM and allows you to
fetch rows that match specified conditions 3 . In this example, only rows satisfying
DepartmentID = 3 are returned 3 .

3. How do you sort query results?

Use ORDER BY to sort results by one or more columns. For example,

SELECT Name, Salary

FROM Employees
ORDER BY Salary DESC;

sorts employees by salary in descending order. By default, ORDER BY sorts in ascending order;
adding DESC makes it descending. The ORDER BY clause “is used to sort the result set in
ascending or descending order” 4 .

4. How do you select only unique values from a column?

Use the DISTINCT keyword. For example,

SELECT DISTINCT DepartmentID

FROM Employees;

1
 returns each DepartmentID once, even if it appears in multiple rows. This removes duplicate
values from the output. (While many tutorials note DISTINCT , one example is: SELECT
DISTINCT column FROM table; 1 .)

5. How can you combine multiple conditions?

You can use logical operators AND , OR , and NOT in the WHERE clause. For example:

SELECT * FROM Employees

WHERE DepartmentID = 3 AND Salary > 50000;

returns employees in department 3 and with salary over 50,000. Using OR :

SELECT * FROM Employees

WHERE DepartmentID = 3 OR DepartmentID = 4;

returns those in either dept 3 or 4. The AND / OR keywords let you filter on multiple criteria 5 .
(If none match, an empty set is returned 6 .)

6. How do you count rows or compute sums?

Use aggregate functions like COUNT() or SUM() . For example:

SELECT COUNT(*) AS TotalEmployees

FROM Employees;

returns the number of rows in Employees . Similarly,

SELECT SUM(Salary) AS TotalSalary

FROM Employees;

adds up all salaries. Aggregate functions must often go with GROUP BY if you group results.
For instance, to count per department:

SELECT DepartmentID, COUNT(*) AS DeptCount

FROM Employees
GROUP BY DepartmentID;

groups rows by DepartmentID and counts each group. In SQL, “the aggregate function SUM()
can be used with the GROUP BY clause to calculate sums for groups of rows” 7 , and similarly
for COUNT().

7. What is the purpose of GROUP BY ?

The GROUP BY clause groups rows that share the same values in specified columns, allowing
aggregate functions to compute per-group results. For example, the previous query groups by
DepartmentID . Without GROUP BY , aggregate functions apply to the entire result. In SQL,
“the aggregate function SUM() can be used with the GROUP BY clause to calculate sums for

2
 groups of rows based on one or more columns” 7 . Likewise, you can group by any column(s)
to, say, get average or count per group.

8. How do you filter aggregated groups?

Use HAVING after GROUP BY . Unlike WHERE (which filters individual rows), HAVING filters
groups. For example:

SELECT DepartmentID, AVG(Salary) AS AvgSal

FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;

returns only departments whose average salary exceeds 60,000. In SQL, the HAVING clause
filters results based on aggregate conditions (e.g., SUM or AVG) after grouping 8 .

9. How do you join two tables? (INNER JOIN)

An INNER JOIN returns rows that have matching values in both tables. For example, given
Employees and Departments tables linked by DepartmentID , you can write:

SELECT e.Name, d.DepartmentName

FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This selects each employee’s name along with their department name for those with matching
department IDs. An inner join “produces rows only when a match is found in both tables” (as is
standard SQL behavior). The INNER JOIN clause specifies how the tables relate by the ON
condition.

10. What is a LEFT JOIN?

A LEFT JOIN (or LEFT OUTER JOIN ) returns all rows from the left (first) table, and the
matching rows from the right table; unmatched right-side rows are returned as NULL . For
example:

SELECT e.Name, d.DepartmentName

FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

returns all employees, with DepartmentName if a matching department exists; otherwise

NULL . The left join preserves all left-table rows 9 (whereas an inner join would drop those
without a match).

11. What is a RIGHT JOIN?

A RIGHT JOIN returns all rows from the right table and matching rows from the left table, with
NULL for non-matching left rows. For example:

3
 SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This returns all departments, and any matching employee names. In practice, RIGHT JOIN is
the mirror of LEFT JOIN .

12. What is a FULL OUTER JOIN?

A FULL OUTER JOIN returns all rows when there is a match in either left or right table. It
combines the results of left and right joins. For example:

SELECT e.Name, d.DepartmentName

FROM Employees e
FULL JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

returns all employees and all departments, pairing where possible and using NULL where there
is no match. This covers situations where an employee has no department and where a
department has no employees. (A FULL JOIN is essentially all rows from both tables, joined
where keys match.)

13. What is a subquery?

A subquery is a SELECT query nested inside another query. It allows the inner query to provide
values to the outer query. For example:

SELECT FirstName
FROM Employees
WHERE Salary = (
SELECT MAX(Salary)
FROM Employees
);

This returns the employee(s) with the highest salary. Here the inner query (SELECT
MAX(Salary) FROM Employees) finds the maximum salary, and the outer query selects those
with that salary. In SQL, “a SELECT statement may contain another SQL statement, known as a
subquery or nested query” 10 .

14. What is the difference between a correlated and non-correlated subquery?

A non-correlated subquery can run independently of the outer query (it doesn’t reference outer
query columns). A correlated subquery refers to columns from the outer query and is evaluated
for each row of the outer query. For example, a correlated subquery might look like:

SELECT e.Name, e.Salary

FROM Employees e
WHERE e.Salary > (

4
 SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = e.DepartmentID
);

Here the inner query uses e.DepartmentID from the outer query, making it correlated.

15. How do you handle NULL values in expressions?

SQL has functions to handle NULL . The ISNULL(expr, value) function (SQL Server) returns
value if expr is NULL . For example:

SELECT ISNULL(ManagerID, 0) AS ManagerID

FROM Employees;

turns any NULL ManagerID into 0. According to documentation, “the ISNULL() function
returns a specified value if the expression is NULL” 11 . Similarly, COALESCE(expr1, expr2,
…) returns the first non- NULL expression. For example:

SELECT COALESCE(PhoneExtension, 'n/a') AS PhoneExt

FROM Employees;

returns the extension if not NULL , else 'n/a' . The COALESCE function “returns the first non-
null value in a list” 12 .

16. What is the difference between ISNULL and COALESCE ?

In SQL Server, both handle NULL but have differences. ISNULL(expr, val) takes two
arguments and returns val if expr is NULL . COALESCE(expr1, expr2, ..., exprN)
can take multiple arguments and returns the first non- NULL . Also, ISNULL returns the type of
the first argument, whereas COALESCE follows normal datatype precedence rules. In practice,
COALESCE is ANSI-standard. For example:

SELECT ISNULL(NULL, 'A'), COALESCE(NULL, 'A', 'B');

both yield 'A' in this case 12 11 .

17. How do you get the current date and time in SQL Server?
Use the GETDATE() function. For example:

SELECT GETDATE() AS CurrentTime;

returns the current system date and time. In SQL Server, “the GETDATE() function returns the
current database system date and time” in YYYY-MM-DD hh:mm:ss format 13 .

18. What is a view and how do you create one?

A view is a saved query represented as a virtual table. It does not store data itself but shows

5
 results of a SELECT . You create a view with CREATE VIEW . For example:

CREATE VIEW [Brazil_Customers] AS

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

This defines a view of Brazilian customers 14 . After creation, you can query the view like a table:
SELECT * FROM [Brazil_Customers]; 15 . As documented, “the CREATE VIEW command
creates a view. A view is a virtual table based on the result set of an SQL statement” 16 .

19. What is a stored procedure?

A stored procedure is a precompiled SQL code block stored in the database that can be executed
with parameters. It can contain logic like queries and control flow. For example, to create a
simple stored procedure:

CREATE PROCEDURE SelectAllEmployees

AS
SELECT * FROM Employees;
GO

You can then run it with EXEC SelectAllEmployees; . A stored procedure can also accept
parameters, e.g.:

CREATE PROCEDURE SelectEmployeesByDept @DeptID INT

AS
SELECT * FROM Employees WHERE DepartmentID = @DeptID;
GO

This returns employees for a given department. In SQL Server, the syntax is
CREATE PROCEDURE name AS ... 17 .

20. What is a trigger?

A trigger is special stored code that runs automatically in response to DML events (INSERT,
UPDATE, DELETE) on a table (or DDL events). For example, you can create an AFTER INSERT
trigger to log inserts:

CREATE TRIGGER dbo.trgEmpInsert

ON dbo.Employees
AFTER INSERT
AS
INSERT INTO EmployeeLog(EmpID, ActionDate)
SELECT EmployeeID, GETDATE() FROM INSERTED;

This trigger inserts a log row whenever a new employee is added. Triggers use special virtual
tables ( INSERTED , DELETED ). As documentation shows, a trigger is created with CREATE

6
 TRIGGER … ON table … FOR INSERT/UPDATE/DELETE and can, for example, insert into a log
table using INSERTED 18 . The above example is adapted from that pattern.

21. What are transactions?

Transactions ensure a sequence of operations is atomic. In T-SQL, you begin a transaction with
BEGIN TRANSACTION , then execute statements, and end with either COMMIT (to save) or
ROLLBACK (to undo). For example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR = 0
COMMIT;
ELSE
ROLLBACK;

This transfers 100 between accounts. The concept is that “explicit transactions start with the
BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement” 19 . If
any error occurs, the ROLLBACK undoes all updates, ensuring data integrity.

22. How do you handle errors in T-SQL?

Use the TRY...CATCH construct. You put code in a BEGIN TRY...END TRY block, and if an
error occurs, control jumps to BEGIN CATCH...END CATCH . For example:

BEGIN TRY
-- code that might fail
INSERT INTO Employees(Name) VALUES(NULL); -- suppose Name is NOT NULL
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;

In SQL Server, “a TRY...CATCH construct catches execution errors” and transfers control to the
CATCH block if an error occurs 20 . Inside CATCH , you can retrieve error info with functions
like ERROR_MESSAGE() .

23. How does ROW_NUMBER() work?

ROW_NUMBER() is a window function that assigns a unique sequential integer to rows in a
result set, according to an OVER clause. For example:

SELECT Name, Salary,

ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

will number employees 1, 2, 3, … in descending salary order. According to documentation,

ROW_NUMBER “returns the sequential number of a row within a partition of a result set, starting

7
 at 1 for the first row in each partition” 21 . If you include PARTITION BY DeptID in OVER ,
numbering restarts for each department.

24. What is the difference between ROW_NUMBER() and RANK() ?

Both are window functions with similar syntax, but differ in handling ties. ROW_NUMBER() gives
a unique sequence to every row, even if values tie (no duplicates). In contrast, RANK() gives the
same rank to tied values, and then skips rank(s). For example, if two people tie for second-
highest salary, ROW_NUMBER might give them 2 and 3, but RANK() would give both rank 2 and
the next would be rank 4. The MS docs note that " ROW_NUMBER numbers all rows sequentially…
RANK provides the same numeric value for ties” 21 .

25. What is a clustered index?

A clustered index determines the physical order of data in a table. There can be only one per
table. With a clustered index on key columns, the table’s rows are stored in sorted order of that
key. For example, CREATE CLUSTERED INDEX IX_EmpID ON Employees(EmployeeID)
would sort the table by EmployeeID . MS docs explain: a clustered index “sorts and stores the
data rows in the table based on their key values” and is the only ordering for the table 22 .
Without a clustered index, the table is a heap (no particular order).

26. What is a nonclustered index?

A nonclustered index is a separate structure from the data rows. It contains the indexed columns
and pointers to the actual data. You can have many per table. For example:

CREATE INDEX IX_LastName

ON Employees(LastName);

creates a nonclustered index on LastName . This speeds lookups by last name without
reordering the table. The pointer (row locator) tells SQL where to find the full row. According to
Microsoft: “Nonclustered indexes have a structure separate from the data rows. A nonclustered
index contains the nonclustered index key values and each key value entry has a pointer to the
data row” 23 .

27. Give an example of creating an index and explain its benefit.

Example:

CREATE INDEX idx_EmpID

ON Employees(EmployeeID);

This creates an index on EmployeeID . Indexes allow faster data retrieval by organizing data (B-
tree) for quick searches 24 25 . With an index on EmployeeID , queries like WHERE
EmployeeID = ... or joins on that column will use the index to find rows quickly rather than
scanning the whole table. W3Schools notes “indexes are used to retrieve data from the database
very fast” 25 .

28. How can you optimize a slow query?

Common techniques include adding appropriate indexes, avoiding unnecessary columns in
SELECT , and rewriting inefficient joins or subqueries. For example, ensure WHERE columns
are indexed, and only join/filter on indexed fields. Also use TOP or pagination if not all rows

8
 needed. Avoid SELECT * if many columns. Understanding the query execution plan helps
identify bottlenecks. (Using indexes as above is one key strategy.) According to MS
documentation and best practices, indexing relevant columns usually improves performance 25
24 .

29. How do you view the execution plan of a query?

In SQL Server Management Studio (SSMS), you can include an execution plan by clicking “Include
Actual Execution Plan” before running a query. This shows how SQL Server executed your query
(indexes used, join methods, etc.). You can also run SET STATISTICS PROFILE ON; or SET
SHOWPLAN_XML ON; to get plans. The plan helps diagnose performance issues by showing
table scans vs index seeks, join types, etc.

30. How do you use the UNION operator?

UNION combines results of two queries into one result set, removing duplicates. UNION ALL
does the same but keeps duplicates. For example:

SELECT Name FROM Employees WHERE DepartmentID = 1

UNION
SELECT Name FROM Employees WHERE DepartmentID = 2;

returns unique names from both departments. If you want all names including duplicates, use
UNION ALL . The important thing is both queries must have the same number of columns and
compatible types.

Intermediate Level Questions

1. How do you use HAVING with aggregate functions?
HAVING filters groups after aggregation. For example:

SELECT DepartmentID, COUNT(*) AS EmpCount

FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

returns only departments with more than 5 employees. Here, HAVING COUNT(*) > 5 applies
to the grouped result. Recall that WHERE cannot filter aggregates, so HAVING is needed for
conditions on counts, sums, etc.

2. Write an inner join involving three tables.

For example, to join Employees , Departments , and Locations :

SELECT e.Name, d.DepartmentName, l.LocationName

FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
INNER JOIN Locations l ON d.LocationID = l.LocationID;

9
 This retrieves each employee’s name, department, and location. It demonstrates joining multiple
tables by chaining INNER JOIN . Each join clause specifies matching keys.

3. Explain the difference between an INNER JOIN and a LEFT JOIN .

An INNER JOIN only returns rows where there is a match in both tables. A LEFT JOIN (as
earlier) returns all rows from the left table and matches from the right (filling with NULL if no
match). In other words, LEFT JOIN includes unmatched left rows that INNER JOIN would
exclude.

4. Give an example of a FULL OUTER JOIN .

SELECT e.Name, d.DepartmentName

FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This returns all employees and all departments. If an employee has no department,
DepartmentName will be NULL . If a department has no employees, Name will be NULL .
This covers all cases from both tables.

5. How do you write a correlated subquery?

A correlated subquery references the outer query’s columns. For example:

SELECT e.Name, e.Salary

FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = e.DepartmentID
);

The inner query uses e.DepartmentID from the outer query, making it correlated. This finds
employees whose salary is above the average salary in their own department. The subquery runs
once per outer row.

6. What is the purpose of a view?

Views simplify complex queries, encapsulate logic, and can provide security (restricting access to
underlying data). They act like virtual tables. For example, you could create a view joining several
tables, and then select from that view. Views can also simplify permissions by granting access to
the view rather than base tables. As noted, a view is “a virtual table based on the result set of an
SQL statement” 16 .

7. How do you create a stored procedure with a parameter?

Example:

CREATE PROCEDURE GetEmployeesByDept @DeptID INT

AS

10
 BEGIN
SELECT * FROM Employees WHERE DepartmentID = @DeptID;
END;
GO

This procedure takes @DeptID as input and returns employees in that department. You
execute it with:

EXEC GetEmployeesByDept @DeptID = 3;

as shown in W3Schools examples 26 .

8. What is an INSTEAD OF trigger?

An INSTEAD OF trigger replaces the action. For example, on a view that isn’t updatable, you
might define:

CREATE TRIGGER trg_VewUp

ON dbo.EmployeeView
INSTEAD OF INSERT
AS
INSERT INTO Employees(...) SELECT ... FROM INSERTED;

This trigger fires in place of the usual INSERT on the view, allowing custom logic. In contrast, an
AFTER trigger fires after the insert has succeeded on a table.

9. Explain BEGIN TRY...END TRY and BEGIN CATCH...END CATCH .

These blocks implement error handling. Within BEGIN TRY , you put the code that might fail. If
an error occurs, control jumps to the BEGIN CATCH block. For example:

BEGIN TRY
BEGIN TRANSACTION;
-- Perform updates
INSERT INTO Orders(...) VALUES(...);
UPDATE Inventory SET Qty = Qty - 1 WHERE ItemID = ...;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;

This ensures that on error, the transaction is rolled back. Microsoft states that TRY...CATCH
“catches execution errors” and transfers control to the CATCH block 20 .

10. What are clustered vs nonclustered indexes?

A clustered index defines the physical order of data. There can be one per table. Its key dictates
row sorting 22 . A nonclustered index is a separate B-tree that points to data rows (either by row

11
 pointer or clustered key) 23 . So clustered indexes speed up range queries on the key, while
nonclustered indexes speed lookups on other columns without changing row order.

11. How do you use window functions with partitions?

Example using ROW_NUMBER() partitioned by department:

SELECT Name, DepartmentID,

ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS
RankInDept
FROM Employees;

This resets the row number for each DepartmentID . The PARTITION BY clause divides the
result set into groups; within each group the numbering starts at 1. The MS docs note that
ROW_NUMBER() “returns the sequential number of a row within a partition” 21 .

12. Explain RANK() and DENSE_RANK() .

Both assign ranks to rows based on ordering, but treat ties differently. RANK() gives the same
rank to tied rows and leaves gaps. For example, ranks might go 1, 2, 2, 4. DENSE_RANK() also
gives tied rows the same rank but does not leave gaps: 1, 2, 2, 3. For example:

SELECT Name, Salary,

RANK() OVER (ORDER BY Salary DESC) AS Rnk,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRnk
FROM Employees;

If two employees tie for highest salary, both get 1 for Rnk and DenseRnk , but the next Rnk
would be 3 while the next DenseRnk would be 2.

13. What is a transaction log?

The transaction log records all changes made to the database (and transactions). It is used for
recovery and rollback. Every time data is modified within a transaction, the log writes the action.
If a transaction is rolled back, SQL Server uses the log to undo changes. In case of a crash, the
log is also used during recovery to redo or undo transactions to bring the database to a
consistent state.

14. What is deadlock and how can it be prevented?

A deadlock occurs when two or more transactions lock resources in a circular wait. For example,
T1 locks A then waits for B, while T2 locks B then waits for A. SQL Server detects deadlocks and
will kill one transaction. To prevent deadlocks, ensure transactions acquire locks in a consistent
order, keep transactions short, and use low isolation levels or row locking hints if needed.

15. How do you use UNION and UNION ALL ?

UNION combines result sets from two queries and removes duplicates. UNION ALL combines
them and keeps duplicates. For example:

12
 SELECT Name FROM Employees WHERE DepartmentID = 1
UNION
SELECT Name FROM Employees WHERE DepartmentID = 2;

returns each name once. Use UNION ALL if you want to include duplicates (and improve
performance if deduplication isn’t needed).

16. What is the difference between stored procedures and functions?

In SQL Server, a stored procedure ( PROC ) can perform operations and return result sets or
output parameters. It cannot be used in a SELECT statement. A user-defined function (UDF)
returns a value or table and can be used in queries. Functions must be deterministic and cannot
have side-effects like modifying data (unless it’s an inline table-valued function). Stored
procedures can contain transaction and error handling logic; functions are more limited.

17. What is SQL normalization (1NF, 2NF, 3NF)?

Normalization is the process of organizing tables to reduce redundancy.

◦ 1NF (First Normal Form): Eliminate repeating groups; each column holds atomic values.
◦ 2NF: Ensure 1NF and remove partial dependencies; non-key columns depend on the
whole primary key (applies when composite keys exist).
◦ 3NF: Ensure 2NF and remove transitive dependencies; non-key columns depend only on
the primary key, not on other non-key columns.
This design reduces update anomalies and saves space.

18. What are temporary tables and table variables?

Temporary tables (created with CREATE TABLE #Temp(...) ) store intermediate data and
exist for the session. Table variables ( @TempTable ) are similar but have scope limited to a
batch or procedure. Temporary tables can have indexes and statistics and persist for the session,
whereas table variables have more limited performance and scope. Use temp tables for large
data and queries involving multiple steps, table variables for small, in-memory intermediate
results.

19. How do you find and kill a long-running query in SQL Server?
Use system views or Activity Monitor. For example:

SELECT * FROM sys.dm_exec_requests WHERE status = 'running';

to find running queries. Or query sys.sysprocesses . Once you identify the session (SPID),
you can stop it:

KILL <session_id>;

20. What are common performance tuning strategies?

Key strategies include proper indexing (clustered/nonclustered as needed), avoiding SELECT *,
using joins on indexed columns, updating statistics, and examining execution plans. Use
indexing on columns used in WHERE , joins or ORDER BY 25 24 . Keep transactions short and
consider query hints only if necessary. Monitor with SQL Profiler or Query Store for bottlenecks.

13
21. What is an execution plan and how do you use it?
The execution plan shows how SQL Server executes a query. It indicates join types, index usage,
estimated costs, etc. In SSMS, you can display the Actual Execution Plan. Reading it helps identify
slow operations (e.g., table scans vs index seeks). The Query Optimizer chooses a plan; by
reviewing it, you can add indexes or rewrite queries to improve performance.

22. Explain the difference between a primary key and a unique constraint.
Both enforce uniqueness on columns. A table can have only one primary key, which implicitly
creates a unique, clustered index (unless a clustered index already exists, then a nonclustered
index). A unique constraint can be on multiple columns and creates a unique index. Unlike
primary key, unique constraint allows one row to have a NULL (depending on DBMS). In SQL
Server, defining a primary key creates a unique index 27 .

23. How would you update rows using JOIN?

Example: Suppose you want to update employees’ department names in another table. You can
do:

UPDATE e
SET e.Salary = e.Salary * 1.1
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales';

This increases salaries by 10% for employees in Sales. The UPDATE ... FROM JOIN syntax lets
you match rows across tables before updating.

24. What are indexes on computed columns or included columns?

SQL Server lets you index a computed column if it’s deterministic. Included columns allow you to
add non-key columns to an index leaf. For example:

CREATE INDEX IX_EmpName

ON Employees(FirstName, LastName)
INCLUDE (HireDate);

This index is on FirstName, LastName , but also stores HireDate in leaf nodes to cover
queries that select it, improving performance. Included columns are not part of the key but avoid
lookups.

25. What is a CTE (Common Table Expression)?

A CTE is a temporary named result set that you can reference within a single SELECT ,
INSERT , UPDATE , or DELETE . It’s defined with WITH . For example:

WITH RecentSales AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY SaleDate DESC) AS rn
FROM Sales
)
SELECT * FROM RecentSales WHERE rn <= 5;

14
 This returns the 5 most recent sales. CTEs are useful for readable complex queries or recursion
(recursive CTEs).

26. What is the difference between WHERE and ON in JOINs?

The ON clause specifies how to match rows between tables in a join. The WHERE clause filters
the combined result. For example, if you LEFT JOIN and put a condition in ON , you affect
matching logic; if you put the condition in WHERE , it can turn the join effectively into an inner
join by excluding rows where right-side is NULL. In general, use ON for join conditions and
WHERE for filtering the final result.

27. What is a self-join?

A self-join is when a table is joined to itself to compare rows. For example, to find all employee-
manager pairs (assuming each employee has a ManagerID referencing another employee):

SELECT e.Name AS Employee, m.Name AS Manager

FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

Here Employees appears twice with different aliases. It lets you relate rows within the same
table.

28. What is parameter sniffing?

Parameter sniffing is when SQL Server uses the parameter values passed to a stored procedure
on first execution to generate an execution plan. That plan is cached and reused, which can
cause performance issues if later calls have very different parameter values. There are ways to
mitigate it, such as using OPTION (RECOMPILE) or local variables.

29. How do triggers differ from stored procedures?

A trigger executes automatically in response to table events, whereas a stored procedure is
called explicitly. Triggers cannot be called directly and cannot commit/rollback outside their
batch. Triggers have access to INSERTED / DELETED tables; stored procedures do not.

30. What is an execution plan hint like FORCESEEK or NOLOCK ?

These are query hints. For example, FORCESEEK directs the optimizer to use an index seek
instead of a scan. WITH (NOLOCK) on a table means use a dirty read (no shared locks). These
hints override default optimization and locking behavior. They should be used cautiously, as they
can improve or degrade performance or affect data consistency. Generally, analyze the plan
before using hints.

Sources: Concepts and examples are based on SQL Server documentation and tutorials 1 2 3 4

7 8 12 11 13 16 18 19 20 21 25 24 10 .

1 SQL SELECT Statement

https://www.w3schools.com/sql/sql_select.asp

2 3 5 6 SQL SELECT and SELECT WHERE (With Examples)

https://www.programiz.com/sql/select

15
 4 SQL ORDER BY Clause (With Examples)
https://www.programiz.com/sql/order-by

7 8 SQL SUM With GROUP BY

https://www.programiz.com/sql/sum-with-group-by

9 25 SQL INDEX
https://www.w3schools.com/sql/sql_ref_index.asp

10 SQL Subquery (With Examples)

https://www.programiz.com/sql/subquery

11 SQL Server ISNULL() Function

https://www.w3schools.com/sql/func_sqlserver_isnull.asp

12 SQL Server COALESCE() Function

https://www.w3schools.com/sql/func_sqlserver_coalesce.asp

13 SQL Server GETDATE() Function

https://www.w3schools.com/sql/func_sqlserver_getdate.asp

14 15 16 SQL CREATE VIEW

https://www.w3schools.com/sql/sql_ref_create_view.asp

17 26 SQL Stored Procedures

https://www.w3schools.com/sql/sql_stored_procedures.asp

18 Triggers in SQL Server

https://www.tutorialsteacher.com/sqlserver/triggers

19 BEGIN TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver16

20 TRY...CATCH (Transact-SQL) - SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16

21 ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16

22 23 24 27 Clustered and nonclustered indexes - SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-
server-ver16

16