DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
Which three actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)
Answer: See the explanation.
Explanation:
Box 1: Create the fact table.
Box 2: Create the columnstore index with the MAXDOP keyword.
Box 3: Load the data.
1. Create Table
2. Load Data
3. Create coloumnstore index
1
0
the answer provided initially is correct.
We specify the MAXDOP option, because the ASC and INCLUDE options cannot be used when creating Nonclustered columnstore indexes
1
0
this comment was relates to the 3rd task only. the first two are 1.create table 2. load data
0
0
A columnstore index can be created before there is data in the table.
0
0
While true, you can have a column store index on the table without data in it, you can’t load data while that index is enabled. Disabling the index, loading the data, then enabling the index will cause a rebuild of the index. Doing this would be two additional steps (disable, enable), which aren’t listed, and there is no advantage to doing that in the first place since this is a new table.
Even if the index could exist in an enabled state (if this were not a columnstore index), that would have a significant negative impact on write performance against the table.
Create the table, load the data, create the index.
2
0
1. Create table
2. Load Data
3. Create Column Store Index(CSI)
in SQL 2012 we can’t chanche data in table with column store index
https://msdn.microsoft.com/ru-ru/library/gg492088(v=sql.110).aspx#Update
in 2014 – data can be modified if CSI is clustered
in 2016 – all data can be modified with all CSI
0
0
1. Create Fact Table
2. Load the Data
3. Create Columnstore Index with MAXDOP
0
0
Create the fact table.
Load the data.
Create the columnstore index with the MAXDOP keyword.
0
0