I ran some of the queries on SQL 2000 and SQL 2005 [on the servers]. I ran it for 50 times only.

I got follow results:

SQL 2000 SP3:

time: 1

NOT IN :1376 ms

AND <> :1796 ms

derived UNION table LEFT OUTER JOIN : 453 ms

time: 50

NOT IN :58110 ms

AND <> :58800 ms

derived UNION table LEFT OUTER JOIN : 43703 ms

SQL 2005 SP1:

time: 1

NOT IN :1220 ms

AND <> :1173 ms

derived UNION table LEFT OUTER JOIN : 420 ms

time: 50

NOT IN :50236 ms

AND <> :53313 ms

derived UNION table LEFT OUTER JOIN : 21466 ms

My experiment also shows that "derived UNION table LEFT OUTER JOIN" is better than 'NOT IN" or "AND <>".....

Thanks,

Miriamka