Introduction to Indexes

  • GilaMonster (7/4/2012)

    For seeking, for range scans, for supporting an order by.

    Oh, I didn't have the ORDER BY clause in mind yet. Thanks, it's more clearly now.

    So, if I my query returns the records in the descending order - it is better to create the index column also in the same order ("DESC"), right?

  • GilaMonster (7/4/2012)

    For seeking, for range scans, for supporting an order by. Not ever for returning rows in the index order without an order by specified. That was quoted a bit out of context (context being ordering of returned rows without an order by)

    Would it potentially make a difference to the amount of fragmentation caused by adding new items to the index? e.g. if your index key is a value that will generally *decrease*, would you get a less fragmented index by declaring it DESC?

  • Maybe. Will it be a big difference and will the reduction in fragmentation affect query performance? Depends on other factors (frequency of inserts, type of queries, etc)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail, Awesome as always.

    I have a small Confusion. Lets assume I have a table "Emp_details" with 4 columns-Emp_ID(primary key which creates clustered index by default), Name, Designation and Location and table has a million rows. 
    How does index works when i query table with where condition on "Name" column as shown below.

    SELECT * FROM Emp_details where Name='ABC'

    Thanks in Advance

  • The way you have it no index is used when this query is executed. You need an index on column Name

    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

Viewing 5 posts - 121 through 124 (of 124 total)

You must be logged in to reply to this topic. Login to reply