Pinal Dave - SQL Server Performance Tuning Expert

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

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.

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.