Official description of create Partition Function

Create Partition Function (TRANSACT-SQL)

Create a function in the current database, which maps all rows of the table or index to the partition based on the value of the specified column. Using the create partition function is the first step in creating partitioned tables or indexes.

Transact-SQL syntax conventions

Syntax

 
Create Partition Function partition_function_name (input_parameter_type) as range [left | right] For values ([boundary_value [,... n]) [;]
 

Parameters

Partition_function_name

Is the name of the partition function. The partition function name must be unique in the database and comply with the identifier rules.

Input_parameter_type

Is the Data Type of the Column Used for partitioning. When used as a partition columnText,Ntext,Image,XML,Timestamp,Varchar (max),Nvarchar (max),Varbinary (max), Alias data type, or CLR user-defined data type, all data types are valid.

The actual column (also called a partition column) is specified in the create table or create index statement.

Boundary_value

specify the boundary value for each partition of a partitioned table or index using partition_function_name . If boundary_value is empty, the partition function uses partition_function_name to map the entire table or index to a single partition. Only one partition column specified in the create table or create index statement can be used.

boundary_value is a constant expression that can reference a variable. This includes user-defined type variables, functions, and user-defined functions. It cannot reference A Transact-SQL expression. boundary_value must match the data type provided in input_parameter_type or be implicitly converted to this data type, if the size and decimal places of the value do not match the corresponding value in input_parameter_type , this value cannot be truncated during implicit conversion.

Note:

If boundary _ value contains datetime or smalldatetime text value, it is assumed that us_english is the session language when these text values are calculated. This action is not recommended. To ensure that the partition function definition has the expected behavior for all session languages, we recommend that you use constants that are interpreted in the same way for all language settings, such as yyyymmdd format; or explicitly convert the text value to a specific style. For more information, see compile an international Transact-SQL statement. To determine the language Session of the server, run select @ language .

... N

SpecifyBoundary_valueThe number of provided values cannot exceed 999. The number of created partitions is equalN+ 1. You do not need to list values in order. If the values are not listed in order, the database engine sorts them, creates a function, and returns a warning, indicating that the values are not provided in order. IfNIf any duplicate value is included, the database engine returns an error.

Left | right

When the interval values are sorted from left to right by the database engine in ascending order,Boundary_value[,... N] Which side of each boundary value interval (left or right ). If not specified, the default value is left. For more information, see examples.

Remarks

The scope of a partition function is limited to the database in which the partition function is created. In this database, partition functions reside in a separate namespace different from other functions.

All rows whose partition column is null are placed in the leftmost partition, unless null is specified as the Boundary Value and right is specified. In this case, the leftmost partition is an empty partition, And the null value is placed in the following partition.

Permission

You can use any of the following permissions to execute the create partition function:

• Alter any DataSpace permission. By default, this permission is grantedSysAdminFixed server roles andDb_ownerAndDb_ddladminA member of a fixed database role.
• The control or alter permissions of the database in which the partition function is created.
• The control server or alter any database permission of the server on which the database where the partition function is created is located.

Example

A. Create a range left partition function for the int Column

The following partition functions divide a table or index into four partitions.

Copy code

 
Create Partition Function myrangepf1 (INT) as range left for values (0, 1,100,100 );
 

The following table lists the partition columns.Col1How to partition a table using this partition function.

Partition	1	2	3	4
Value	Col1<=1	Col1>1AndCol1<=100	Col1>100AndCol1<= 1000	Col1>1000

B. Create a range right partition function for the int Column

The following partition functions use the sameBoundary_value[,... N] Value, but it specifies range right.

Copy code

 
Create Partition Function myrangepf2 (INT) as range right for values (0, 1,100,100 );
 

The following table lists the partition columns.Col1How to partition a table using this partition function.

Partition	1	2	3	4
Values	Col1<1	Col1> =1AndCol1<100	Col1> =100AndCol1<1000	Col1> =1000

C. Create a range right partition function for the datetime Column

The following partition functions divide a table or index into 12 partitions. Each partition correspondsDatetimeThe value of a month in a year in the column.

Copy code

Create Partition Function [mydaterangepf1] (datetime) as range right for values ('000000', '000000', '000000', '000000', '000000', '000000 ', '123', '123', '123', '123', '123 ');
 

The following table lists the partition columns.DatecolHow to partition a table or index using this partition function.

Partition	1	2	...	11	12
Value	Datecol<February 1, 2003	Datecol> =February 1, 2003AndDatecol<March 1, 1, 2003		Datecol> =November 1, 2003AndCol1<December 1, 2003	Col1> =December 1, 2003

D. Creating a partition function on a char Column

The following partition functions divide a table or index into four partitions.

Copy code

Create Partition Function myrangepf3 (char (20) as range right for values ('ex ', 'rxe', 'xr ');
 

The following table lists the partition columns.Col1How to partition a table using this partition function.

Partition	1	2	3	4
Value	Col1<Ex...	Col1> =ExAndCol1<Rxe...	Col1> =RxeAndCol1<XR...	Col1> =XR