If you need to load additional data into a partitioned table
that does not have clustered indexes, and **sp_helpartition** shows
that some partitions contain many more pages than others, you can
use the bulk copy session to help balance number of rows on each
partition.

The following example shows that the table has only 487 pages on one partition, and 917 on another:

partitionid firstpage controlpage ptn_data_pages ----------- ----------- ----------- -------------- 1 189825 189824 812 2 204601 204600 487 3 189689 189688 917 (3 rows affected) Partitions Average Pages Maximum Pages Minimum Pages Ratio (Max/Avg) ---------- ------------- ------------- ------------- --------------- 3 738 917 487 1.242547

The number of rows to add to each partition can be computed by:

Determining the average number of rows that would be in each partition if they were evenly balanced, that is, the sum of the current rows and the rows to be added, divided by the number of partitions

Estimating the current number of rows on each partition, and subtracting that from the target average

The formula can be summarized as:

Rows to add = (total_old_rows + total_new_rows)/#_of_partitions - rows_in_this_partition

This sample procedure uses values stored in *systabstats* and *syspartitions* to perform
the calculations:

create procedure help_skew @object_name varchar(30), @newrows int as declare @rows int, @pages int, @rowsperpage int, @num_parts int select @rows = rowcnt, @pages = pagecnt from systabstats where id = object_id(@object_name) and indid in (0,1) select @rowsperpage = floor(@rows/@pages) select @num_parts = count(*) from syspartitions where id = object_id(@object_name) select partitionid, (@rows + @newrows)/@num_parts - ptn_data_pgs(id, partitionid)*@rowsperpage as rows_to_add from syspartitions where id = object_id (@object_name)

Use this procedure to determine how many rows to add to each
partition in the *customer* table, such as when
18,000 rows need to be copied in. The results are shown below the
syntax.

help_skew customer, 18000

partitionid rows_to_add------------------ 1 5255 2 9155 3 3995

If the partition skew is large, and the number of rows to be added is small, this procedure returns negative numbers for those rows that contain more than the average number of final rows.

Query results are more accurate if you run **update
statistics** and **update partition statistics** so
that table and partition statistics are current.

With the results from **help_skew**,
you can then split the file containing the data to be loaded into
separate files of that length, or use the **-F** (first)
and **-L** (last) flags to **bcp**.