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

A long wooden fence across a meadow with one red post marking the boundary between two fields.

Date Functions Quiz: What Does DATEDIFF Count?

January 28, 2012

SQL Datatype, SQL DateTime, SQL Function

This Date Functions Quiz asks what DATEDIFF returns for two dates one day apart, when the unit is year. The answer is 1, because DATEDIFF counts boundaries crossed. A tested script shows an age calculation that works, plus DATETRUNC and EOMONTH.

A soap bubble floating above a small red bubble wand on a table by a window.

Common Table Expressions Quiz: Why Did My Second Query Fail?

January 27, 2012

Pinal Dave

SQL Puzzle

CTE, SQL Coding Standards, SQL Error Messages

This Common Table Expressions Quiz defines one CTE, reads it once, and then reads it again. The answer is error 208, because a CTE lives for one statement only. A tested script shows the error, the missing semicolon trap and the recursion limit.

A garden entrance with a red bar closed across it between two stone posts, a path continuing beyond.

Policy Based Management Quiz: Which Mode Can Stop a Change?

January 26, 2012

Pinal Dave

SQL Puzzle

Best Practices, Policy Management, SQL Coding Standards, SQL Server Configuration

This Policy Based Management Quiz asks which evaluation mode can stop a bad change before it is saved. The answer is On change: prevent. A tested T-SQL script blocks a badly named table and shows how few facets support that mode.

Two wooden bookshelves side by side, filled with plain books, a red book at the end of one shelf.

Fill Factor Quiz: Is 0 Different from 100?

January 25, 2012

Pinal Dave

SQL Puzzle

Clustered Index, SQL Data Storage, SQL Index, SQL Performance

This Fill Factor Quiz asks whether an index with fill factor 0 leaves more free space than one with 100. It does not: both build full pages. A tested script shows how a fill factor of 70 changes the pages and why it only applies at build time.

Two hourglasses on a desk by a window, one tied with a red ribbon, beside a loose coil of rope.

Accelerated Database Recovery Quiz: Why Did the Rollback Finish at Once?

January 24, 2012

Pinal Dave

SQL Puzzle

SQL Performance, SQL Server 2019, SQL Transactions, Transaction Log

This Accelerated Database Recovery Quiz asks how long a rollback takes when ADR is on. The answer is almost no time, and a tested script measures the same rollback with ADR off and on. It also shows what ADR costs.

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

Pinal Dave

SQL Puzzle

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.