Last Line of a SQL Script: The Joke Every DBA Has Lived

Back in 2007 I shared a funny photo of a food label where everything looked normal until the very last line of the ingredients. The last line of a SQL script works the same way. Everything above it looks fine, and the ending decides how your afternoon goes.

Why the Last Line of a SQL Script Matters

We read a script from top to bottom, but we judge it by how it ends. The top is where I write the careful comment with my name and the date. The bottom is where I was tired and wanted lunch.

Most of the scripts that ever hurt me were fine in the middle. The trouble was in the last line of a SQL script, or in the line I forgot to write there.

The Missing COMMIT

You run BEGIN TRANSACTION, update a few rows, check the result, feel proud, and go to lunch. The transaction is still open. Every row you touched is still locked, and your colleagues start seeing blocking. When they follow the blocking chain, it leads straight to your lunch plate.

Before you walk away from any query window, ask it one question:

SELECT @@TRANCOUNT AS OpenTransactions;  -- 0 means nothing is waiting for you

If the answer is 1 or more, you still owe SQL Server a COMMIT or a ROLLBACK. When you close an SSMS window with an open transaction, it asks what to do. Read that box. Do not click the first button out of habit.

The Highlighted Half

SQL Server Management Studio runs only the selected text when something is highlighted. That is a great feature until you select the wrong half.

DELETE FROM dbo.Orders
WHERE OrderId = 42;

Highlight only the first line and press F5, and SQL Server deletes every order in the table. It has no idea the WHERE clause is sitting one line below, feeling left out. Here the last line of your selection matters more than the last line of the file.

Make the Last Line of a SQL Script a Check

My habit for any script I hand to someone else: the last line of a SQL script is a check, not an action. The script proves what it did before it says goodbye.

SET XACT_ABORT ON;

BEGIN TRANSACTION;

UPDATE dbo.Orders
SET Status = 'Shipped'
WHERE OrderId = 42;

IF @@ROWCOUNT <> 1
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, 'Expected exactly one row. Nothing was changed.', 1;
END

COMMIT TRANSACTION;

SELECT @@TRANCOUNT AS OpenTransactions;

SET XACT_ABORT ON at the top means a runtime error rolls back the whole transaction instead of leaving half of it behind. The row count check stops the script when the WHERE clause matched more (or fewer) rows than you expected. The final SELECT confirms nothing is left open.

Related reading on this blog: UPDATE Without WHERE Clause: The Day Everyone Got a Raise and Transactions and Variables: SQL in Sixty Seconds #150.

Read the last line before you press F5. Then read it again, slowly, like a food label.

A script is not finished when it runs, it’s finished when the last line proves it worked.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.