Index Creation Guidelines

Question

Index Creation Guidelines

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

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

TryHard SSC Journeyman Points: 95 More actions · Answer 1

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.

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 2

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.

Henmi Ten Centuries Points: 1015 More actions · Answer 3

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?

Thanks

Hendry

Sergiy SSC Guru Points: 110206 More actions · Answer 4

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

Brian Laws SSCommitted Points: 1556 More actions · Answer 5

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!

Rohit Nayak-223705 Mr or Mrs. 500 Points: 517 More actions · Answer 6

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?

Thanks

~Rohit

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 7

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

tymberwyld SSCertifiable Points: 7810 More actions · Answer 8

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)?

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 9

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.