Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dsteinmetz
Starting Member
6 Posts |
 Posted - 2011-03-22 : 11:24:45
|
| We currently have a table that has 11 columns as part of the clustered index while there are 50 columns total. There are no non-clustered indexes on the table currently. Obviously those 11 columns make each row unique. This table gets records inserted into it many times a month with each insertion being maybe 50-200 rows. Right now i think we have a fast response on reports that use this table but perhaps the insertions are slower simply for the fact the new records may go in between already existing ones and thus causes page fragmentation.My question is, would it be better to create just one identity field as the clustered index and then a non-clustered index that basically uses the same columns as the current clustered index. This would in theory speed up the inserts as everything would go to the end, and I will still be getting at least the same performance with the non-clustered. Is that a true statement? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-22 : 11:49:36
|
| Very likely, though not 100% certain. You'll have to test out the scenario in a test environment.--Gail ShawSQL Server MVP |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-22 : 12:11:46
|
| Also, if you do that, make the 11 column index unique. |
|
|
|
dsteinmetz
Starting Member
6 Posts |
Posted - 2011-03-22 : 12:53:59
|
| Actually, now that I think about it, does it really matter? Won't the overhead to maintain the original clustered index be the same as the overhead to maintain the new non-clustered index and thus be a wash. So all I'm really getting is a larger file size because of an extra index? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-22 : 13:19:41
|
quote:
Originally posted by dsteinmetz
Actually, now that I think about it, does it really matter? Won't the overhead to maintain the original clustered index be the same as the overhead to maintain the new non-clustered index and thus be a wash. So all I'm really getting is a larger file size because of an extra index?
Maybe. How many nonclustered indexes are on that table? The 11 column clustering key will be present in each and every one of them.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Clustered Index vs Non Clustered Performance