Two sessions race for 2,000 rows: the check-then-update version leaves 254 rows with two winners, and the one-statement version leaves 2,000 rows with exactly one winner each

Record Checkout in SQL Server: A Visible Lock With an Expiry Time

Record checkout puts the name of the editor and an expiry time on the row, so a screen can show who has it. This post builds it with one UPDATE and proves it with two sessions that race for 2,000 rows. A check-then-update version gives some rows two winners, and the one-statement version never does. It also covers expired locks, late saves and check-in. Every result comes from SQL Server 2025.

Read More
Bar chart of rows written for one move on a list of 1,000 items: moving the last item to the top writes 1,000 rows when renumbering, 1,000 when shifting and 1 with gaps; moving item 500 to position 100 writes 1,000, 401 and 1; moving item 10 up one place writes 1,000, 2 and 1

Reordering a List in SQL Server: Move One Row, Write One Row

Reordering a list in SQL Server means changing a sort key, and the best design writes the fewest rows. This post compares three designs for dragging an item to a new place: renumber everything, shift the rows in between, and leave gaps. Each one is measured by rows written on a list of 1,000 items. Every result comes from SQL Server 2025.

Read More
Two scripts run twice: the plain script raises error messages 2705, 1913, 2714, 1750 on run 2 and changes the Fern price from 11.00 to 12.10, leaving 6 different rows; the guarded script raises no errors, changes 0 rows and leaves 0 different rows

Schema Snapshot: Prove a Deployment Script Is Safe to Run Twice

A schema snapshot records a table’s columns, indexes, constraints and rows, so you can prove a deployment script is safe to run twice. This post runs a plain script twice and watches a price change again. It then guards each step, runs the guarded script twice, and shows that the two snapshots match. Every result comes from SQL Server 2025.

Read More
Two sessions in a deadlock: Session A updates Tea then Honey, Session B updates Honey then Tea, each waits for a lock the other holds, Session A gets error 1205 and retries, and the saved run shows try 1 error 1205 after 3,939 ms and try 2 committed with Tea 98 and Honey 102

Retrying a Deadlock Victim: When the Retry Helps and When It Hurts

Retrying a deadlock victim works when the retry repeats the whole transaction. This post builds a bounded T-SQL retry for error 1205 and tests it against a real deadlock between two sessions. It then shows two cases where a retry does harm: inside an outer transaction and on an error that is not a deadlock. Every result comes from SQL Server 2025.

Read More