Performance Tips Part 2

• Leo Peysakhovich

  Hall of Fame

  Points: 3973

  More actions

  March 8, 2005 at 10:33 pm

  #185484

  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

  More actions

  March 17, 2005 at 2:36 pm

  #547678

  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

  More actions

  March 22, 2005 at 11:20 am

  #548261

  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