Exam 70-767 topic 1 question 20 discussion

Actual exam question from Microsoft's 70-767

Question #: 20
Topic #: 1

DRAG DROP -
You have a data warehouse.
You need to move a table named Fact.ErrorLog to a new filegroup named LowCost.
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.
Select and Place:

Show Suggested Answer

Suggested Answer:

Step 1: Add a filegroup named LowCost to the database.
First create a new filegroup.
Step 2:
The next stage is to go to the "˜Files' page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files)
Step 3:
To move a table to a different filegroup involves moving the table's clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :
CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID
ON HumanResources.Department(DepartmentID)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY
This recreates the same index but on the SECONDARY filegroup.
References: http://www.sqlmatters.com/Articles/Moving%20a%20Table%20to%20a%20Different%20Filegroup.aspx

by Slava_bcd81 at Oct. 6, 2020, 8:29 p.m.

Comments

Submit Cancel

redsoxfred

4 years, 7 months ago

I tried it and the solution works... The table partition is changed

upvoted 2 times

...

Lukis92

4 years, 7 months ago

In my opinion, the last step is: Create a new Fact.ErrorLog table on the LowCost filegroup. Rebuilding or reorganizing indexes don't make sense at this phase.

upvoted 1 times

Lukis92

4 years, 6 months ago

My bad. I didn't read the instruction carefully. The solution is right.

upvoted 2 times

...

Slava_bcd81

4 years, 8 months ago

rebuild clustered index in the new filegroup? alter index... rebuild?

upvoted 1 times

...