Steve Kass

How to convert Books Online ms-help: links to MSDN Library http: links [bookmarklet included]

skass — Wed, 05 Aug 2009 03:11:00 GMT

As you know, Books Online is available in two convenient locations. It's online as part of the MSDN Library, and it's offline as a Document Explorer help collection. Topics are accessible by URIs in each, but the URIs don't match. For example: Books Online...(read more)

DELETE FROM Where?

skass — Thu, 30 Apr 2009 21:48:00 GMT

For years, SQL Server has supported a (second) FROM clause in UPDATE and DELETE statements. Its behavior isn't always deterministic, a fact Microsoft points out in the documentation.

Today, someone was surprised by the basic semantics of DELETE .. FROM. This probably happens a lot, but fortunately today's surprisee posted this Connect item because the following statement didn't do what the documentation seemed to say it would:

DELETE FROM t2
FROM t1 AS t2

Quick now, what table does this truncate?

You're in good company if you said t2, which is the wrong answer. The statement truncates t1, however, not t2. In fact, this DELETE statement contains no reference to the table t2, assuming there even is one. Here, t2 is only an alias, and the statement is semantically identical to

DELETE FROM ThisIsAnAliasNotATable
FROM t1 AS ThisIsAnAliasNotATable

When there are two FROM clauses in a DELETE statement, the first FROM clause is interpreted in the context of the table source defined by the second one, which is analogous to how SELECT works. The following query selects rows from t1, not t2, and that's no surprise:

SELECT t2.a
FROM t1 AS t2

If you think about it, you should agree that different behavior for DELETE would be very bad. If SQL Server produced what Vitaliy expected, someone could create tables with names used somewhere as aliases, and cause UPDATE and DELETE statements to stop updating the tables they were supposed to update. What makes the DELETE behavior more confusing than the SELECT behavior is the fact that in the case of DELETE, the keyword FROM can be used twice. It you write DELETE .. FROM statements with two FROMs, remember that the bottom FROM clause is the "outer" one in the sense of scope. It should be considered first, as is clearer in this DELETE statement, which is equivalent to the one that suprised Vitaliy:

WITH t2 AS (
SELECT * FROM t1
)
DELETE FROM t2

For the record, this CTE-based DELETE is just as non-standard as the DELETE .. FROM, but it's less confusing. If you can't easily (or effeciently) avoid the use of DELETE .. FROM, consider rewriting DELETE .. FROM as a CTE to reduce the chance of confusion.

It's 2008, and @@ROWCOUNT ain't what it used to be. Don't use it in triggers.

skass — Wed, 29 Apr 2009 04:57:00 GMT

During SQL Server 2008 beta testing, Aaron Bertrand noted that the value of @@rowcount inside a trigger could be unexpected, if the triggering statement was MERGE.

The consequences of this can be pretty bad, but fortunately there's a simple workaround. You need to do something if anyone might invoke MERGE against tables with triggers that contain @@rowcount checks

A MERGE statement can cause as many as three triggers to fire. Within each of them, the value of @@rowcount is the number of rows affected by the entire MERGE statement, i.e. the total number rows inserted, updated, or deleted by the various merge clauses.

Books Online mentions this in the article on MERGE: When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

Elsewhere, however, Books Online continues to give old advice that as of 2008 is not good advice. For example, in the article Multirow considerations for DML triggers, Books Online says, For example, the @@ROWCOUNT function can be used in the logic of the trigger to distinguish between a single and a multirow insert.

Not so, I'm afraid. The value of @@rowcount in an INSERT trigger, say, will always be at least equal to the number of rows inserted, but it can be greater. For example, a MERGE statement could have an INSERT action that doesn't occur, but an UPDATE one that updates 3 rows. The INSERT trigger will be called, because the MERGE statement's INSERT section "inserted zero rows."

Here's an example from AdventureWorks2008 to show what can go wrong.

I've added a GrandTotal table containing one row and column. GrandTotal.gt is supposed to keep track of the grand total of all purchase SubTotals.

This code creates the table and gives it a value that's initially correct for the data in AdventureWorks2008:

USE AdventureWorks2008;
GO
CREATE TABLE GrandTotal(
gt DECIMAL(18,2)
);
INSERT INTO GrandTotal;
SELECT sum(SubTotal);
FROM Purchasing.PurchaseOrderHeader;
GO

This code creates a trigger to update the grand total whenever new line items are inserted into the table:

CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT > 0
UPDATE GrandTotal SET
gt += (SELECT SUM(LineTotal) FROM inserted);
GO

And this MERGE statement (enclosed in a rolled-back transaction here so that no data in the database is modified by this test script) has the unintended result of updating the grand total from $63,791,994.84 to NULL.

begin tran
select * From GrandTotal;
merge into Purchasing.PurchaseOrderDetail as P
using (
select 2,'20010715',100,318,100,318,0,'20090101'
) as T(PurchaseOrderID,DueDate,OrderQty,ProductID,UnitPrice,ReceivedQty,RejectedQty,ModifiedDate)
on T.PurchaseOrderID = P.PurchaseOrderID
when matched then
update set ModifiedDate = T.ModifiedDate
when not matched by target and PurchaseOrderID < 0 then
insert (PurchaseOrderID,DueDate,OrderQty,ProductID,UnitPrice,ReceivedQty,RejectedQty,ModifiedDate)
values (3,'20010715',100,318,100,318,0,'20090101');
select * From GrandTotal
rollback tran;

Oops.

MERGE is only beginning to get used, and for a variety of triggers, a too-large @@rowcount won't cause a problem. But I think the number of people who might get caught by this problem will grow.

Don't use @@rowcount in triggers, unless you're certain it doesn't cause problems if it's higher than the real rowcount you want (which might be zero). As an alternative, Itzik suggested to me this (for INSERT; the others are similar):

declare @rc int;
with Two as (select top (2) *from inserted)
select @rc = count(*) from Two;

The value of @rc will then be 0, 1, or 2, depending on whether 0, 1, or more than 1 rows were inserted.

Steve Kass
http://www.stevekass.com