ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-203 All Questions

View all questions & answers for the DP-203 exam
Go to Exam

Exam DP-203 topic 2 question 14 discussion

Actual exam question from Microsoft's DP-203
Question #: 14
Topic #: 2
[All DP-203 Questions]

HOTSPOT -
You have an enterprise data warehouse in Azure Synapse Analytics that contains a table named FactOnlineSales. The table contains data from the start of 2009 to the end of 2012.
You need to improve the performance of queries against FactOnlineSales by using table partitions. The solution must meet the following requirements:
✑ Create four partitions based on the order date.
✑ Ensure that each partition contains all the orders placed during a given calendar year.
How should you complete the T-SQL command? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
Range Left or Right, both are creating similar partition but there is difference in comparison
For example: in this scenario, when you use LEFT and 20100101,20110101,20120101
Partition will be, datecol<=20100101, datecol>20100101 and datecol<=20110101, datecol>20110101 and datecol<=20120101, datecol>20120101
But if you use range RIGHT and 20100101,20110101,20120101
Partition will be, datecol<20100101, datecol>=20100101 and datecol<20110101, datecol>=20110101 and datecol<20120101, datecol>=20120101
In this example, Range RIGHT will be suitable for calendar comparison Jan 1st to Dec 31st
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15
by alexleonvalencia at Dec. 10, 2021, 12:56 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Canary_2021
Highly Voted 2 years, 12 months ago
Answer is correct. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15
upvoted 27 times
...
victor90
Highly Voted 3 years ago
I think the box 2 should be 20090101,2010101,20110101,20120101 since the question asked about 4 partitions. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15#c-creating-a-range-right-partition-function-on-a-datetime-column
upvoted 17 times
TestMitch
3 years ago
No! That's wrong! Number of partitions created = Number of partition boundaries specified + 1.
upvoted 30 times
...
onyerleft
2 years, 12 months ago
Choosing box 2 with range right would create five partitions. The first partition would be <20090101. So the provided answer is correct
upvoted 6 times
rlnd2000
4 months, 3 weeks ago
Incorrect you read the requirement: -Ensure that each partition contains all the orders placed during a given calendar year.- now as you said [time<2009-01-01] goes to the first partition and for sure in the second partition we will have only 2009
upvoted 1 times
...
...
...
rlnd2000
Most Recent 4 months, 3 weeks ago
The given answer does not fully meet the requirement: ""Ensure"" that each partition contains all the orders placed during a given calendar year. Using the values 2010101, 20110101, 20120101 means that all dates before 2010101 will go to the first partition. If, by any chance, a new date from the year 2008 is inserted into the table, it will go to the first partition together with 2009. Therefore, I believe 20090101, 2010101, 20110101, 20120101 is a better answer.
upvoted 2 times
...
Bakhtiyor
8 months, 1 week ago
RIGHT - [time<2010-01-01] [2010-01-01<=time<2011-01-01] [2011-01-01<=time<2012-01-01] [time<=2012-01-01] LEFT - [time<=2010-01-01] [2010-01-01<time<=2011-01-01] [2011-01-01<time<=2012-01-01] [time<2012-01-01]
upvoted 1 times
...
moneytime
10 months, 1 week ago
I stand with the chosen answer. For me it is 100% correct If you chose LEFT partition with 20090101,2010101,20110101, then be aware that the fourth partition will not include 1st January 2012 which means the partition doesn't actually supports all the 12 calendar months is a particular year.
upvoted 1 times
...
kkk5566
1 year, 3 months ago
RIGHT : t<20100101, 20100101<=t<20110101, 20110101<=t<20120101 20120101<=t LEFT t<=20100101, 20100101<t<=20110101,20110101<t<=20120101, t>20120101
upvoted 2 times
...
Maddhy
2 years ago
Answer is 1.right 2. Last option becoz there they mentioned 4 partitions ( I'm sure that it is guarenteed)
upvoted 4 times
Maddhy
2 years ago
The reason we are using right that is here the values are not null
upvoted 1 times
...
...
rzeng
2 years, 1 month ago
IF use [RIGHT], it means : [time<20100101], [20100101<=time<20110101] and so on IF use [LEFT], it means: [time<=20100101], [20100101<time <= 20110101] and so on See if you choose [LEFT] then will NOT include the 0101 value of current calendar year into the query, so GO with [RIGHT]
upvoted 9 times
...
Deeksha1234
2 years, 4 months ago
Answer is correct.. given1st boundary value will be included in the 2nd partition (since right) so 1st partition will end at 20091231 and 2nd will start at 20100101 and end at 20101231 and so on..
upvoted 2 times
...
Deeksha1234
2 years, 4 months ago
Answer is correct
upvoted 1 times
...
dsp17
2 years, 5 months ago
How to remember LEFT / RIGHT concept, it's confusing, pls help
upvoted 2 times
monibun
2 years, 3 months ago
I do that by initials of L(eft) means starting as "Less than equal to first boundary value, there onwards greater than " and for right, other way around.
upvoted 7 times
...
...
PallaviPatel
2 years, 10 months ago
correct Answer.
upvoted 1 times
...
dev2dev
2 years, 11 months ago
where does 2009 year stored? i think the 1st choice should be LEFT so th
upvoted 4 times
allagowf
2 years, 2 months ago
the answer is correct, check the requirements: ✑ Create four partitions based on the order date. ✑ Ensure that each partition contains all the orders placed during a given calendar year. both right an d left results in 4 partitions, but left will have mixed values from 2 years, check the clarification in the answer. so right will result in 4 partions each partition contains all the orders placed during a given calendar year.
upvoted 3 times
...
...
VeroDon
2 years, 11 months ago
correct
upvoted 2 times
...
alexleonvalencia
3 years ago
Respuesta correcta. RIGTH, [3 VALORES].
upvoted 2 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago