Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Tips Part 2 Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2005 10:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/performancetipspart2.asp


Post #166442
Posted Thursday, March 17, 2005 2:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

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
    )
  )

 

Post #168534
Posted Tuesday, March 22, 2005 11:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 1:50 PM
Points: 141, Visits: 115

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

Post #169273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse