Pinal Dave - SQL Server Performance Tuning Expert

A small model village where most houses are pale and unfinished and one finished house has a red roof and warm lit windows.

Piecemeal Restore Quiz: What Happens to Tables Not Restored Yet?

January 23, 2012

SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Server Architecture

This Piecemeal Restore Quiz asks what happens when a query reads a table whose filegroup is still offline. The answer is an error for that table only, while everything else keeps working. A tested script shows the error and then brings the last filegroup online.

A half-knitted cream scarf resting on an armchair with its needles, a red ball of yarn beside it ready to continue.

Rebuild Index Quiz: DROP and CREATE, DROP_EXISTING or REBUILD?

January 22, 2012

Pinal Dave

SQL Puzzle

Maintenance Plan, SQL Index, SQL Performance, SQL Table Operation

This Rebuild Index Quiz asks which way of refreshing an index stays usable and can pause and resume. The answer is ALTER INDEX with ONLINE and RESUMABLE turned on. A tested two-window script shows the pause and the resume.

A set of nested wooden boxes opened on a table, the smallest innermost box painted red.

XML Data Type Quiz: Which Method Returns a Plain Value?

January 21, 2012

Pinal Dave

SQL Puzzle

SQL Datatype, SQL Function, SQL XML

This XML Data Type Quiz asks which xml method returns the customer name as an ordinary SQL value with a type you choose. The answer is value(). A tested script runs the methods on one order document and shows what each one returns.

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

Pinal Dave

SQL Puzzle

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.