View Poll Results: As below....

Voters
1. You may not vote on this poll
  • Well duh - index 101 Poots

    0 0%
  • E minus Poots - it is not remotely pointless

    0 0%
  • Enough of the polls thank you Poots

    1 100.00%
Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: 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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Huh ?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    Heh heh -

    I'm going for a lie down.
    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
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •