Order of conditions in a query

I have a query with many (approximately, 30) conditions, such as:

select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )

My question is:

In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.

The question is, is there the same rule in T-SQL?

I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?

This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...

By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.

[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?

We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]

Could someone kindly advise me on this, please?

Thank you ever so much.

The way you have that written, can't you do a right join and omit the IS NULL condition?|||The order the WHERE clause is processed is "undefined" and "not guaranteed". So don't try to write something that is based on process order.

IS NULL is very fast compared to varchar equals. But the optimizer is going to "pick" how to best process the query and may not do it the same way every time.|||

The way you have that written, can't you do a right join and omit the IS NULL condition?

I'd love to, but it's an "either-or" from a business logic, and I'm not returning a set of rows, - this query is actually a part of the matching logic (a Notify function) for a Notification Services application. I'm sorry, I should have mentioned that.

Thank you.

sql

order by

There is a field called field1 in a table called table1
Field1 is varchar(50)
Data inside field1 is such as:

3T
apr 2007
feb 2001
jan 2001
dec 1999
...

I am writing a sql query to return the data so that the field1 is sorted:
ie:

3T
dec 1999
jan 2001
feb 2001
apr 2007
...

This is what I have in the order by clause:

cast('01-' + replace(field1, ' ', '-') as datetime)

The problem is if there is something else such as '3T' or another text inside this field1.
Should there be a case statement inside the oprder by clause?

Please note that if there is any text which does not seem to be a date then it should appear first in the list as shown in the example above.
Thanks

Code Snippet

select'3T'as data

into #t

union allselect'feb 2001'

union allselect'apr 2007'

union allselect'dec 1999'

union allselect'jan 2001'

selectcaseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

from #t

orderbyisdate(data),

caseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

|||

Use the following query..

Code Snippet

Create Table #data (

[Col] Varchar(100)

);

Insert Into #data Values('apr 2007');

Insert Into #data Values('feb 2001');

Insert Into #data Values('jan 2001');

Insert Into #data Values('dec 1999');

Insert Into #data Values('Americas');

Insert Into #data Values('APJ');

Insert Into #data Values('EMEA');

Insert Into #data Values('1982');

Insert Into #data Values('AJP09837');

Select * From #data Order By

Case When Isdate(Col)=1 Then 1 End,

Case When IsDate(Col)=1 Then cast(Col as datetime) End,

Col

drop table #data