Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 311
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 311 Typically, if you re adding more than 50% of the current table count or more into the table, you should drop the indexes first because they slow down the inserts, and the indexes will be better if you rebuild them after adding that much data anyway. Regardless, fast bulk copy doesn t work if there are indexes on the table unless the table is empty to begin with. Another option for large bulk inserts sets the batch size. Batch size is the number of rows that will be inserted as part of a transaction. If the batch size is large, then the transaction that is generated will be large, and it may cause your transaction log to fill up. If the transaction is too small, SQL Server spends too much time committing transactions rather than writing your data, and performance suffers. Typically, a batch size between 1,000 and 10,000 is used. Files with lots of rows and very few columns tend to benefit from higher batch sizes. By default, BCP does the entire operation in one batch, but it lets you know when it finishes sending each 1,000 rows to SQL Server. You should be aware of a few special options, as shown in Table 5.3, that are used for importing data into SQL Server. TABLE 5.3 BCP DATA IMPORT PARAMETERS Parameter Function -k Tells SQL Server that if some of the data coming in has nulls in it, it shouldn t apply the default values; it should just leave the column null. -E If the table being imported into has an identity column, this option tells SQL Server to use the values in the file rather than the automatically created values from the IDENTITY property. -R Tells BCP to use the regional time, date, and currency settings rather than the default, which is to ignore any regional settings. -b Gives batch size, number of rows in each batch. Defaults to all of the rows in one batch. -h Gives Bulk Insert Hints (see Table 5.4) The -h option enables you to specify one or more different hints to SQL Server about how to process the bulk copy. These options enable you to fine-tune BCP performance, and are listed in Table 5.4.
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.