MS SQL Server - Custer an index or not?

Author

Custer an index or not?

_Stephen

2006-03-22, 11:23 am

I see this as potential problem. I have Indexed columns for a Clustered
Index as follows:
Merchant_Account_ID
DateOfReport
Standard_Entry_Class
_Code

This would repage my index on the daily insert of say 300+ returns where
they are going in by Merchant + Data+ Code.

I am finding a few of these, not as many as I thought :)

I want to remove the clustered aspect of the index, and still leave it as
unique. This would increase my insert performance wouldn't it?

TIA

Tibor Karaszi

2006-03-22, 1:24 pm

> I want to remove the clustered aspect of the index, and still leave it as unique. This would

> increase my insert performance wouldn't it?

Likely. But insert performance will be even better if you can cluster on something which it
monotonically increasing (instead of finding free space using PFS and IAM pages, SQL Server just
navigates the index, find the "end of the table" and there the row goes). Perhaps DateOFReport is,
so you can specify this as the first column in the index?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"_Stephen" < srussell@electracash
.com> wrote in message news:uaNuXscTGHA.5856@TK2MSFTNGP11.phx.gbl...
>I see this as potential problem. I have Indexed columns for a Clustered Index as follows:
> Merchant_Account_ID
> DateOfReport
> Standard_Entry_Class
_Code
>
>
> This would repage my index on the daily insert of say 300+ returns where they are going in by
> Merchant + Data+ Code.
>
> I am finding a few of these, not as many as I thought :)
>
> I want to remove the clustered aspect of the index, and still leave it as unique. This would
> increase my insert performance wouldn't it?
>
> TIA
>

Doug

2006-03-25, 1:23 pm

yes, removing clustered indexes always increases speed for inserts, and
usually speeds up updates for the key fields.

Sponsored Links