SQL Server Performance

Replace "Is not null" clause in "Where" to use Index.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by LucasDa, Jun 25, 2009.

  1. LucasDa New Member

    Hi.
    I have one question. I need improve one Query, this query have a clausule WHERE with fileds to behave against "IS NOT NULL". The question is, exist some way to replace the "is not null" to that the statement can use the Indexes?.
    For example:
    SELECT CampoA, CampoB, CampoC FROM Tabla
    WHERE CampoA IS NOT NULL
    AND CampoB IS NULL
  2. arunyadav Member

    One way is to create a covering index on CampoA, CampoB, CampoC columns.. Rest, if you could tell what kind of indexes are there on the table, it would be easier to suggest..
  3. FrankKalis Moderator

    Slight modification... [:)]
    I would create the index on CampoA and CampoB and have CampoC as included column.
  4. LucasDa New Member

    Thank for the response.
    I have been working on the Query but the performance not improve.
    My query is the follow:SELECTA
    .AccountID, A.ConcessionID, A.ContractNumber, A.Files, A.LastUpdateFile, A.ListStatus, A.DateTimeFrom, A.DateTimeTo
    FROM InteroperabilityAccountHistory A(NOLOCK)JOIN
    ForeignTollOperators B (NOLOCK)ON
    A.ConcessionID = B.ConcessionIDWHERE
    A.ConcessionID = 102AND
    A.ListStatus IS NOT NULL AND
    A.DateTimeTo IS NULL
    I created an Index on A.ConcessionID with A.listStatus and A.DateTimeTo as included column but the engine continue using the Primary Key. This Primary Key it's formed by the column A.AccountID. What can I do to improve the Query?
    Thank for All....!!!
  5. rohit2900 Member

    I you can post the complete table structure along with the index structure. and also provide below information
    1. No of records in this table.
    2. Expected no of records for this query.
    I'll suggest u to create composite index on (A.ConcessionID, A.ListStatus, A.DateTimeTo ) with A.AccountID, A.ContractNumber, A.Files, A.LastUpdateFile, A.DateTimeFrom in the included clause.
    As per my understanding your query is using PK as you are selecting most of the columns of this table.

Share This Page