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
[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.
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
[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.
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
May I jump to refer http://sqlblogcasts.com/blogs/ssqan...r-2005-index-optimization-best-practices.aspx (my own exp.)(
Thanks, Satya. That is very helpful - it is good to have several resources I can reach to learn more about this topic. Chris
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.
Rohit Do not post multiple (duplicate) questions on the same topic, please check your other post in this regard.
