SQL Server Performance

indexes - best practice

Discussion in 'Getting Started' started by cjp, Aug 13, 2008.

  1. cjp New Member

    I have been reading web articles on best practice for building indexes and, to some extent, am puzzled: on the one hand, experts advise covering all fields that are likely to be used in big queries by incorporating them into an index; on the other hand, I am advised to keep indexes as small as possible. At some point, these two pieces of advice conflict with each other.
    Working in medical research means that I will often be building queries that not only require many (and diverse) fields but which, inevitably, require multiple joins - many fields would need to be indexed.
    If (say) I regularly use 8 fields in table x for joins, is it better to have one large, compound index containing all the fields or to have several smaller indexes? - My usual practice is to build a clustered index on practice id and patient id and then to build a non-clustered index for any other fields that I am likely to use: I seldom need to update or delete information, so am unlikely to fall foul of the situation where a change is contantly updating the index.
    By the way, is there any performance advantage is setting a Primary Key rather than a clustered unique index?
    All advice gratefully received.
    CJP
  2. FrankKalis Moderator

    [quote user="cjp"]
    I have been reading web articles on best practice for building indexes and, to some extent, am puzzled: on the one hand, experts advise covering all fields that are likely to be used in big queries by incorporating them into an index; on the other hand, I am advised to keep indexes as small as possible. At some point, these two pieces of advice conflict with each other.
    [/quote]
    Indexing is really more an art than an exact science. Both advices are valid and now it is up to you to find the right mixture of both for your particular situation. [:)]
    I have no experience in medical research and its specific needs, but I would tend to think that in such a situation I would add an IDENTITY column as PRIMARY KEY and put the other columns into a unique constraint or unique index
    A PRIMARY KEY is a logical construct, while an index is a physical one. Behind the scenes SQL Server implements a PRIMARY KEY with a unique index. This index doesn't necessarily also has to be the clustered index, but it commonly is when you don't change it explicitly. So, in short, there is no performance advantage.
  3. cjp New Member

    Many thanks for your advice - I will continue with the experimentation approach you tacitly recommend. However, I would like to ask you for one clarification.
    You suggest setting a PK on an identity column. I have encountered this recommendation before without really understanding it since it seems to entail adding a column of (in effect) unrelated data and then indexing this. Why would this be better than building a PK or unique index on the actual data provided in the table?
    Chris


  4. FrankKalis Moderator

    [quote user="cjp"]
    You suggest setting a PK on an identity column. I have encountered this recommendation before without really understanding it since it seems to entail adding a column of (in effect) unrelated data and then indexing this. Why would this be better than building a PK or unique index on the actual data provided in the table?
    [/quote]
    No, I'm not suggesting to always use a PK on an IDENTITY column. The choice for such a column would kick in when it comes to the physical implementation of the logical model. The choice between a single-column surrogate PRIMARY KEY and a multi-column natural key has to factor in performance, scalability, diskspace, etc... In many cases you will find that it is "cheaper" to add a surrogate then using the natural key. Still you could "preserve" the natural key by adding a unique constraints on that columns. Again, this is an "It depends" situation and, to a certain extent, also a "matter of taste" on behalf of the modeler.

    Do you know this blog: http://www.sqlskills.com/blogs/kimberly/ It has some very good articles on the use of IDENTITY columns and indexing in general.
  5. cjp New Member

    Thanks, Frank - I'm very grateful for your help.
    I was not aware of the Kimberly Tripp article on indexing but I will go off and read this now.
    By the way, I have also found another thread where you advise on indexing - so this is now part of my reading too.
    Chris

  6. FrankKalis Moderator

    Good luck, but please don't nail me down on what I've said the other day. [:D]
  7. satya Moderator

  8. cjp New Member

    Thanks, Satya. That is very helpful - it is good to have several resources I can reach to learn more about this topic.
    Chris


  9. rohit2900 Member

    HI...
    I'm having a small doubt as what all coulmns my index should cover and in what order. I'll explain my ques with below ex.
    I'm having two tables t1, t2 and I'm using below query to fetch the data.
    select t1.col1, t1.col2, t2.col1, t2.col2
    from t1
    inner join t2 on t1.col3 = t2.col3
    where t1.col4 = ... and t2.col4 = ...
    Now if I'm creating a covering index on these tables then what columns should I create index and which column I keep in include part.
  10. satya Moderator

    Rohit
    Do not post multiple (duplicate) questions on the same topic, please check your other post in this regard.

Share This Page