Exam 70-767 All Questions

View all questions & answers for the 70-767 exam

Exam 70-767 topic 1 question 129 discussion

Actual exam question from Microsoft's 70-767

Question #: 129
Topic #: 1

HOTSPOT -
You are the administrator of a database that hosts tables for a data warehouse.
The table named Fact1 has data from the start of calendar year 2011 through the end of 2017. The table contains at least 20 million rows of data for each year.
You create the table by running the following Transact-SQL statement:
CREATE PARTITION FUNCTION PartitionFunc-Fact1(SMALLINT)
AS RANGE LIFT VALUES(2012, 2013, 2014, 2015)
You need to modify the partition function so that rows for each calendar year are in a separate partition. You must also move all data prior to 2014 to another table named Fact1_old.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segment in the dialog box in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer: Explanation
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

by cubo at Nov. 12, 2019, 10:09 a.m.

Comments

Submit Cancel

Andrescarnederes

Highly Voted 5 years, 2 months ago

1.Merge 2.2013 3.Switch 4.Split 5.2016

upvoted 11 times

arnoldnowak1992

4 years, 8 months ago

In my opinion you should to merge year 2012 because we want to move "move all data prior to 2014" so after that you will get 2011,2012 and 2013 in one (first) partition. In your case you would be get: 1st partition: 2011, 2012 2nd partition: 2013, 2014 3rd partition: 2015 4th partition: 2016, 2017 It doesn't make sense. In my proposal it look like: 1st partition: 2011, 2012, 2013 2nd partition: 2014 3rd partition: 2015 4th partition: 2016, 2017

upvoted 1 times

...

DudeHere

4 years, 7 months ago

100% correct. This question is tricky due to the LEFT range specification and (SMALLINT) instead of (DATE) ALTER PARTITION FUNCTION PartitionFunc_FACT () MERGE RANGE (2013) effectively removes the left boundary of 2013 making a complete range of 2012 and 2013 records. Now, let's SWITCH all those old records to the fact_old table ALTER TABLE Fact1 SWITCH PARTITION 1 TO Fact_old This leaves us with 2 Ranges: 2014 and 2015-2017 Let's create one more LEFT boundary of 2016. This will effectively SPLIT that last range and create the needed partitions (on partition for EACH calendar year): ALTER PARTITION FUNCTION PartitionFunc_Fact1() SPLIT RANGE (2016)

upvoted 1 times

...

Dieter

5 years, 1 month ago

Correct in my opinion: 1. Merge to get all from 2012 to 2014 (via Merge 2013) in Parition 1. 2. Switch this parition to Fact_old 3. Split the rest (2015 to 2017)with 2016 into three partitions, 2015, 2016, 2017.

upvoted 7 times

...

Cococo

Most Recent 4 years, 9 months ago

Images from this question - https://vceguide.com/how-should-you-complete-the-transact-sql-statement-50/

upvoted 1 times

...

Rmznd

5 years, 4 months ago

Is that the correct answer?

upvoted 1 times

m8rvil

5 years, 3 months ago

I think the answer is: 1. Split 2. 2014 3. Switch 4. Split 5. 2015

upvoted 7 times

eceb

4 years, 10 months ago

I've read this answer in other dumps but I think it's not correct because in order to switch partition 1 to fact_old you have to merge in partition 1 all data prior to 2014, so the first option should be merge 2013 (to get all data from partitions 2012 to 2014). I agree with Andrescarnederes and Dieter

upvoted 2 times

...

Juliano

5 years, 4 months ago

1. SET 2. 2012 3. Merge 4. Split 5. 2016

upvoted 2 times

eceb

4 years, 10 months ago

set is not a possible option in the alter partition function, so this answer is not correct. ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) } [ ; ] https://docs.microsoft.com/es-es/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-ver15

upvoted 1 times

...