1

I have following two questions:

  1. I have a SQL Server index with the following options:

    PAD_INDEX = OFF
    STATISTICS_NORECOMPUTE = OFF
    SORT_IN_TEMPDB = OFF
    DROP_EXISTING = OFF
    ONLINE = OFF
    ALLOW_ROW_LOCKS = ON
    ALLOW_PAGE_LOCKS = ON
    FILLFACTOR = 70
    

    Where are these details are stored in the database? I have found tables like sys.indexes and sys.stats where a few of these options are stored. I need the complete list, if possible.

  2. To partition an index in SQL Server, we need to drop it and recreate it by associating it with partition scheme. Is this the only way to apply partition on an existing index?

2

Not all index create options are persisted as meta-data; these (e.g. SORT_IN_TEMPDB, ONLINE, etc.) are specified only as run-time DDL options.

Rather than drop and recreate the index entirely, an index can be partitioned by using CREATE INDEX...WITH (DROP_EXISTING=ON).

CREATE INDEX YourTable_Index ON dbo.YourTable(IndexColumn)
    WITH (DROP_EXISTING=ON) ON PS_YourTable(PartitioningColumn);

EDIT:

Be aware that SQL Server does not consider the existing index FILLFACTOR with CREATE INDEX...DROP_EXISTING=ON because a new index is created. Although, the existing index FILLFACTOR can be used with ALTER INDEX...REBUILD (by omitting the FILLFACTOR clause), that method does not allow changing partitioning or the filegroup whereas CREATE INDEX...DROP_EXISTING=ON does and avoids sorting the rows during the index recreate. The desired FILLFACTOR needs to be explicitly specified when using CREATE INDEX...DROP_EXISTING=ON when a non-default value is desired.

You'll need to generate the DDL dynamically if you want to retain the old index FILLFACTOR (and other persisted options of the original index) without manually specifying the value.

  • It does not take care of existing option details. for example I added FillFactor=70 and recreated using drop_existing=on option. New Index has FillFactor=0. My object is to retain the option settings – Murali Dhar Darshan Mar 22 at 5:48
0

You can also ALTER TABLE SWITCH to move a table and all of its indexes from a filegroup to a partition scheme, which you can follow by drop and rename. The whole table must fit in a single partition mapped to the table's current filegroup, so this is a metadata-only operation. But new data can go to additional partitions, and the original partition can later be SPLIT. Of course it's rather expensive to split non-empty partitions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.