Performance Tips Part 2

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

  • 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

        )

      )

     

  • 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 2 (of 2 total)

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