You are the database administrator for your company and manage all your company’s SQL Server 2008 databases.
A production database named Prod1 contains all the product and sales data of the company.
You must create a table named Prod_details to hold detailed data about products manufactured by yourcompany.
Company employees will frequently access data in this table in such a way that you want to create thetable as a partitioned table.
You use the following statements to create the partition function My_pf and the partition scheme My_ps1 :
CREATE PARTITION FUNCTION My_pf (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000);
CREATE PARTITION SCHEME My_ps1
AS PARTITION My_pf
TO (FG1, FG2, FG3, FG4);
Which statement should you use to create the Prod_details table as a partitioned table?

A.
CREATE TABLE Prod_details (
prod_id int,
prod_desc varchar(50),
prod_name varchar(20))
ON My_ps1(prod_id);

B.
CREATE TABLE Prod_details (
prod_id int,
prod_desc varchar(50),
prod_name varchar(20))
ON My_ps1(prod_name);

C.
CREATE TABLE Prod_details (
prod_id int,
prod_desc varchar(50),
prod_name varchar(20))
ON My_pf(prod_id);

D.
CREATE TABLE Prod_details (
prod_id int,
prod_desc varchar(50),
prod_name varchar(20))
ON My_ps1;

Explanation:

You should use the following statement to create the table as a partitioned table:
CREATE TABLE Prod_details (
prod_id int,
prod_desc varchar(50),
prod_name varchar(20))
ON My_ps1(prod_id);
This statement uses the correct syntax for creating a partitioned table. A partitioned table initially involves thecreation of a partition function that specifies how the values in the table that use the function will be partitioned.Then, a partition scheme is created based on the previously created partition function. The partition scheme mapsthe different partitions created by the partition function to filegroups. One or more filegroups can be specified inthe partition scheme. The partition table is created by using the following syntax that includes the partitionscheme: CREATE TABLE table_name ( column_def1, column_def2, … ) ON partition_scheme_name ( 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 filegroups towhich 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 bepartitioned. The column specified must match the column definition specified in the partition function interms of the data type, length, and precisionIn the Transact-SQL statement used to create the Prod_details table, the data type of the partition column is
int .This data type is the same as the data type that was specified in the My_pf partition function. The name of thepartition scheme is correctly specified as My_ps1
. Therefore, the statement will successfully create a tablepartitioned on the prod_id column by using the My_ps1 partition scheme. You should not use the following statement:
CREATE TABLE Prod_details ( prod_id int, prod_desc varchar(50), prod_name varchar(20)) ON My_ps1(prod_name); This statement specifies the partition column as
prod_name . The partition column does not match the columnspecifications provided while creating the partition My_pf function. In a partitioned table, the partition column mustmatch the column definition specified in the partition function in terms of the data type, length, and precision. Anyviolation of this will generate an error while creating the partitioned table. You should not use the following statement: CREATE TABLE Prod_details ( prod_id int, prod_desc varchar(50), prod_name varchar(20)) ON My_pf(prod_id); When you create a partitioned table, you must specify the name of the partition scheme instead of the partitionfunction. In this scenario, My_ps1 is the partition scheme, and it must be specified while creating the table. You should not use the following statement: CREATE TABLE Prod_details ( prod_id int, prod_desc varchar(50), prod_name varchar(20)) ON My_ps1; When creating a partitioned table, you must specify the column name on which the table will be partitioned. You cannot create a partitioned table without specifying the column name on which the table will be partitioned. In thisstatement, you did not specify a value of My_ps1 for the partition scheme; therefore, the SQL server will consider My_ps1 as a filegroup.

Objective:
Performing Data Management Tasks

Sub-Objective:
Manage data partitions.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE TABLE (Transact-SQL)