THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Steve Kass

SQL MVP since 2002 Professor of Mathematics and Computer Science at Drew University

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

    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:

    A while ago, another MVP said it would be nice for "local" Books Online to have a "find this page on the web" link. Local Books Online is usually faster and more convenient, but for blog, newsgroup, or forum posts, it makes more sense to paste in MSDN Library links, so they're clickable from the reader's browser. Alan Brewer of the SQL Server Documentation Team provided a helpful tip: MSDN will understand the GUID from the local Books Online link and redirect your request.

    In other words,  

    Books Online Topic: CREATE STATISTICS (Transact-SQL)
    Document Explorer URI: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/b23e2f6b-076c-4e6d-9281-764bdb616ad2.htm
    Alternate MSDN Library URI: http://msdn.microsoft.com/en-us/library/b23e2f6b-076c-4e6d-9281-764bdb616ad2.aspx

    So I wrote this Javascript applet that works from inside local Books Online to put the Alternate MSDN Library URI on the Windows clipboard:

    javascript:(function(){window.clipboardData.setData("Text","http://msdn.microsoft.com/en-us/library/" + location.pathname.slice(-40,-4) + ".aspx");})(); 

    I also found a handy place to install it. I set it as Document Explorer's "Search Page" URL, which you can specify from local Books Online under Tools->Options. There was already a toolbar button in Books Online for Web Browser Search (a globe with binoculars). Now when I click on it, an MSDN Library URL of the Books Online article I'm reading is on my clipboard, ready to paste anywhere. (I don't use Books Online for web browser searches, and I'm guessing neither do you, so nothing is lost by redefining this button.)

    Pretty cool, I thought. Until Alan quickly added that MSDN doesn't always understand and correctly redirect the URL-with-GUID. In particular, not for local Books Online pages that describe the managed API. So I did some quick and dirty guess-and-test bookmarklet development, and this is what I ended up with. Based on limited testing, it works on "regular" SQL Server topics as well as managed API ones.

    javascript:(function(){var s = document.getElementsByTagName('head').item(0).innerHTML; var p = s.search("AssetID"); s = s.substr(p+16); p = s.search("\""); s = s.slice(0,p); p = s.search(":"); if (p>-1) s = s.substr(p+1); window.clipboardData.setData("Text","http://msdn.microsoft.com/en-us/library/" + s.toLowerCase() + ".aspx");})(); 

    The bookmarklet is a Javascript program, so you could enhance it further to do all kinds of useful things. For example, clicking on the globe could raise a dialog where you pick a language (to replace en-us in my bookmarklet), and copy the language-specific URL to the clipboard.

    With luck, this bookmarklet will be useful to a few readers, not to mention do some bookmarklet evangelism.

     

     

     

     

  • DELETE FROM Where?

    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.

    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

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement