Exam DP-200 topic 1 question 40 discussion

Considerations Create four partitions based on the order date. Ensure that each partition contains all the orders placed during a given calendar year. If we chose the left option here, data buckets will be like below Partition 1: OrderDateKey <= 20100101 Partition 2: 20100101 < OrderDateKey <= 20110101 Partition 3: 20110101 < OrderDateKey <= 20120101 Partition 4: 20120101 < OrderDateKey The problem here is "Ensure that each partition contains all the orders placed during a given calendar year." will Fail in this case. The OrderDateKey first day of the year ex- 20110101 will be in the partition which contains 2010 dates. If we chose the Right option here, data buckets will be like below Partition 1: OrderDateKey < 20100101 Partition 2: 20100101 <= OrderDateKey < 20110101 Partition 3: 20110101 <= OrderDateKey < 20120101 Partition 4: 20120101 <= OrderDateKey This will "Ensure that each partition contains all the orders placed during a given calendar year." So Option is Right with (20100101,20110101,20120101)

The second option should be RIGHT, since we need to store calendar data in each partition. Partition 1: col < 20090101 Partition 2: 20090101 <= col < 20100101 Partition 3: 20100101 <= col < 20110101 Partition 4: 20110101 <= col < 20120101 Partition 5: 20120101 <= col

Box 1 = Right, Box 2 = 20090101, 20100101, 20110101, 20120101 In a range left partition function, all boundary values are upper boundaries, they are the last values in the partitions. If you partition by year, you use December 31st. If you partition by month, you use January 31st, February 28th / 29th, March 31st, April 30th and so on. In a range right partition function, all boundary values are lower boundaries, they are the first values in the partitions. If you partition by year, you use January 1st. If you partition by month, you use January 1st, February 1st, March 1st, April 1st and so on:

LEFT is the Correct answer. We will use RIGHT when the value range is ( 20081231,20091231,20101231,20111231)

The answer is: LEFT, and 20100101, 20110101, 20120101. This will (1. give 4 partitions (2. Ensure each partitions contain orders made within a calendar year (E.g. 1st Jan 2009 to 31st Dec 2009) PARTITION 1: <= 20100101 --------> all orders before AND on 31st Dec 2009 PARTION 2: Col > 1st Jan 2010 to 31st Dec 2010 PARTITION 3: 1st Jan 2011 to 31st Dec 2011 PARTITION 4: 1st Jan 2012 and above This satisfies the 2 conditions of 4 partitions and each partition comprising orders in (within) a calendar year.

Answer should be Right 2010 , 2011, 2012 This ensures all the dates of one calendar year falls in same partiton and 3 boundaries gives 4 partitons

Answer should be RIGHT and 20100101,20110101,20120101 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

The total number of partitions is always the total number of boundary values + 1. As the question talks about four partions, second option should be the right answer.. And as it using is lower value of the right parition, it should be right.

Check the link provided in the solution: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7 RANGE LEFT: left boundary is exclusive, while the right boundary is inclusive. RANGE RIGHT: left boundary is inclusive, while the right boundary is exclusive. => RANGE RIGHT is certainly the correct option because 01-01-xxxx should be included in the right partition together with all the dates from year xxxx. We just need to play with the date values. Precisely, IMO the correct answer is RANGE RIGHT FOR VALUES (20100101, 20110101, 20120101). - Partition 1: col < 20100101 - Partition 2: 20100101 <= col < 20110101 - Partition 3: 20110101 <= col < 20120101 - Partition 4: 20120101 <= col If the empty partitions are discarded, it could also be RANGE RIGHT FOR VALUES (20090101, 20100101, 20110101, 20120101) because the partition <20090101 is empty. But I think this partition still exists even though it's empty, and the question specifies for 4 partitions. Please provide me evidence if I'm wrong about the existing empty partitions.

Answer cannot be RIGHT, because boundary value is INCLUSIVE, so RIGHT would be a range of 2nd Jan one year up to 1st Jan next year. Both 2nd and 3rd option would be valid, because there are no rows with dates < 2009, so crating left boundary on 01.01.2009 will not create fifth partition, because it would had to be empty.

The range should be RIGHT if you follow the link below. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7#PartitionedTable

What is the correct answer??

This can be a bit confusing if you don't read and interpret what's said in the link below with an open mind; https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7 scroll to "Table partition options" partition_column_name - Specifies the column that Azure Synapse Analytics will use to partition the rows. This column can be any data type. Azure Synapse Analytics sorts the partition column values in ascending order. The low-to-high ordering goes from LEFT to RIGHT in the RANGE specification. RANGE LEFT - Specifies the boundary value belongs to the partition on the left (lower values). The default is LEFT. RANGE RIGHT - Specifies the boundary value belongs to the partition on the right (higher values). I will go with the answers provided, "LEFT and the 4 date partitions" - because the questions state that it's to the end of 2012, so the lower boundary will be the start of that year (2012-01-01), therefore LEFT range.

The answer should be LEFT and 2nd Line.

Correct answer is 'RIGHT' https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7 (Scroll towards the end) LEFT/RIGHT is confusing, trick to remember is 1. Draw a hyphen among the values 20090101-20100101-20110101-20120101 2. pick one value (say 20100101) 3. Now understand, in which range do we want 20100101 to fall? here we want it in 20100101-20110101. 4. 20110101 is towards its right, Hence 'RIGHT'

In a range left partition function, all boundary values are upper boundaries, they are the last values in the partitions. If you partition by year, you use December 31st. If you partition by month, you use January 31st, February 28th / 29th, March 31st, April 30th and so on. In a range right partition function, all boundary values are lower boundaries, they are the first values in the partitions. If you partition by year, you use January 1st. If you partition by month, you use January 1st, February 1st, March 1st, April 1st and so on. Reference: https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server/#:~:text=Range%20Left%20and%20Range%20Right&text=Range%20left%20means%20that%20the,value%20in%20the%20right%20partition.

The range is correct. We need to create 4 partitions, 2009-2010, 2010-2011, 2011-2012, and 2012-2013, because we want data till the end of 2012.