Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Microsoft hiring for SQL roles
Microsoft builds software, cloud and hardware products including Windows, Microsoft 365, Azure, Dynamics, GitHub, LinkedIn and Xbox. The India Development Center in Hyderabad and Bengaluru is one of its largest engineering hubs outside the US. It hires for software engineers, data scientists, data and applied scientists, and engineers working on Azure data and quality.
For SQL roles, this Microsoft practice set focuses most on Indexes and Performance (5), Window Functions (5), SQL Basics (4), Transactions and Advanced SQL (4). It has 23 medium, 7 hard questions, so you can start easy and work up to the harder ones.
Company type
Product
Headquarters
Redmond, Washington, USA
Main India offices
Hyderabad, Bengaluru, Noida, Gurugram, Pune
Microsoft SQL interview process
Online coding assessment
Technical round 1 (DSA)
Technical round 2 (DSA or design)
Hiring manager round
AA (As Appropriate) round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for MicrosoftStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
A search on name LIKE '%sharma%' is very slow on a 20 million row table. What can you do?
A leading wildcard cannot use a normal B-tree index, so the database scans the table. Options are full-text indexing, trigram indexes in PostgreSQL, a reversed or computed column if the pattern is a suffix, or a search engine for heavy text search. Also limit the result, filter on other indexed columns first, and check whether users really need contains search.
02
Join three tables to get total sales by product category.
SELECT cat.category_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;
Join from the most detailed table outward and aggregate at the end.
A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.
04
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
05
Why can't you use a window function in WHERE, and how do you filter on it?
Window functions are evaluated after WHERE, GROUP BY and HAVING in the logical order, as part of SELECT, so their results do not exist when WHERE runs. To filter, compute the window function in a subquery or CTE and filter in the outer query. Some databases such as Snowflake and BigQuery support a QUALIFY clause for filtering window results directly.
06
What is the difference between ROWS and RANGE frames?
ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.
07
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
08
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
10
Management wants the percentage of each category in total sales. How do you write it?
Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.
11
You need to update 50 million rows in production. How do you do it safely?
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
12
Inserts into a heavily used table have become slow. What could be causing it?
Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.
PostgreSQL and standard SQL:
SELECT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
AS median_salary
FROM employees
GROUP BY department_id;
SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.
14
Implement keyset pagination instead of OFFSET for large tables.
Slow for deep pages:
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000;
Keyset pagination:
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 50;
OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.
NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.
16
A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?
The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.
17
The dashboard queries hit the production database and slow down the app at month end. What do you propose?
Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.
18
How do you get the top 3 rows in different databases?
MySQL and PostgreSQL:
SELECT * FROM products ORDER BY price DESC LIMIT 3;
SQL Server:
SELECT TOP 3 * FROM products ORDER BY price DESC;
Standard SQL:
SELECT * FROM products ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;
Always use ORDER BY, otherwise the top rows are not guaranteed.
19
What are dirty reads, non-repeatable reads and phantom reads?
A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.
20
Why is NOT IN dangerous when the subquery contains NULL?
If the subquery returns any NULL, value NOT IN (list) becomes unknown for every row, because comparing with NULL is unknown, so the query returns no rows. For example, NOT IN (SELECT manager_id FROM employees) fails when a manager_id is NULL. Use NOT EXISTS or filter out NULLs in the subquery to get correct results.
Never build SQL by concatenating user input. Use parameterised queries or prepared statements, where input is sent separately from the SQL text, or ORMs that do this automatically. Validate and whitelist inputs such as sort column names, give application users least privilege, and avoid dynamic SQL in procedures unless parameters are bound. Input like ' OR 1=1 -- then cannot change the query logic.
22
A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?
Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.
23
Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?
Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.
24
What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows, which requires sorting or hashing. UNION ALL combines results and keeps all rows, so it is faster. Both need the same number of columns with compatible types in the same order. Use UNION ALL when duplicates are impossible or acceptable, such as stacking monthly tables, and UNION only when deduplication is required.
25
Can you use window functions with aggregate functions in the same query?
Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.
26
A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?
Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.
LIKE matches string patterns. The percent sign matches any sequence of characters and the underscore matches exactly one character. name LIKE 'Ra%' finds names starting with Ra, and code LIKE 'A_1' matches A01 or AB1. Leading wildcards such as LIKE '%kumar' prevent index use and are slow on large tables. Case sensitivity depends on the database collation.
28
After joining orders with payments, revenue doubled. What went wrong?
Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.
29
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
30
You need to delete duplicate rows but keep one copy of each. How do you do it?
Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.
Tips to crack the Microsoft SQL interview
Practise DSA on linked lists, trees and graphs, and be ready to write clean, compilable code with edge cases handled.
Learn basic Azure data services such as Azure SQL, Synapse and Data Factory if you are applying for data roles.
Prepare examples showing a growth mindset, which Microsoft highlights as a core part of its culture.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; Microsoft interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Microsoft SQL interview?
Candidates usually go through 5 stages: Online coding assessment; Technical round 1 (DSA); Technical round 2 (DSA or design); Hiring manager round; AA (As Appropriate) round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the Microsoft SQL interview?
This practice set leans on Indexes and Performance (5), Window Functions (5), SQL Basics (4), Transactions and Advanced SQL (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Microsoft interviewers ask you to walk through real work.
Are these the exact questions asked at Microsoft?
No. These are curated practice questions that match the skills and difficulty usually tested in product interviews like Microsoft's. They are not a leaked or verified list of past questions.
Where does Microsoft hire SQL professionals in India?
Microsoft has large teams in Hyderabad, Bengaluru, Noida, Gurugram, Pune. Openings and locations change often, so check the Microsoft careers page and filter by city and experience.