SQL Server Where Clauses

Question

Hi,

When running a select query (on SQL SERVER 2000+) with multiple where statements such as

SELECT    * 
FROM      TABLE1
WHERE     TableId = @TableId 
          AND Column1 = @Column1 
          AND Column2 = @Column2 
          AND Column3 = @Column3 etc.

...does the query calculate each and every one of the where clauses even if the first one does not equate? I have a query which checks each of the columns to see if any changes have been made, before updating (i.e. it only updates if there are changes). If the query compares every column then I think I will need to rewrite the query. However, if the query engine first filters to the ID column and then compares each column individually within that filtered set the performance should be fine (I would have thought?).

I hope that makes sense.

Thanks, James.

Answer 1

No.

The query engine is pretty well optimised.

Answer 2

Based on statistics and available indexes, the optimiser will estimate which is the best order to compute the query. Most of the time it gets it right, but can be thrown off by out of date statistics.

You should not be concerned with the order of WHERE clauses. Rather, have a look at the actual execution plan created for the query.

Answer 3

why hit the data twice with something like:

--did data change?
if exists (select ... where pk=@pk and and and...)
begin
    update ... where pk=@pk
end

just do something like:

update ... where pk=@pk and and and...

you can check @@ROWCOUNT if you need to know if it actually changed and was UPDATEd

Answer 4

I dont know if this will be possible in your case but try this:

On the table/data set you are looking at, create a LastModified datetime default getdate() column. Then create a trigger for Update to that table. Make the trigger do the following.

On update set LastModified = getdate()

This is good database design, and you can index the LastModified column. This is a narrower index to maintain and your query can now be

UPDATE... WHERE LastModifed >= @LastRunDate

For help on triggers, try this Stack overflow thread: Trigger Firing