October 2, 2008 at 10:25 pm
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:cool:
October 3, 2008 at 1:21 am
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
October 3, 2008 at 3:21 am
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."
October 3, 2008 at 4:32 am
A history table that isn't mostly queried by date?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 5:19 am
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
October 3, 2008 at 5:43 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply