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
    tran008, Aug 12, 2009
    #2

  2. Luis Martin Moderator

    May be.
    What is the response time?. Good? Bad?
    Luis Martin, Aug 12, 2009
    #3

  3. FrankKalis Moderator

    Which version of SQL server are you running?
    FrankKalis, Aug 12, 2009
    #4

  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
    tran008, Aug 12, 2009
    #5

  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.
    FrankKalis, Aug 12, 2009
    #6

  6. preethi Member

    What is the clustered index on the table?
    preethi, Aug 13, 2009
    #7

  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.
    Luis Martin, Aug 13, 2009
    #8

Share This Page