Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Index Creation Guidelines Expand / Collapse
Author
Message
Posted Sunday, October 23, 2005 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 18, 2007 1:15 AM
Points: 5, Visits: 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.

Post #231510
Posted Sunday, October 23, 2005 5:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259

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. 




Post #231512
Posted Friday, October 28, 2005 1:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 16, 2005 7:32 PM
Points: 59, Visits: 1
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



Post #233211
Posted Friday, October 28, 2005 1:50 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #233216
Posted Friday, October 28, 2005 8:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 06, 2012 2:48 PM
Points: 182, Visits: 104
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!
Post #233345
Posted Tuesday, June 12, 2007 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 21, 2013 10:26 AM
Points: 25, Visits: 90

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

 

 

 

 

 

Post #373166
Posted Tuesday, June 12, 2007 7:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
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


Post #373336
Posted Monday, September 17, 2007 7:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268
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)?



Post #399656
Posted Tuesday, September 18, 2007 5:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259

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.




Post #400144
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse