CREATE PARTITION FUNCTION partfunc (int) AS RANGE LEFT FOR VALUES (1000, 2000) GO in .NET framework

Printer PDF417 in .NET framework CREATE PARTITION FUNCTION partfunc (int) AS RANGE LEFT FOR VALUES (1000, 2000) GO

5. View the results of executing this command by executing the following query:

SELECT * FROM sys.partition_range_values;

Defining a partition function is the first step of partitioning a table, view, or indexed view. Partition functions are stand-alone objects that define the boundary points that are used to partition data. The CREATE PARTITION FUNCTION statement s RANGE clause specifies the partition that boundary values belong to, and the VALUES clause defines the boundary points for the partition function.

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each answer choice is right or wrong are located in the Answers section at the end of the book.

6

What does a partition function define A. Boundary points for a partition B. Physical storage for a partition C. A rowset that returns the values in a partition D. The number of the partition containing a specified value

The second step of partitioning a table, index, or indexed view is creating a partition scheme. The partition scheme defines the physical storage structures, or filegroups, that will be used with a specific partition function. In this lesson, you will see how to create a partition scheme that maps partitions to filegroups.

After this lesson, you will be able to:

You use the CREATE PARTITION SCHEME Transact-SQL command to create a partition scheme. The general syntax for this command is as follows:

CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

You begin by naming your partition scheme according to the rules for object identifiers. You then use the PARTITION clause to specify the name of the partition function that will be mapped to this partition scheme. In the command s TO clause, you specify the list of filegroups that define the on-disk storage for any data using the partition scheme. Any filegroups you specify in this clause must already be added to the database, must have at least one file assigned to them, and must not be marked read-only. The following example shows how to use the command to define a partition scheme called partscheme:

CREATE PARTITION SCHEME partscheme AS PARTITION partfunc TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6]) GO

Notice that we still have not specified a table, view, or indexed view or referenced any other object in the database with the exception of the partition function. A partition scheme simply specifies a name for a physical storage structure. How does this partition scheme work with the partition function from Lesson 1 The partition function partfunc had six partitions. Based on the partition scheme

6

definition, SQL Server stores any values that reside in Partition 1 in FG1, values in Partition 2 in FG2, values in Partition 3 in FG3, and so on. So by carefully designing your partition functions and partition schemes, you can determine the exact set of data within a table, index, or indexed view that resides within a particular filegroup.

1. 2. 1. 2. What does a partition scheme define What are the requirements for creating a partition scheme A partition scheme defines a physical storage structure composed of one or more filegroups. The filegroups defined in the partition scheme must already be part of the database, must have a file assigned to them, and must not be marked readonly.

In this practice, you create the partition scheme for the partition function you created in the practice of Lesson 1. 1. If necessary, launch SSMS, connect to your instance, open a new query window, and change the context to the partitiontest database. 2. Create a partition scheme by executing the following command:

CREATE PARTITION SCHEME partscheme AS PARTITION partfunc TO ([FG1], [FG2], [FG3]);

3. View the results of executing the command by running the following query:

SELECT * FROM sys.partition_schemes;