Post reply

index file groups and files

  • April 16, 2015 at 12:15 pm

    #398336

    I have a 3rd party app which has a separate file group called Index_1, the file group has individual files in the filegroup. When creating an index with tsql I believe the "on" refers to the filegroup name, does the engine determine the best place to put the index, the quirky thing is there is also a file in the group called Index_1

    create index index1 on table1(column1,column2) WITH (FILLFACTOR=100) on index_1

  • April 17, 2015 at 3:04 am

    #1791608

    Sorry, what's the question?

    ON in an index creation script refers to either a filegroup or a partition scheme

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2015 at 6:48 am

    #1791654

    if I have a filegroup called Index_1 with 8 files

    index_1

    Index_2

    index_3

    etc........

    which are all part of this filegroup, when the index is created are the indexes spread across all the individual files, or is there a logic which assigns them to a specific file?

  • April 17, 2015 at 6:59 am

    #1791659

    it will be spread across the files, distribution depending on their size and free space.

    ---------------------------------------------------------------------

  • April 17, 2015 at 7:00 am

    #1791660

    thanks that is what I had figured

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply