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

Two keys, one red and one brass, hanging on cords toward the single keyhole of a door lock.

MERGE Statement Quiz: What If Two Source Rows Match One Target?

January 20, 2012

Output Clause, Ranking Functions, SQL Delete, SQL Table Operation

This MERGE Statement Quiz asks what happens when two source rows match one target row. The answer is error 8672, and the whole statement fails. A tested script shows the error, the fix with ROW_NUMBER and the same change written as UPDATE plus INSERT.

A grand piano with its lid raised, showing the strings and hammers inside, one hammer painted red.

Resource Database Quiz: Where Do the System Objects Live?

January 19, 2012

Pinal Dave

SQL Puzzle

SQL Backup and Restore, SQL Server Architecture, SQL System Table, System Database

This Resource Database Quiz asks where SQL Server stores the definition of sys.objects and whether BACKUP DATABASE can protect it. The answer is the hidden Resource database. A tested script shows its version, its date and the system objects every database sees.

A white picket fence around a neat vegetable bed with a red gate latch and one tall sunflower standing out of line inside.

Create Constraints Quiz: What Does WITH NOCHECK Leave Behind?

January 18, 2012

Pinal Dave

SQL Puzzle

Primary Key, SQL Column, SQL Constraint and Keys, SQL Table Operation

This Create Constraints Quiz adds a CHECK constraint WITH NOCHECK to a table that already holds a bad row. The bad row stays, and the constraint is marked not trusted. A tested script proves it, shows what trust changes for the optimizer and lists four ways to create a constraint.

A slate board on an easel, partly wiped clean, with a sponge resting on the ledge.

CHECKPOINT Behavior Quiz: What Lets the Log Be Reused?

January 17, 2012

Pinal Dave

SQL Puzzle

SQL Backup and Restore, SQL Log, SQL Server Configuration, Transaction Log

This CHECKPOINT Behavior Quiz asks what lets SQL Server reuse transaction log space under the SIMPLE and FULL recovery models. The answers differ, and a tested script shows both. It also shows how one open transaction keeps the log from being reused.

Three open boxes of different sizes, each holding three dark balls, one ball in each box painted red.

CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) Quiz: How Many Bytes?

January 16, 2012

Pinal Dave

SQL Puzzle

SQL Column, SQL Datatype, SQL String, Unicode

This CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) Quiz stores the text abc in three columns and asks how many bytes each one uses. The answer is 10, 3 and 6. A tested script proves it and shows the trailing space trap, the question mark problem and the 8,000 byte limit.

A plain map spread on a wooden table with a red ribbon marking a new detour route, a brass compass beside it.

Stored Procedure Recompile Quiz: What Forces a New Plan?

January 15, 2012

Pinal Dave

SQL Puzzle

Execution Plan, Parameter Sniffing, Recompile, SQL Stored Procedure

This Stored Procedure Recompile Quiz asks which action makes SQL Server build a new plan for a procedure. The answer is a new index on a table it reads. A tested script uses plan counters to show what reuses the plan and what rebuilds it.

A sandcastle on a beach half washed away by the sea, and beside it a red bucket of shells that stayed dry.

Table Variables vs Temporary Tables Quiz: Which Survives a Rollback?

January 14, 2012

Pinal Dave

SQL Puzzle

SQL TempDB, SQL Transactions, SQL Variable, Temp Table

This Table Variables vs Temporary Tables Quiz inserts one row into each, then rolls back. The table variable keeps its row and the temporary table loses it. A tested script proves it, and two more scripts show a rollback-proof log and the statistics gap.