Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)

by
21. What is a Covering Index?

Answer: An index that contains all the columns needed to satisfy a query without accessing the base table (reduces IO).

Example: CREATE NONCLUSTERED INDEX idx_Covering
ON Employees (Name, DepartmentID, Salary);

22. Explain Database Mirroring.

Answer: A disaster recovery feature to keep two copies of a database synchronized (Principal and Mirror server).

23. What is Change Data Capture (CDC)?

Answer: A feature to track changes (INSERT, UPDATE, DELETE) in tables automatically.

Example:

EXEC sys.sp_cdc_enable_table
@source_schema = ‘dbo’,
@source_name = ‘Employees’,
@role_name = NULL;

24. What is Change Tracking?

Answer: Lightweight alternative to CDC — only tells that a change happened, not what changed.

Example:

ALTER TABLE Employees ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
25. What are Window Functions?

Answer: Functions that perform calculations across a set of table rows related to the current row.

Example:

SELECT Name, Salary,
AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary
FROM Employees;
26. Difference between DELETE and TRUNCATE?

Answer:

DELETE TRUNCATE
Row-by-row deletion Deallocates entire data pages
Can use WHERE clause Cannot use WHERE
Slower Faster
Triggers fire No triggers fire
27. What are Indexed Views Limitations?

Answer:

  • Restrictions like:

    • No outer joins,

    • No aggregates like DISTINCT,

    • Must be schema-bound,

    • Columns must be deterministic.

28. Explain the FOR JSON clause in SQL Server.

Answer: Generates JSON output from SQL query results.

Example: SELECT Name, Salary
FROM Employees
FOR JSON AUTO;

29. What is MERGE Statement?

Answer: A single statement to Insert, Update, or Delete based on conditions.

Example:

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN
INSERT (ID, Name) VALUES (S.ID, S.Name);
30. Explain Error Handling with TRY…CATCH.

Answer: Handle errors inside SQL batches, procedures, or triggers.

Example:

BEGIN TRY
— Attempt to divide by zero
SELECT 1 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Page : 1 2 3 4 5

1 thought on “Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)”

  1. Pingback: Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples) - Telugu Data Hub

Leave a Comment