SQL Table Operation Archives - Page 10 of 12 - SQL Authority with Pinal Dave

A pottery rib shapes a clay vessel beside a trimmed clay strip and a newly attached clay handle

XML modify(): Inserting, Replacing and Deleting Nodes

May 11, 2012

SQL Function, SQL Table Operation, SQL Variable, SQL XML

The modify() method edits XML in place, but a path that matches nothing changes nothing and says nothing. Here is how to stay in control.

A harbor seen from above with a grid of mooring buoys, small boats tied to most of them and a few buoys floating empty.

Numbers Table vs GENERATE_SERIES: Building Rows on Demand

February 16, 2012

Pinal Dave

SQL Tips and Tricks

SQL System Table, SQL Table Operation, Table Partitioning, Temp Table

Learn numbers table in SQL Server with practical examples, clear checks, common traps, and guidance for your own database.

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.

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 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 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.

A long wooden peg board with a row of round pegs and one empty hole in the row, a red peg standing just past the gap.

Identity Column Quiz: Why Did My IDs Skip a Number?

January 4, 2012

Pinal Dave

SQL Puzzle

SQL Constraint and Keys, SQL Identity, SQL Table Operation, SQL Transactions

This Identity Column Quiz starts with three rows and one rolled-back insert, then asks what ID the next row gets. The answer is 5, not 4. A tested script proves it and shows two more ways numbers go missing.