Re: IS NULL not working in WHERE clause.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 18:33:34 -0500

Earl,

  Is there no clustered index or primary key on the big table?
While that shouldn't matter, maybe it's a clue - it's not a great
idea, either, but I doubt you want to add one just now. I suspect
something is corrupt, and if you could find out the following,
it might help:

Look at the estimated execution plan for
  A. The bad query on the big table.
  B. The good query on the big table.

In particular, let me know which index is being used at
the start of the query (or indexes, if the query uses
separate indexes for separate parts of the where condition).

You can also try adding an index hint to see if that helps. If
the query plans are different, add a hint to mention the name
of the index used when the query works, probably

  from pmix_fact1 with (index (IX_pmix_fact1_6))

Also, if there is a parallel query plan, try adding the hint

  OPTION (MAXDOP 1)

after the order by clause.

Of course if you haven't run DBCC CHECKTABLE and can afford to, that's
probably something to do before fiddling.

Steve Kass
Drew University

Earl Newcomer wrote:

>The query with the between clause is the one that works. It is the other
>query that is not running consistently against different tables. The first
>query against a table with 277 million rows returns rows where the time_id is
>NOT NULL. The same query run against a table with 655,000 rows only returns
>rows where the time_id is NULL.
>
>"Aaron [SQL Server MVP]" wrote:
>
>
>
>>Put your BETWEEN clauses in parentheses, e.g.
>>
>>WHERE someting = smething
>>AND (some_COL BETWEEN something AND something)
>>AND something_else IS NULL
>>
>>Or, don't use BETWEEN at all.
>>http://www.aspfaq.com/2280
>>
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>>
>>
>>
>>"Earl" <Earl@discussions.microsoft.com> wrote in message
>>news:2C486DD9-BD80-46A6-979A-082457CD2029@microsoft.com...
>>
>>
>>>The following query returns rows where the time_id IS NOT NULL. This
>>>
>>>
>>query
>>
>>
>>>seems to be breaking SQL Server(?)
>>>
>>>select bus_date, time_ordered, time_id
>>> from [vicorp\gillic].pmix_fact1
>>> where bus_date = '2003-11-01'
>>> and time_id is null
>>>order by time_ordered
>>>
>>>
>>>The following query does NOT return any rows where the time_id is null.
>>>
>>>select bus_date, time_ordered, time_id
>>> from [vicorp\gillic].pmix_fact1
>>> where bus_date = '2003-11-01'
>>> and time_ordered between '2003-10-31 00:05:54' and '2003-11-05
>>>
>>>
>>13:38:11'
>>
>>
>>> and time_id is null
>>>order by time_ordered
>>>
>>>
>>>
>>>
>>
>>
>>

Relevant Pages

  • Re: Index View
    ... Please Give me the Hint How Can I Proceed? ... key and/or unique constraints in the tables used and the logic of the join ... Test the query (the select statement you gave will do for some simple ... I expect that you'll find that materializing the view is not the magic ...
    (microsoft.public.sqlserver.programming)
  • Re: Index not getting used
    ... > executing the query with concat hint but it has still not finished the ... > execution, mostly I think it is ignored, can confirm only after the ... Also regarding concat it will use "union all" ... Looking at the original query and the rule-based path, ...
    (comp.databases.oracle.server)
  • Re: Forcing index usage...
    ... I'm in a situation where oracle spatial doesn't use a domain index even if I use the index hint. ... But as soon as I add more predicates or nest the query into another one, ... Could you give us an example of the SQL ... Complete with execution plans. ...
    (comp.databases.oracle.server)
  • Re: How to list all queries only of a database in a combobox.
    ... "Hint: you could find this out for yourself if you change the ... WHERE clause to ... the name of a query, and see what Type it is." ... Prev by Date: ...
    (microsoft.public.access.forms)
  • Re: SQL tuning nightmare - db file sequential reads
    ... Your INDEX_FFS hint was probably a trick to make ... and I had no hint in my query. ... queries/reports to run fast, and in most cases these indexes did just ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.server)