Nonclustered indexes quickie

Hi

You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall?

If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option?

Thought I'd make it a poll just for the hell of it.
:)Huh ?|||Hi

You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall?

If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option?

Thought I'd make it a poll just for the hell of it.
:)

Are you drunk or something?|||i think the brits may already be off to the pubs by this time, but please explain before I go off to BOL to try and figure this one out.|||Heh heh - I just had a couple of cups of a colleague's rocket fuel coffee - it really does affect me in odd ways. In any event I think I might have got all excited about a very obvious thing.

Ok - this was my epiphany:
The leaf level of a nonclustered index contains nothing but the index values contained in the B-Tree plus a pointer to the data page (unless you use an includes statement in which case this doesn't apply).

Fill factor affects the leaf level only. The pad index option applies the fill factor value to the B-Tree of the index. As such if pad index is not used the B-Tree is stuffed full while there is room at the leaf level. Any changes to the index will (most likely) require movement to another page on the B-Tree and, following on, movement at the leaf level. The advantage of leaving loads of room at the leaf level is to a large extent lost since you need to shuffle the B-Tree about anyway. ...

Actually - I've just realised where I have got to. If your indexes are likely to change use pad_index otherwise don't. I knew that already - I just got there backwards.

I'm going for a lie down. :D|||Heh heh -

I'm going for a lie down. :D

Careful Poots, blindman's gonna make you change your sig line.

I think I understand where you're going. I'm going to read more about it now.

Regards,

hmscott