Perhaps I am 'breaking in' Aaron's bad habits series of blogs or Alex's defensive programming but I would like to share with you the style I have seen recently on the client's side and how it affects perfromance.
There is long lines stored procedure that has many DML operations but I have seen that repeated many times within the stored procedure.
Test
table has a clustered unique index created on c column and has 2 million rows.
DECLARE
@par1 char(1)='A'
DECLARE
@par2 INT=10
DELETE
FROM Test WHERE
c
>20000 AND c<60000
AND
@par1<>'B' AND @par2>9
If you turn on an actual execution plan you will see that SQL Server uses CI index SEEK to get to the range and intresting , Filter operator to check variables and also Sort operator to select the first few rows based on a sort order.
On the other hand running the below you will see only CI Delete operator only. And running then secod one takes on my laptop slightly faster.
DELETE
FROM Test WHERE
c
>20000 AND c<60000
I would like to hear from you friends how you deal with such programming styles and how it affects performance at your company. Would it be much better to write IF..ELSE block to check for deletion which I proposed to the client in that case?