You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain a table named Claims. The Claims table will contain a large amount of data. You plan to partition the data into following categories:
• Open claims
• Claims closed before January 1, 2005
• Claims closed between January 1, 2005 and December 31, 2007
• Claims closed from January 1, 2008 till date
The close_date field in the Claims table is a date data type and is populated only if the claim has been closed.
You need to design a partition function to segregate records into the defined categories.
What should you do?
A. Create a RANGE RIGHT partition function by using the values 20051231, 20071231, and 20080101.
B. Create a RANGE RIGHT partition function by using the values 20051231, 20071231, and NULL.
C. Create a RANGE LEFT partition function by using the values 20051231, 20071231, and 20080101.
D. Create a RANGE LEFT partition function by using the values 20051231, 20071231, and NULL.
Correct Answer:
Explanation/Reference:
None of the available choices is correct. The correct answer is:
CREATE PARTITION FUNCTION PF (Date)
AS RANGE LEFT FOR VALUES (CAST(NULL AS Date), ‘2004-12-31’, ‘2007-12-31’);
NULL is considered smaller than the minimum value of the data type’s values. So if NULL is not specified as boundary value in a partition function, then any data with a NULL in the partition column will reside in the leftmost partition. This will not respect the requirements because Open claims and Claims closed before January 1, 2005 will be in the same category, but according to the requirements they should be in different partitions. So we MUST specify NULL as a boundary value. But what function to use LEFT or RIGHT?
For RANGE LEFT, beginning a list of boundary values with NULL will place all and only data with NULLs in the partition column into partition 1. For RANGE RIGHT, beginning a list of boundary values with NULL will leave partition 1 permanently empty, and data with NULL in the partition column will fall into partition 2, along with all other values less than the next boundary value. So we should use RANGE LEFT.
As soon as we use RANGE LEFT, claims closed before January 1, 2005 will be represented by December 31, 2004 boundary value. Claims closed between January 1, 2005 and December 31, 2007 will be represented by December 31, 2007 boundary values (between means >=January 1, 2005 and <= December 31,
As a result, the correct answer: Create a RANGE LEFT partition function by using the values NULL, December 31, 2004, December 31, 2007.
