Home » Microsoft » 70-762 » Which solution will achieve the goal in the least amount of time
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
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/Reference:
Reference
If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.
A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command
INSERT INTO columnstore index
SELECT list of columns FROM Staging Table
This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. If the number of rows in the staging table
102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. One key limitation was that this INSERT operation was single threaded. To load data in parallel, you could create multiple staging table or issue INSERTSELECT with non-overlapping ranges of rows from the staging table. This limitation goes away with SQL Server 2016 (13.x). The command below loads the data from staging table in parallel but you will need to specify TABLOCK.
References
httpsdocs.microsoft.comen-ussqlrelational-databasesindexescolumnstore-indexes-data-loading-guidanceview=sql-server-2017#plan-bulk-load-sizes-to-minimizedelta-
rowgroups
