Exam 70-762 topic 1 question 165 discussion

Actual exam question from Microsoft's 70-762

Question #: 165
Topic #: 1

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

Show Suggested Answer

Suggested Answer: A 🗳️
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 INSERT/SELECT 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:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance?view=sql-server-2017#plan-bulk-load-sizes-to- minimize-delta-rowgroups

by Alex5x at Dec. 1, 2020, 8:06 p.m.

Comments

Submit Cancel

Alex5x

4 years, 5 months ago

Microsoft recommends to use the command below: INSERT INTO <columnstore index> WITH (TABLOCK) SELECT <list of columns> FROM <Staging Table> As you can see, TABLOCK hint is specified on a table that is the target of an INSERT statement. In our case the target is the fact table. However, the option A says: "specify the TABLOCK option on the staging table". This is confusing.

upvoted 3 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 165 discussion

Comments

Alex5x

SY0-701