Developing SQL Databases — Question 155

You have a data warehouse fact table that has a clustered columnstore index.
You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?

Answer options

• A. Load the source data to a staging table. Load the data to the fact table by using the INSERT_SELECT statement and specify the TABLOCK option on the staging table
• B. Create a Microsoft SQL Server Integration Services (SSIS) package. Use multiple data flow tasks to load the data in parallel.
• C. Load the source data to the fact table by running bcp.exe and specify the ""H TABLOCK option
• D. Load the source data to the fact table by using the BULK INSERT statement and specify the TABLOCK option

Correct answer: A

Explanation

The correct answer is A because loading the data into a staging table first allows for efficient bulk operations with the INSERT_SELECT statement and the TABLOCK option, preventing delta group creation. Option B, while parallel, may still result in a delta group and is not the fastest method. Options C and D are not optimal as they do not leverage the staging table for the best performance.