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

An open suitcase on a bed, one side empty with its straps showing and the other side packed with folded linens under a red strap.

DACPAC vs BACPAC Quiz: Which File Carries Your Data?

January 13, 2012

Cloud Computing, Schema, SQL Backup and Restore, SQL Migration

This DACPAC vs BACPAC Quiz asks which file moves a database with its rows to Azure SQL Database. The answer is the BACPAC. A tested script shows what each file describes, and the SqlPackage commands show how to use them.

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

Pinal Dave

SQL Puzzle

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.