Alternative for clustered indexes

This set of steps may be useful when:

This set of steps successfully distributes the data in almost all cases, but requires careful attention:

  1. Find the minimum value for the key column for the clustered index:

    select min(order_id) from orders
  2. If the clustered index exists, drop it. Drop any nonclustered indexes.

    See “Using drop index and create clustered index” or “Using constraints and alter table”.

  3. Execute the command:

    set sort_resources on

    This command disables create index commands. Subsequent create index commands print information about how the sort will be performed, but do not create the index.

  4. Issue the command to create the clustered index, and record the partition numbers and values in the output. This example shows the values for a table on four partitions:

    create clustered index order_cix 
        on orders(order_id)
    The Create Index is done using Parallel Sort
    Sort buffer size: 1500
    Parallel degree: 25
    Number of output devices: 3
    Number of producer threads: 4
    Number of consumer threads: 4
    The distribution map contains 3 element(s) for 4 partitions.
    Partition Element: 1
    Partition Element: 2
    Partition Element: 3
    Number of sampled records: 2449

    These values, together with the minimum value from step 1, are the key values that the sort uses as diameters when assigning rows to each partition.

  5. Bulk copy the data out, using character mode.

  6. Unpartition the table.

    See “alter table...unpartition Syntax”.

  7. Truncate the table.

  8. Repartition the table.

    See “alter table...partition syntax”.

  9. In the resulting output data file, locate the minimum key value and each of the key values identified in step 4. Copy these values out to another file, and delete them from the output file.

  10. Copy into the table, using parallel bulk copy to place them on the correct segment. For the values shown above, the file might contain:

    1             Jones        ...
    450977        Smith        ...
    903269        Harris        ...
    1356032       Wilder        ...

    The bcp commands look like this:

    bcp testdb..orders:1 in keyrows -F1 -L1
    bcp testdb..orders:2 in keyrows -F2 -L2
    bcp testdb..orders:3 in keyrows -F3 -L3
    bcp testdb..orders:4 in keyrows -F4 -L4

    At the end of this operation, you will have one row on the first page of each partition – the same row that creating the index would have allocated to that position.

  11. Turn set sort_resources off, and create the clustered index on the segment, using the with sorted_data option.

    Do not include any clauses that force the index creation to copy the data rows.

  12. Use bulk copy to copy the data into the table.

    Use a single, nonparallel session. You cannot specify a partition for bulk copy when the table has a clustered index, and running multiple sessions runs the risk of deadlocking.

    The clustered index forces the pages to the correct partition.

  13. Use sp_helpartition to check the balance of data pages on the partitions and sp_helpsegment to balance of pages on the segments.

  14. Create any nonclustered indexes.

  15. Dump the database.

While this method can successfully make use of nearly all of the pages in a partition, it has some disadvantages: