You are the database administrator for your company. The network contains a SQL Server 2008 computer. The SQL server contains a database named Sales.
You want to create four partitions on the CustID column in the Customers table of the Sales database. The values for each partition will be as follows:
You want to create a partition function that will be used to create the appropriate partitions for the Customers table as per these requirements.
Which Transact-SQL statement should you run?
A.
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE LEFT FOR VALUES (1, 1000, 2000);
B.
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000);
C.
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE LEFT FOR VALUES (1, 1000, 2000, >2000);
D.
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000, >2000);
Explanation:
You should run the following Transact-SQL statement:
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000);
Partitions allow you to place a subset of a table or index on a specified filegroup. The CREATE PARTITIONFUNCTION statement is used to create a partition function. A partition function maps the rows of a table or indexinto partitions based on the values of a specified column. The complete syntax for the CREATE PARTITIONFUNCTION statement is:
CREATE PARTITION FUNCTION name_of_partition_function ( type_of_input_parameter ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,…n ] ] );
The LEFT or RIGHT arguments specify the side of each boundary value interval to which the boundary_value[ ,…n ] belongs when interval values are sorted in ascending order from left to right by the database engine. Forexample, running the following Transact-SQL statement will create a partition function that can be used topartition a table or index into four partitions:
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000);
If you use the above partition function on a partitioning column named CustID , the partitioning would be done as follows:
You should not run the following Transact-SQL statement:
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE LEFT FOR VALUES (1, 1000, 2000);
If you use this partition function on a partitioning column named CustID , the partitioning would be done as follows:
This would not meet the requirements of the scenario. To ensure that the values for each partition meet therequirements, you should use the LEFT range direction instead of the RIGHT range direction when creating thepartition function. You should not run the following Transact-SQL statement:
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE LEFT FOR VALUES (1, 1000, 2000, >2000);
This statement is syntactically incorrect because values in the values list cannot contain symbols, such as arelational operator. You should not run the following Transact-SQL statement:
CREATE PARTITION FUNCTION PFsales1 (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000, >2000);
This statement is syntactically incorrect because values cannot contain symbols. Also, to ensure that the valuesfor each partition meet the requirements, you should use the
LEFT range direction instead of the RIGHT rangedirection when creating the partition function.Objective:
Performing Data Management TasksSub-Objective:
Manage data partitions.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE PARTITION FUNCTION (Transact-SQL)
