BULK INSERT finance.dbo.customers
FROM ‘di\bulk\accts143_10141000.txt’
WITH DATAFILETYPE = ‘char’,
FIELD/TERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n,’
TABLOCK
You want to ensure that response times do not slow when new account information is added to the database. What should you do?
A．Drop the indexes for the customers table before the data load, and then re-create the indexes after the data load is complete.
B．Remove the TABLOCK option from the BULK INSERT statement.
C．Add the BATCHSIZE option to the BULK INSERT statement and then set the option equal to 10 percent of the number of rows to be loaded.
D．Add the ROWS_PER_BATCH option to the BULK INSERT statement and then set the option equal to 10 percent of the number of rows to be loaded.
参考答案
正确答案：B
解析：Explanation: The TABLOCK hint increases the number of locks during the adding process. This is the reason why response time are slows down during this process. By removing the TABLOCK hint the default more granular row-level lock will be used. This would decrease the scope of the locks which would result in less waiting jobs and performance would improve.
Note: The BULK INSERT statement is used to copy a data file into a database table or view in a format specified by the user. The BULK INSERT statement accepts the TABLOCK hint, which allows the user to specify the locking behavior. that the BULK INSERT statement should use. TABLOCK specifies that a bulk update table-level lock is taken for the duration of the bulk copy. If TABLOCK is not specified, the default uses row-level locks.
Incorrect Answers:
A: The recommendation to perform. the bulk copy operation with the indexes in place depends on the amount of data to be copied into the table compared to the amount of existing data already in the table. The large the amount of data to be loaded into a table, relative to the amount of data already in the table, the more efficient it is to drop all indexes on the table, perform. the bulk copy operation, and then re-create the indexes after the data is loaded
C: The BATCHSIZE option of the BULK INSERT statement is used to specify that SQL Server 2000 must treat each batch of rows inserted into the table as a separate transaction. This is useful when errors occur in the bulk copy operation, which would cause the transaction to be rolled back. By specifying the BATCHSIZE option, only the current transaction will be rolled back if an error occurs and the destination table still contains the other batches of rows that were successfully inserted.
D: The ROWS_PER_BATCH option of the BULK INSERT statement SQL Server can be used to
indicate the approximate number of rows in a batch. SQL Server 2000 then optimizes the load automatically, according to the batch size value, which may result in better performance. Although accuracy in the clause is not critical, the larger the batch size is, the better the performance of the bulk copy operation.