SQL Server Performance

index tunning...

Discussion in 'T-SQL Performance Tuning for Developers' started by tran008, Aug 12, 2009.

  1. tran008 New Member

    Currently I have the index on the id for the following select statment:
    SELECT id , (CASE
    WHEN XTYPE = 2 AND XERROR_TYPE =9999 THEN -1
    WHEN XTYPE = 1 AND XERROR_TYPE =9999 THEN -1
    ELSE 0
    END) AS DamageShare


    FROM table1 where id between 1 and 100000.
    Do I need to add index on Xtype and Xerror_type as well?
    thanks
  2. Luis Martin Moderator

    May be.
    What is the response time?. Good? Bad?
  3. FrankKalis Moderator

    Which version of SQL server are you running?
  4. tran008 New Member

    This is sql 2000.
    If I do the following,
    select id from table where id between 1 and 8000 I have index seek, however with the case in, I get table scan.
    thanks
  5. FrankKalis Moderator

    [quote user="tran008"]
    This is sql 2000.
    If I do the following,
    select id from table where id between 1 and 8000 I have index seek, however with the case in, I get table scan.
    [/quote]
    On 2005 you could have used the INCLUDE clause, however on 2000 the only chance you have is to add the 2 columns to the index to get a covering index of the query. This widens the index, but from looking at your CASE expression I would guess the impact is not all that significant.
  6. preethi Member

    What is the clustered index on the table?
  7. Luis Martin Moderator

    Hello anybody in home???
    Is response time bad or what?.
    I asking you because your question i "Do I need...." depends on many factors.
    Time response, how many rows in your table, etc.

Share This Page