Wednesday, January 24, 2007

EXISTS vs COUNT()

COUNT may be used in SQL to check if a record exists like for an example

IF(SELECT COUNT(CUSTOMER_ID) FROM TBL_CUSTOMER WHERE CUSTOMER_ID = 100) = 0

BEGIN /*LOGIC*/ END

BUT using the keyword EXISTS gives better performance THEN COUNT()

IF EXISTS(SELECT CUSTOMER_ID FROM TBL_CUSTOMER WHERE CUSTOMER_ID = 100)

BEGIN /*LOGIC*/ END

If in case the row count is needed @@ROWCOUNT can be used and also if only the first record is needed the TOP KEYWORD can be used

SELECT TOP 1 * FROM TBL_CUSTOMER

No comments:

Post a Comment