Index Creation Guidelines

  • Does anyone have any input on the indexing rules when using a GUID primary key column?

    My understanding is that using a clustered index on this is not a good idea, for the same reasons as not using a clustered index on a name field. So a non clustered index is the other alternative. So then if there are no other great candidates for a clustered index, why would you create a column with sequencial number just so you can have a clustered index as suggested by others. I can't see the benefit in this.

    One final point. The article is a general guide which seems quite valid. One major point that I believe has been mmissed by all is to considere the ratio of updates/inserts/deletes to selects. This should have a major impact on your decision making process. An extra couple of milliseconds on an insert due to an index recalculation is insignificant compared to a second on a select.

  • Your last point is valid.

    "why would you create a column with sequencial number just so you can have a clustered index as suggested by others. I can't see the benefit in this"

    The benefit of clustered index in this case in maintenance and storing the data on pages with lower fragmentation level. 

  • I have a question about the composite index.

    Should a composite index be in the same order as it appears in the WHERE clause?

    For example, the composite index is (address_id, address_type) and my WHERE clause is like this: address_type=xxx AND address_id =1.

    Does the order like that affects the usage of composite index?



  • No, it does not.

    But in composite index it's better to have strongest restriction first.

    In your case index (address_id, address_type) will be used much more likely then (address_type, address_id).

    Of course, it depends. I'm talking about typical situation, but don't forget to use your brain.

    Code for TallyGenerator

  • I think that the decision of the column order in the composite index should also be informed by the other indexes on the table.

    If the column is already in its own index or the first in another index, then I'd consider using a different column as the first in the new composite index. That way, the query optimizer can make use of the two index and make an intersect them in other queries. For example, if there already is an index on address_id, then I would try using (address_type, address_id). With the address_type first, the query analyzer could now use that index when address_type is in your where clause. This _may_ increase performance of other queries. However, you will definitely have to test and see if the trade-off is worth it. The (address_id, address_type) may have a bigger payoff than the queries with address_type in the where clause.

    I'm not really sure about what benefits a composite index would give over 2 indexes. Wouldn't an index intersection of separate address_id and address_type indexes be as effective as the composite (address_id, address_type)? If not in a single query, it may be more beneficial overall (since other queries can use the two, separate indexes). I could use some clarification on this. Thanks!

  • Consider a table like

    create table example(

    tx_id int,

    product_id int,

    fiscal_yr_id int,

    amount decimal


    According to business rules, the uniqueness constraint is defined as (tx_id,product_id,fiscal_yr_id) ; so naturally they are candidates for the composite primary key.

    Also assume that all of the queries would be exact queries i.e. where tx_id=--- and product_id=--- and fiscal_yr_id =-- i.e. no range queries and that  the selectivity of the fiscal_yr_id is low i.e. say  that it only contains two choices 2006,2007

    In cases like these, are there alternatives to having the clustered index pk with (tx_id,product_id,fiscal_yr_id) that might offer better performance?








  • 3 columns are forming the key - tx_id,product_id,fiscal_yr_id. You should look at selectivity of the first and combination of first and second column. If this selectivity is right you should not worry about third column. We are talking about complete index selectivity

  • Question,

    In SQL Server 2005, is it still necessary to create indexes for Foreign Keys? I was under the impression in SQL 2000 and previous that a Foreign Key is merely a Constraint and not really an Index. So, if I have a Foreign Key which I know is NOT highly selective, should I also put an index on it (say FK_IX_OrderDetails_Orders)?

  • It all depend from the data analysis. In most cases, if table is not lookup table, it is not a bad idea to have index on FK column(s) because it will be used for a join between tables. But there will be small number of situations where this advise is not correct.

Viewing 9 posts - 31 through 38 (of 38 total)

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