You are a database developer on an instance of SQL Server 2008. You have over a million rows of sales history data that you plan to import into theSales database. You want to create a SalesHistory table to store the data.
Sales history for each branch is identified by the BranchID column, which is an int data type, and most queries will be executed by branch managers for their specific branch’s data. You decide to partition the table to improve query performance.
You want to partition the SalesHistory table as follows:
Which Transact-SQL should you use to create the SalesHistory table as a partitioned table?
A. CREATE PARTITION FUNCTION MyPF (int)
AS RANGE LEFT FOR VALUES (6999, 26999, 56999, 86999);
CREATE PARTITION SCHEME MyPSAS PARTITION MyPFTO (fg1, fg2, fg3, fg4, fg5);
CREATE TABLE SalesHistory (OrderID int,OrderDate datetime,TerritoryID int,BranchID int,TaxFlg bit,ShipMethodID int,Amount money)ON MyPS(BranchID);
B. CREATE PARTITION FUNCTION MyPF (int)
AS RANGE LEFT FOR VALUES (7000, 27000, 57000, 87000);
CREATE TABLE SalesHistory (OrderID int,OrderDate datetime,TerritoryID int,BranchID int,TaxFlg bit,ShipMethodID int,Amount money)ON MyPF(BranchID);
C. CREATE PARTITION FUNCTION MyPF (int)
AS RANGE LEFT FOR VALUES (7000, 27000, 57000, 87000);
CREATE PARTITION SCHEME MyPSAS PARTITION MyPFTO (fg1, fg2, fg3, fg4, fg5);
CREATE TABLE SalesHistory (OrderID int,OrderDate datetime,TerritoryID int,BranchID int,TaxFlg bit,ShipMethodID int,Amount money)ON MyPS(BranchID);
D. CREATE PARTITION FUNCTION MyPF (int)
AS RANGE LEFT FOR VALUES (7000, 27000, 57000, 87000);
CREATE PARTITION SCHEME MyPSAS PARTITION MyPFTO (fg1, fg2, fg3, fg4, fg5);
CREATE TABLE SalesHistory (OrderID int,OrderDate datetime,TerritoryID int,BranchID int,TaxFlg bit,ShipMethodID int,Amount money)ON MyPS(OrderDate);
Correct Answer: C
Explanation/Reference:
To create a partitioned a table, you first create a partition function using the CREATE PARTITION FUNCTION statement that specifies the number of partitions and how partitioning will occur. A partition function maps the rows of a table or index into partitions based on the specified partition boundary values. The CREATE PARTITION FUNCTION statement in this scenario creates a partition function named MyPF with a partition column of the int data type. The FOR VALUES clause of the CREATE PARTITION FUNCTION statement specifies the boundary value of each partition. The RANGE RIGHT and RANGE LEFT clauses are used to specify how the actual boundary values are handled. RANGE LEFT indicates that the boundary value should be stored in the partition on the left side of the boundary value with the boundary values sorted in ascending order from left to right. The CREATE PARTITION FUNCTION statement in this scenario, defines five partitions as follows:
Next, you must create a partition scheme based on the previously created partition function using the CREATE PARTITION SCHEME statement. The partition scheme maps the partitions created by the partition function to filegroups. One or more filegroups can be specified in the partition scheme. The AS PARTITION clause of the CREATE PARTITION SCHEME statement identifies the partition function, and the TO clause specifies the filegroups. The complete syntax for the CREATE PARTITION SCHEME statement is:
CREATE PARTITION SCHEME name_of_partition_scheme AS PARTITION name_of_partition_function [ALL] TO ({file_group |
[PRIMARY]} [,…n] );
The partition scheme created in this scenario would map the partitions to file groups as follows:
After you have created the partition function and partition scheme, you must create the SalesHistory table as a partitioned table using a CREATE TABLE statement that includes an ON clause. The ON clause identifies the partition scheme and the column on which the table will be partitioned. The specified partitioning scheme identifies the partition function that is used. The complete syntax of the CREATE TABLE statement to create a partitioned table is as follows:
CREATE TABLE table_name (column_def1, column_def2, …)ON name_of_partition_scheme (partition_column_name);
The arguments used in the statement syntax are as follows:
table_name: Specifies the name of the table to be created.
column_defn: Specifies the details of the column(s) in the table.
partition_scheme_name: Specifies the name of the partition scheme that identifies the partition function and the filegroups to which the partitions of the table will be written. partition_column_name: Specifies the name of the column in the table on which the table will be partitioned. The column specified must match the column definition specified in the associated partition function in terms of the data type, length, and precision.
You should not use the Transact-SQL that includes RANGE LEFT FOR VALUES (6999, 26999, 56999, 86999) in the CREATE PARTITION FUNCTION statement because this will partition the SalesHistory table as follows:
You should not use the Transact-SQL that fails to include a CREATE PARTITION SCHEME statement. You must create a partition scheme and specify the partition scheme, not the partition function, in the ON clause of the CREATE TABLE statement. This code will generate the following error:
Msg 1921, Level 16, State 1, Line 4Invalid partition scheme ‘MyPF’ specified.
You should not use the Transact-SQL that specifies MyPS(OrderDate) in the ON clause of the CREATE TABLE statement. When creating a partitioned table, the data type of the column specified in the ON clause must be the same as the data type specified in the CREATE PARTITION FUNCTION statement, which in this scenario is int. This code will generate an error.
