SQL Server "Write Once" Table Clustered Index

I have a fairly unique table in a SQL Server database that doesn't follow 'typical' usage conventions and am looking for some advice regarding the clustered index.

This is a made-up example, but follows the real data pretty closely.

The table has a 3 column primary key, which are really foreign keys to other tables, and a fourth field that contains the relevant data. For this example, let's say that the table looks like this:

CREATE TABLE [dbo].[WordCountsForPage](
 [AuthorID] [int] NOT NULL,
 [BookID] [int] NOT NULL,
 [PageNumber] [int] NOT NULL,
 [WordCount] [int] NOT NULL
)

So, we have a somewhat hierarchical primary key, with the unique data being that fourth field.

In the real application, there are a total of 2.8 Billion possible records, but that's all. The records are created on the fly as the data is calculated over time, and realistically probably only 1/4 of those records will ever actually be calculated. They are stored in the DB since the calculation is an expensive operation, and we only want to do it once for each unique combination.

Today, the data is read thousands of times a minute, but (at least for now) there are also hundreds of inserts per minute as the table populates itself (and this will continue for quite some time). I would say that there are 10 reads for every insert (today).

I am wondering if we are taking a performance hit on all of those inserts because of the clustered index.

The clustered index makes sense "long term" since the table will eventually become read-only, but it will take some time to get there.

I suppose开发者_StackOverflow中文版 I could make the index non-clustered during the heavy insert period, and change it to clustered as the table becomes populated, but how do you determine when the cross-over point would be (and how can I notify myself in the future that the 'time has come')?

What I really need is a convertible index that crosses over from non-clustered to clustered at some magical time in the future.

Any suggestions for how to handle this one?

---

Actually, I would not bother with trying to have a non-clustered index first and convert it to a clustered one (that alone is a really messy affair!) later on.

As The Queen Of Indexing, Kimberly Tripp, explains in her The Clustered Index Debate Continues.., having a clustered index on a table can actually improve your INSERT performance!

Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing.

A heap is a table which has no clustered index defined on it.

Considering this, and the effort and trouble it takes to go from heap to a table with a clustered index - I wouldn't even bother. Just define your indices, and start using that table!