How will SQL Server perform this query behind the scenes

Question

SELECT 
 *
FROM 
 myTable
WHERE 
 field1 LIKE 'match0' AND
 myfunc(t1.hardwareConfig) LIKE 'match1'

Here is my question,

the matching of field1 is fast and quick, but myfunc takes forever to return and I want to make sure that if field1 doesn't match that it doesn't even attempt to do myfunc.

Will SQL just know this or can I make it explicit in my query?

I'm on MSSQL 2000, 2005 and 2008, hopefully there's a common answer.

Answer 1

There's a lot that goes on behind the scenes to determine how a query is executed, but in this situation I would expect for it to filter on the field1 first. Note that this isn't a guarantee - the query optimizer can make some strange choices now and then. Additionally, you can improve your chances by using the '=' operator rather than the 'LIKE' operator.

Answer 2

mysp can't be a stored procedure. It has to be a user defined function (which you should have qualified with its schema name, by the way). A user defined function is restricted to be side-effect free and should adhere to some rules. Essentially, SQL Server will combine the query and execute it as a single module with a single execution plan. It's not procedural function calling as you might expect. SQL describes what to do, not how to do it and the query optimizer will generate a plan that responds to your complete query, not each part of it separately. The same is true for views.

Answer 3

In SQL Server Management Studio (for 2008 and I think 2005), you can tell it to show you the execution plan. Press Control-M in the query window. Then execute the query. That will show you the detail.

Answer 4

To enforce the order in which conditions are evaluated, use the following approach, because it is documented that CASE preserves the order in which conditions are evaluated.

SELECT 
        *
FROM 
        myTable
WHERE 
  CASE WHEN field1 LIKE 'match0' THEN 
    CASE WHEN myfunc(t1.hardwareConfig) LIKE 'match1' 
      THEN 1 
    END 
  END = 1

The following article explains it in good detail: Predicates in SQL

Answer 5

Is myfunc just a complex calculation or does the return value change frequently. I am wondering because if it doesn't change that often and this is a frequently executed query, it may be better design to have an after insert trigger calculate the value and store it in the table as a separate column. Then you don't have to worry about which order things are evaluated in. The query may even run faster, especially if you have an index on both columns.