SQL Puzzle Archives - Page 15 of 22 - SQL Authority with Pinal Dave

A tall wooden bookcase standing against a wall, with a red sanding block on the floor in front of it.

Nonclustered Index Rebuild Quiz: Does a Clustered Rebuild Touch Them?

January 12, 2012

Clustered Index, SQL Heap, SQL Index, SQL Statistics

This Nonclustered Index Rebuild Quiz asks what happens to the other indexes when you rebuild the clustered index. The answer is nothing, and a tested script shows it. It also covers the cases where SQL Server does rebuild them.

A pantry shelf where an old ceramic jar sits on a red tray beside the new jar that took its place.

OUTPUT Clause Quiz: What Do inserted and deleted Hold?

January 11, 2012

Pinal Dave

SQL Puzzle

Output Clause, SQL Delete, SQL Table Operation, SQL Transactions

This OUTPUT Clause Quiz raises a price from 10 to 12 and asks which two values come back. The answer is 10 and 12, because deleted holds the old row and inserted the new one. A tested script proves it and shows OUTPUT with INSERT, DELETE and an audit table.

Two toy locomotives facing each other nose to nose on one wooden track, one red and one slate blue, neither able to pass.

Blocking and Deadlock Quiz: Which One Ends by Itself?

January 10, 2012

Pinal Dave

SQL Puzzle

Deadlock, SQL Error Messages, SQL Lock, SQL Transactions

This Blocking and Deadlock Quiz asks which problem SQL Server ends on its own, and how the losing session finds out. A two-window test on SQL Server 2025 shows the deadlock error 1205 and the lock timeout error 1222. It also shows how to find a blocker and how to retry.

A small wooden podium on a plain floor with red ribbons laid across the top step.

Ranking Functions Quiz: ROW_NUMBER, RANK or DENSE_RANK?

January 9, 2012

Pinal Dave

SQL Puzzle

Ranking Functions, SQL Cursor, SQL Function, SQL Order By

This Ranking Functions Quiz uses two tied scores to ask what number the next student gets from ROW_NUMBER, RANK and DENSE_RANK. The answer is 3, 3 and 2. A tested script proves it and replaces a cursor loop with one query.

A wooden board with rows of small square openings and an oversized red piece leaning against it that cannot fit.

Indexed View Restrictions Quiz: Which Rule Blocks the Index?

January 8, 2012

Pinal Dave

SQL Puzzle

Clustered Index, SQL Error Messages, SQL Index, SQL Joins, SQL View

This Indexed View Restrictions Quiz asks which rule stops a unique clustered index on a view. The answer is the LEFT OUTER JOIN. A tested script shows the error, the working view, and what happens when each required rule is missing.

Two nearly identical white teacups on saucers, one with a small mark on its rim.

Collation Sensitivity Quiz: Does ‘jose’ Match ‘José’?

January 7, 2012

Pinal Dave

SQL Puzzle

SQL Collation, SQL Error Messages, SQL String, Unicode

This Collation Sensitivity Quiz asks whether jose matches JOSE and José in a case-insensitive, accent-sensitive database. The answer is JOSE yes, José no. A tested script compares three collations and shows the width and kana settings too.

A narrow wooden footbridge over a calm stream with a small red gate closing its entrance.

Locking and Blocking Quiz: Will Updating a Different Row Wait?

January 6, 2012

Pinal Dave

SQL Puzzle

SQL DMV, SQL Index, SQL Lock, SQL Performance

This Locking and Blocking Quiz asks whether an update waits when it changes a different row than an open transaction. The answer depends on the index. A two-window test on SQL Server 2025 shows the wait, the lock that causes it, and two ways to remove it.