Mike,

I will read-up on BOL on the clustering - I guess I need to get more in the habit of looking at the docs. It's just too easy to ask up here on th thread! <bg>

On fillfactor, though - a question.

Is the "proper" fillfactor also a product of the nature of the index you are creating? For example, lets say I have an invoice table with a primary surrogate key and a foreign key to my customer table. Now, since the surrogate key is "tight" in that no values will ever get inserted "between" existing values, can I use a higher fillfactor for an index on that key - as opposed to my customer fkey which will have lots of values inserted "between" existing entries? It seems to make sense to me, but I may be misunderstanding how the indexes are working.

Thanks!
KEn


>Ken,
>
>See inline...
>
>>1) How does one determine the best field (or fields) to build the clustered index on? Examples would be of great help to me here.
>
>This is a hard question to answer. I hate to do this to you but the SQL Server Books Online covers this topic really well. Check out the topic Using Clustered Indexes.
>
>You should also get a copy of Inside SQL Server 7.0 by Kalen Delaney. It also coveres the topic. This is a must have!
>
>>2) I was told by "someone who knows" that fillfactors should range between about 50 and 90 - and be based on "how much inserting, updating occurrs" so that heavily inserted tables such as "line items" might be set at 50 while a near static table might get set at 90. Does this sound like good advice?
>
>Yes. Remember that the fillfactor determines how full SQL Server fill the leaf nodes of indexes. The idea is to leave some empty space for the index to grow into. If no space is avaible, SQL Server will have to split one of the leaf nodes into two nodes to create space. Page splitting is a very expensive operation.
>
>You will have to experiment with the exact fill-factor to use. In general, the more volitle the table, the more empty space you want in the leaf nodes, which means a smaller fill-factor. The downside is that the index will consume more pages which equates to more disk space.
>
>Remember that SQL Server does not dynamically maintain the fill-factor. Eventually the empty space will be filled by new or modified data. You'll have to rebuild the clustered index to re-establish the fill-factor.
>
>-Mike