This page needs a newer browser to run its tools. You can still read everything here.

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

T-SQL foundations: batches, variables and routines interview questions

Interview questions on “T-SQL foundations: batches, variables and routines”: 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 staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

Why is GO not part of T-SQL?

Answered in the lesson T-SQL batches, GO and variables
Fresher to mid

When does T-SQL need BEGIN...END?

Answered in the lesson T-SQL control flow: IF, WHILE and CASE
Fresher to mid

Why does a release script change a procedure with CREATE OR ALTER rather than DROP and CREATE?

Answered in the lesson T-SQL stored procedures and parameters
Fresher to mid

How do inline and multi-statement table-valued functions differ?

Answered in the lesson T-SQL functions: scalar, inline and multi-statement

Questions with model answers

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

Mid to senior

A payout procedure finds the account holder with SELECT @holder = full_name FROM dbo.customers WHERE city = @city, and it has worked for years. After a second customer from the same city joins, a payout goes to the wrong name, with no error. What happened, and how do you rewrite the lookup?
What it tests

Knowing that SELECT assignment keeps the last row without an error, choosing a lookup that fails loudly, and backing the rule with a key, a check and a test.

First step

Count the rows the lookup reads. SELECT assignment over two rows keeps whichever the query returned last and raises nothing, so the lookup must use a key, or be written so that a second row is an error.
Follow-ups
- Why did it work for years?
- How would SET with a subquery behave here?
- How would you find other lookups like it in the code?
From the lesson T-SQL batches, GO and variables
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
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.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I 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
Mid to senior

Your team's migration runner sends each .sql file to SQL Server with one call of the database driver. A new file that creates a view and then grants a permission on it fails with a syntax error near GO, yet it runs fine in SSMS. Explain the difference and change the runner.
What it tests

Understanding that GO is a client-side separator, which batch rules make it necessary, and how to build a runner that cuts scripts the way sqlcmd does.

First step

SSMS cuts the file at its GO lines and sends each batch on its own; the driver sends the text unchanged, and the server has no GO statement. The runner has to do the cutting itself.
Follow-ups
- How do you split safely, without cutting at a GO inside a string or a comment?
- Why not simply delete the GO lines?
- What should the runner do when one batch fails?
From the lesson T-SQL batches, GO and variables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly procedure posts monthly interest with a WHILE loop: it reads one savings account at a time, computes the interest, updates the balance and inserts a ledger row, about 50,000 iterations a night. It is slow and sometimes stops halfway. How would you rewrite it, and what would you check before switching?
What it tests

Turning a row-by-row loop into set-based statements, keeping the work all-or-nothing, and proving that the new version gives the same results before it replaces the old one.

First step

Describe the work as sets: every account that earns interest gets one ledger row and one balance update. Two statements in one transaction, an INSERT from a SELECT and an UPDATE, can replace the whole loop.
Follow-ups
- How do you prove that the new version computes the same interest?
- What if the job must not lock every account at once?
- When is a loop still the right tool?
From the lesson T-SQL control flow: IF, WHILE and CASE
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A loyalty report puts customers into tiers with a CASE expression. Customers who spent exactly 900.00 complain that they show as silver, although the rule says 900.00 or more is gold, and in fact nobody shows as gold at all. What would you look for, and how would you test the fix?
What it tests

Knowing that CASE returns the result of the first true WHEN, spotting thresholds tested in the wrong order, and writing boundary tests that would have caught it.

First step

Read the WHEN clauses from the top, because the first true one wins. If spend >= 400.00 is tested before spend >= 900.00, every customer who would be gold stops at silver.
Follow-ups
- Why did nobody notice before the complaints?
- How would you keep the thresholds out of the code?
- What should the CASE give a customer with no orders?
From the lesson T-SQL control flow: IF, WHILE and CASE
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A procedure inserts an order and returns its key with @@IDENTITY. The day the team adds an audit trigger to the orders table, callers start receiving keys that do not exist in it. Explain what changed, and fix the procedure so that the next trigger cannot break it.
What it tests

Knowing the difference between scope and session for identity values, capturing a key at the right moment, and returning many keys safely.

First step

@@IDENTITY returns the last identity value of the session in any scope, and the trigger's insert into its audit table is now the last one. The procedure needs the value from its own scope.
Follow-ups
- Why did it work before the trigger existed?
- Why not IDENT_CURRENT('dbo.orders') instead?
- How would you return the keys of 100 orders inserted at once?
From the lesson T-SQL stored procedures and parameters
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

You are asked to write dbo.open_account for three applications that will call it. What would its interface look like, with parameters, defaults, OUTPUT values, a return code and errors, and how would you change it later without breaking the callers?
What it tests

Designing a procedure as a contract: typed parameters with defaults, an OUTPUT for the new key, errors for failure, safe retries, and rules for changing it compatibly.

First step

Write down what every caller sends and gets back: the customer, the kind of account and an opening amount go in, the new account id comes out through OUTPUT, and every failure is a documented error number.
Follow-ups
- Why an OUTPUT parameter rather than a result set for the new id?
- How do you stop a retried call from opening two accounts?
- What if one caller later needs more data back?
From the lesson T-SQL stored procedures and parameters
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A monthly report over a million order lines became much slower after the tax calculation moved into a scalar function, dbo.line_tax(@amount), which reads GETDATE() to pick the current rate. The server is SQL Server 2022. How do you find out why, and what would you change?
What it tests

Knowing when scalar functions run once per row, how to check whether SQL Server can inline one, and how to rewrite the logic so the optimizer sees it.

First step

Check sys.sql_modules.is_inlineable for the function and the database's compatibility level: a function that calls GETDATE() is never inlined, so it runs once for each of the million rows.
Follow-ups
- How would you prove that the function is the cause?
- Why move the date out of the function?
- When would you replace the function with an inline TVF instead?
From the lesson T-SQL functions: scalar, inline and multi-statement
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A query joins a multi-statement table-valued function that returns about 99,000 rows to two large tables, and the actual plan shows nested loops built for 100 rows. The database is at compatibility level 130. Explain the plan and give three fixes, in the order you would try them.
What it tests

Reading a fixed cardinality estimate in a plan, knowing what interleaved execution changes, and choosing between a rewrite, a compatibility change and a temporary table.

First step

The 100 is not a statistic but the fixed guess for multi-statement functions from SQL Server 2014 on. At level 130 interleaved execution is off, so the join strategy was chosen for 100 rows.
Follow-ups
- Where in the plan do you see the wrong estimate?
- Why is raising the compatibility level not free?
- When is a multi-statement function still a reasonable choice?
From the lesson T-SQL functions: scalar, inline and multi-statement
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

T-SQL batches, GO and variables Free lesson, Pro answers: 10 minutes
T-SQL control flow: IF, WHILE and CASE Free core, Pro depth: 3 of 10 minutes free
T-SQL stored procedures and parameters Free core, Pro depth: 3 of 10 minutes free
T-SQL functions: scalar, inline and multi-statement Free core, Pro depth: 3 of 10 minutes free

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

Warm-up questions

Why is GO not part of T-SQL?

When does T-SQL need BEGIN...END?

Why does a release script change a procedure with CREATE OR ALTER rather than DROP and CREATE?

How do inline and multi-statement table-valued functions differ?

Questions with model answers

A payout procedure finds the account holder with SELECT @holder = full_name FROM dbo.customers WHERE city = @city, and it has worked for years. After a second customer from the same city joins, a payout goes to the wrong name, with no error. What happened, and how do you rewrite the lookup?

Your team's migration runner sends each .sql file to SQL Server with one call of the database driver. A new file that creates a view and then grants a permission on it fails with a syntax error near GO, yet it runs fine in SSMS. Explain the difference and change the runner.

A loyalty report puts customers into tiers with a CASE expression. Customers who spent exactly 900.00 complain that they show as silver, although the rule says 900.00 or more is gold, and in fact nobody shows as gold at all. What would you look for, and how would you test the fix?

A procedure inserts an order and returns its key with @@IDENTITY. The day the team adds an audit trigger to the orders table, callers start receiving keys that do not exist in it. Explain what changed, and fix the procedure so that the next trigger cannot break it.

You are asked to write dbo.open_account for three applications that will call it. What would its interface look like, with parameters, defaults, OUTPUT values, a return code and errors, and how would you change it later without breaking the callers?

A monthly report over a million order lines became much slower after the tax calculation moved into a scalar function, dbo.line_tax(@amount), which reads GETDATE() to pick the current rate. The server is SQL Server 2022. How do you find out why, and what would you change?

A query joins a multi-statement table-valued function that returns about 99,000 rows to two large tables, and the actual plan shows nested loops built for 100 rows. The database is at compatibility level 130. Explain the plan and give three fixes, in the order you would try them.