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

What column to use for Cluster index Expand / Collapse
Author
Message
Posted Thursday, October 2, 2008 10:25 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:20 AM
Points: 111, Visits: 469
I have a table with over 10 million rows, it has all the PaymentHistory. It has ID as identity column, and EMPID with duplicate emp numbers. All the query, [joins], [where clause] are using EMPID. Which column should be created for CLUSTERED index? pls help thans in advance
Post #580048
Posted Friday, October 3, 2008 1:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:25 AM
Points: 929, Visits: 1,127
Generally, you'll get the most benefit of a (clustered) index if you add columns which are most used in your where clause.
In your case EMPLID. But Clustered indexes have to be unique, which is not in your case.
But, if you create a clustered index on a non-unique field, SQL will add a column to make this index unique.

So you have to compare the extra diskspace against the performance benefits.

You could also consider partitioning. A history table is a perfect candidate for that feature.


Wilfred
The best things in life are the simple things
Post #580095
Posted Friday, October 3, 2008 3:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Probably you can use database Engine tunning advisor and/or use Show Plan to find our more.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #580146
Posted Friday, October 3, 2008 4:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
A history table that isn't mostly queried by date?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #580159
Posted Friday, October 3, 2008 5:19 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
Wilfred van Dijk (10/3/2008)
Generally, you'll get the most benefit of a (clustered) index if you add columns which are most used in your where clause.
In your case EMPLID. But Clustered indexes have to be unique, which is not in your case.
But, if you create a clustered index on a non-unique field, SQL will add a column to make this index unique.

So you have to compare the extra diskspace against the performance benefits.

You could also consider partitioning. A history table is a perfect candidate for that feature.


Not necessarily - your clustered index should always* be monotinic. If as an extreme example, you're writing a Db for a web facing app using GUIDS even though you're going to be using the GUIDS for lookups and queries you DO NOT use them for your clustered index as the random nature of the GUID means that the new records can end up going anywhere in your table. This can lead to huge performance issues, page splits, pagelocks as everything is reordered in the table to place the row in the right physical position. You'd probably use insert datetime and cover the lookup using a covering index



* Well, no rules hold fast all the time. Sure someone'll think of a situation where this wouldn't be the case I'm sure
Post #580185
Posted Friday, October 3, 2008 5:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
Andrew Gothard (10/3/2008)

* Well, no rules hold fast all the time. Sure someone'll think of a situation where this wouldn't be the case I'm sure


Depends how fast the table changes. If inserts only happen occasionally (an employees table, or something like that), then the main reason for clustering on an ascending key (fragmentation) isn't that applicable.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #580198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse