Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: how to create index (Case when)

    How to create index when there is a SQL statement like
    Select count(1) as [Total],
    sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
    from Table1


    Thx in Adv
    XLDB

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Use one index on each column ( field 1 field 2 ) ...use nonclustered for each

    however, if table is small ( say less than 1000 row s ) SQL probably wont use indexes..it will scan whole table.

  3. #3
    Join Date
    Dec 2003
    Posts
    61
    the table has 2 million records

  4. #4
    Join Date
    Dec 2003
    Posts
    61
    actually, there are 2 similar tables that i use the query on...

    the table with 2 million records is taking 57 seconds
    the table with 40,000 records is taking 1 second

    there is only 1 index on "Field1" in "Case When" statement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •