Query Cost - Big diffrence

I have a table called test with five fields of which no is the primary
key
When I run the following query
select * from tt where no not in (23,76)
select * from tt where no !=23 and no!=76
The cost of first query is 0.53% and that of second is 99.47%
Why is this huge difference?
MadhivananIt's hard to offer any help without the exact table definitions and exact
query you are running. Is your table called tt or test?
In any case, the percentages you quote are estimates, and they
may or may not correspond to actual relative costs. My guess
is that the difference is the result of how the literal constants 23 and 76
are typed by the query processor. Assuming the column [no] is of
type INT, try
select * from tt where [no] != cast(23 as int) and [no] != cast(76 as int)
If this doesn't help, please post the CREATE TABLE statements and
the exact query, along with the query plans for each query.
Steve Kass
Drew University
Madhivanan wrote:

>I have a table called test with five fields of which no is the primary
>key
>When I run the following query
>select * from tt where no not in (23,76)
>select * from tt where no !=23 and no!=76
>The cost of first query is 0.53% and that of second is 99.47%
>Why is this huge difference?
>Madhivanan
>
>|||There are a few places in MSDN which make passing mention of the possibility
that SQL Server's query optimizer may not choose indexes efficiently when
the OR operator a negative comparisons (ex: != ) are used in a WHERE clause.
Look closely at the execution plan and see if index selection is different
between the two queries.
http://msdn.microsoft.com/library/d...
etchapt14.asp
http://support.microsoft.com/defaul...=kb;en-us;68470
You can explicitly tell the query processor which index to use by specifying
an index hint.
http://msdn.microsoft.com/library/d...r />
_8upf.asp
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1123770945.884995.22410@.g43g2000cwa.googlegroups.com...
>I have a table called test with five fields of which no is the primary
> key
> When I run the following query
> select * from tt where no not in (23,76)
> select * from tt where no !=23 and no!=76
> The cost of first query is 0.53% and that of second is 99.47%
> Why is this huge difference?
> Madhivanan
>