MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

PL/SQL & T-SQL (Oracle and SQL Server) Module 4

Working with sets: temporary tables, MERGE and cursors interview questions

Interview questions on “Working with sets: temporary tables, MERGE and cursors”: warm-ups, then what each harder question tests and how a good answer starts.

12 questions: 4 warm-ups and 8 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher to mid

    How do #local and ##global temporary tables differ?

    Answered in the lesson T-SQL temporary tables: #local and ##global

  2. Fresher to mid

    How do a table variable and a temporary table differ?

    Answered in the lesson Table variables and table-valued parameters

  3. Fresher to mid

    Which WHEN clauses can a MERGE have, and which rows does each one handle?

    Answered in the lesson T-SQL MERGE, OUTPUT and safe upserts

  4. Fresher to mid

    What do LOCAL and FAST_FORWARD mean in DECLARE CURSOR?

    Answered in the lesson T-SQL cursors and set-based rewrites

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Mid to senior

    A report procedure creates #totals with CONSTRAINT totals_pk PRIMARY KEY, and its callers run it inside their own transactions. Since a second team started calling it, calls queue up behind each other at month end while the CPU is nearly idle. Explain what is happening and how you would change the procedure.

    What it tests
    Knowing the documented limit of named constraints on temporary tables created inside a user transaction, and diagnosing waits that are not about the report's data.
    First step
    The waits are not on data. Microsoft documents that when a temporary table with a named constraint is created inside a user transaction, only one session at a time can run that CREATE, so every caller's transaction turns the procedure into a queue.
    Follow-ups

    From the lesson T-SQL temporary tables: #local and ##global

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  2. Mid to senior

    A scheduler runs a nightly job in two steps, each on its own database connection. Step 1 loads the day's orders into #stage; step 2 runs dbo.build_report, which reads #stage. Step 2 fails with error 208 every night, although step 1 succeeds. Explain why, and give two designs that work.

    What it tests
    Knowing that a local temporary table belongs to one session and ends with it, and choosing a staging design that works across connections and reruns.
    First step
    Step 1's #stage belongs to step 1's session and is dropped when that session ends. Step 2 has a session of its own, with no such table, so the report's SELECT fails with error 208, Invalid object name.
    Follow-ups

    From the lesson T-SQL temporary tables: #local and ##global

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  3. Mid to senior

    A procedure validates 5,000 imported rows inside a transaction, writes each bad row into #rejects, and when any row is bad it rolls back and returns SELECT * FROM #rejects. Support says the list of rejected rows is always empty, although imports fail. Explain why, and fix it without losing the all-or-nothing import.

    What it tests
    Knowing that temporary tables take part in transactions while table variables do not, and ordering the rollback and the logging so that both the data and the evidence end up right.
    First step
    The rejected rows were written to #rejects inside the transaction, so the ROLLBACK removed them together with the imported rows. A table variable keeps the rows written inside a transaction that is rolled back.
    Follow-ups

    From the lesson Table variables and table-valued parameters

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Mid to senior

    dbo.get_orders takes @ids varchar(max) such as '4,9,15', splits it and returns those orders. Callers send up to 10,000 ids, some send '4, 9' with spaces, and one sent '4;9'. Redesign the interface with a table-valued parameter, and explain what changes for the callers and for the plan.

    What it tests
    Designing a user-defined table type with a key, calling it from T-SQL and from an application, and knowing what the optimizer knows about a table-valued parameter.
    First step
    Create a table type dbo.id_list with one int column as its primary key, change the parameter to @ids dbo.id_list READONLY, and join it to dbo.orders. Type, uniqueness and NOT NULL are then checked as the rows arrive.
    Follow-ups

    From the lesson Table variables and table-valued parameters

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Mid to senior

    A nightly MERGE applies a supplier's price feed to dbo.stock and has worked for a year. Last night it failed with error 8672, and this morning's prices are stale. Explain the error, change the job so that it cannot fail this way again, and say what you would report to the supplier.

    What it tests
    Reading error 8672, de-duplicating a source on purpose, and making a feed job fail safely and visibly instead of silently.
    First step
    Error 8672 means that one target row matched more than one source row, so the MERGE would have updated the same row twice, and the whole statement stopped. Find the SKUs that appear more than once in last night's feed with GROUP BY sku HAVING COUNT(*) > 1.
    Follow-ups

    From the lesson T-SQL MERGE, OUTPUT and safe upserts

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Mid to senior

    An API registers a customer's device with IF NOT EXISTS (SELECT … WHERE device_id = @id) INSERT …. Under load the device table collects duplicate rows, and since a unique constraint was added, callers get error 2627 a few times an hour. Explain the race and write an upsert that is safe under concurrency.

    What it tests
    Recognising the check-then-insert race, knowing what HOLDLOCK, or UPDLOCK with SERIALIZABLE, changes, and keeping a constraint as the final guard.
    First step
    Two requests check at the same moment, both find no row and both insert, because the check keeps no lock on a key it did not find. The unique constraint turns the duplicate into error 2627, but it does not stop the race.
    Follow-ups

    From the lesson T-SQL MERGE, OUTPUT and safe upserts

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  7. Mid to senior

    A month-end procedure walks 2 million ledger rows with a cursor, works out each account's closing balance and writes it to dbo.monthly_balances, one INSERT per account. It now takes 4 hours and blocks the morning shift. Plan the rewrite, and say how you would prove that it is both correct and faster.

    What it tests
    Turning row-by-row logic into set-based statements, proving equality before speed, and measuring with SET STATISTICS IO and TIME.
    First step
    A closing balance per account is a GROUP BY over every ledger row up to the month's end, or SUM() OVER when every row's running balance is needed. Write the set-based version next to the cursor and compare their results with EXCEPT before timing anything.
    Follow-ups

    From the lesson T-SQL cursors and set-based rewrites

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Mid to senior

    A nightly procedure with a cursor sometimes runs until someone kills it, and on those nights one CPU core stays at 100 %. On other nights it finishes in seconds. The loop body has an IF that skips accounts flagged as closed. Explain the most likely cause, and how you would fix it and keep it from coming back.

    What it tests
    Recognising a misplaced FETCH, reading @@FETCH_STATUS, and writing cursor code that cannot loop forever, or removing the cursor.
    First step
    The second FETCH NEXT is probably inside the IF, so the first closed account stops the cursor moving: @@FETCH_STATUS stays 0 and the loop works on that row forever. Nights without a closed account finish normally.
    Follow-ups

    From the lesson T-SQL cursors and set-based rewrites

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track