Performance Tips Part 2

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/performancetipspart2.asp

  • PW-201837

    SSC-Insane

    Points: 20805

    Re: Situation 6

    I don't get the use of variables and table type variables for what is essentially a set-based validation:

    IF EXISTS (

      SELECT external_item_cd

      FROM inserted

      WHERE external_item_cd IS NOT NULL

      GROUP BY external_item_cd

      HAVING COUNT(*) > 1

    )

    BEGIN

        RAISERROR('Duplicate Records', 16, 1)

        rollback

        Return

    END

    Same with the 2nd step, why count to compare counts, when all you need to test for is existence

    IF EXISTS  (

        SELECT 1

        FROM Item As t1

        INNER JOIN inserted As i

          ON ( i.external_item_cd = t1.external_item_cd )

        WHERE EXISTS (

          SELECT 1

          FROM Item As t2

          WHERE t2.external_item_cd = t1.external_item_cd

          AND   t2.ItemID <> t1.ItemID

        )

      )

     

  • Suresh Kumar Maganti

    Default port

    Points: 1429

    To start with, the idea seems to be to prevent duplications. But the trigger's code suggests that only duplications are to be allowed. Something wrong in the code.


    Kindest Regards,

    M Suresh Kumar

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply