Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Guideline with this index

    Kindly help in clearing the concepts:

    I've a table with more than 200 thousands records. there's a column named CLAIM(Primary key + clustered index).
    When I run DBCC SHOW_STATISTICS for the non-clustered index
    I get ALL DENSITY 8.009868E-6.
    If Density refers to the average percentate of duplicate rows in and index, how come the value is 8. There isn't any duplicate record for the CLAIM key.
    The statistics for the index is current. What does the high density value for the key depicts?

    Howdy!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Scientific notation... 8.009868E-6 is the same as 0.000008009868 so your density is very low.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Thus selectivity is very high, so you're in good shape
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Quote Originally Posted by rdjabarov
    ...Thus selectivity is very high, so you're in good shape
    Lucky me!

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    One more strange thing, folks:
    the column CLAIM is VARCHAR(8) and contains NUMERIC-VALUES. When i restrict the records using:

    WHERE CLAIM=123
    Execution plan doesn't use CLUSTERED-INDEX.
    when i use
    WHERE CLAIM='123'
    Execution plan uses INDEX-SEEK.

    Why is it so?

    I know it should have been INT rather than VARCHAR(8), but i can't change the datatype immediately. My question is, why won't the OPTIMIZER selects the index if quotes are not specified?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Because it does an implicity datatype conversion, and at that point it chooses not to use the index. In 6.5 it was guaranteed to be the case, in 2K "sometimes" the optimizer uses the ind3e3x even if the conversion is required.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by RDJabarov
    ...Thus selectivity is very high, so you're in good shape
    I love nothing more than to followin' you around and pickin' up your scraps

    selectivity would more likely refer to the amount of rows returned in a query based on the total number of rows in the table.

    since this question had nothing to do directly with a query, i suggest that
    density would be the more correct term as to defining the amount of duplicates in a column.
    ex Low density = mostly unique
    High density = mostly duplicated.

    phew my ocd is kickin' in

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Scott,

    You must have confused it with DBCC SHOWCONTIG.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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