When you are using parallel bulk copy, IDENTITY columns can cause a bottleneck. As bcp reads in the data, the utility both generates the values of the IDENTITY column and updates the IDENTITY column’s maximum value for each row. This extra work may adversely affect the performance improvement that you expected to receive from using parallel bulk copy.
To avoid this bottleneck, you can explicitly specify the IDENTITY starting point for each session.
If you copy sorted data into the table without explicitly specifying the IDENTITY starting point, bcp might not generate the IDENTITY column values in sorted order. Parallel bulk copy reads the information into all the partitions simultaneously and updates the values of the IDENTITY column as it reads in the data.
A bcp statement with no explicit starting point would produce IDENTITY column numbers similar to those shown in Figure 4-2:
Figure 4-2: Producing IDENTITY columns in sorted order
The table has a maximum IDENTITY column number of 119, but the order is no longer meaningful.
If you want Adaptive Server to enforce unique IDENTITY column values, you must run bcp with either the -g or -E parameter.
Use the -g id_start_value flag to specify an IDENTITY starting point for a session in the command line.
The -g parameter instructs Adaptive Server to generate a sequence of IDENTITY column values for the bcp session without checking and updating the maximum value of the table’s IDENTITY column for each row. Instead of checking, Adaptive Server updates the maximum value at the end of each batch.
WARNING! Be cautious about creating duplicate identity values inadvertently when you specify identity value ranges that overlap.
To specify a starting IDENTITY value, enter:
bcp [-gid_start_value]
For example, to copy in four files, each of which has 100 rows, enter:
bcp mydb..bigtable in file1 -g100 bcp mydb..bigtable in file2 -g200 bcp mydb..bigtable in file3 -g300 bcp mydb..bigtable in file4 -g400
Using the -g parameter does not guarantee that the IDENTITY column values are unique. To ensure uniqueness, you must:
Know how many rows are in the input files and what the highest existing value is. Use this information to set the starting values with the -g parameter and generate ranges that do not overlap.
In the example above, if any file contains more than 100 rows, the identity values overlap into the next 100 rows of data, creating duplicate identity values.
Make sure that no one else is inserting data that can produce conflicting IDENTITY values.
By default, when you bulk copy data into a table with an IDENTITY column, bcp assigns each row a temporary IDENTITY column value of 0. This is effective only when copying data into a table. bcp reads the value of the ID column from the data file, but does not send it to the server. Instead, as bcp inserts each row into the table, the server assigns the row a unique, sequential, IDENTITY column value, beginning with the value 1. If you specify the -E flag when copying data into a table, bcp reads the value from the data file and sends it to the server which inserts the value into the table. If the number of rows inserted exceeds the maximum possible IDENTITY column value, Adaptive Server returns an error.
The -E parameter has no effect when you are bulk copying data out. Adaptive Server copies the ID column to the data file, unless you use the -N parameter.
You cannot use the -E and -g flags together.
