Results 1 to 6 of 6

Thread: Useful Index

  1. #1
    Join Date
    May 2003
    Posts
    22

    Unanswered: Useful Index

    I hope someone can help me with some advice.

    I have a table with 4 million records. The table has a PK and clustered index on a ColA (which is an identity value). ColB is a non clustered indexed varchar (500). ColC is char(1) with a non clustered index.

    ColC has 17 distinct records out of 4 million.

    Selectivity Ration is defined by SR = 100 * (Rk / Rt) where Rk is the total selectivity of the key and Rt is the total records. I believe I read somewhere that if the SR is less than 15% sqlserver will use a scan. In this case the SR on ColC is practicaly zero.

    So, my question is; Is the index on ColC of any value?

    If I have a query: Select * from table where ColC = 'A' will use a full table scan.

    If I have a query: Select * from table where ColB like 'B%' and ColC = 'A'
    will the index on ColC be of any value?

    If I have a query: Select * from table where ColB like '%B%' and ColC = 'A' will the index on ColC be of any value?


    Any advice here would be very helpful. Thank you in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    1. most likely
    2. most unlikely, in this case you'd benefit from ColB+ColC composit index
    3. highly unlikely

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    '%B%'

    Scan baby Scan

    Disco inferno

    Scan baby Scan

    burn the mother down....

    Look up index intersection in BOL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    leading wildcards in like clauses will always return a table scan
    regardless of how many indexes you have.

    try creating full text indexes on the pattern matched columns

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Ruprect
    leading wildcards in like clauses will always return a table scan
    regardless of how many indexes you have.

    try creating full text indexes on the pattern matched columns
    So, where in SA?

  6. #6
    Join Date
    Mar 2004
    Posts
    4

    Re: Useful Index

    I would also remove the non clustered index from the varchar(500) column. You dont want to index wide columns.

Posting Permissions

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