Partilhar via

CREATE PARTITION FUNCTION (Transact-SQL)

  • Artigo

Creates a function in the current database that maps the rows of a table or index into partitions based on the values of a specified column. Using CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

Arguments

  • partition_function_name
    Is the name of the partition function. Partition function names must be unique within the database and comply with the rules for identifiers.

  • input_parameter_type
    Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

    The actual column, known as a partitioning column, is specified in the CREATE TABLE or CREATE INDEX statement.

  • boundary_value
    Specifies the boundary values for each partition of a partitioned table or index that uses partition_function_name. If boundary_value is empty, the partition function maps the whole table or index using partition_function_name into a single partition. Only one partitioning column, specified in a CREATE TABLE or CREATE INDEX statement, can be used.

    boundary_value is a constant expression that can reference variables. This includes user-defined type variables, or functions and user-defined functions. It cannot reference Transact-SQL expressions. boundary_value must either match or be implicitly convertible to the data type supplied in input_parameter_type, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type.

    Note

    If boundary_value consists of datetime or smalldatetime literals, these literals are evaluated assuming that us_english is the session language. This behavior is deprecated. To make sure the partition function definition behaves as expected for all session languages, we recommend that you use constants that are interpreted the same way for all language settings, such as the yyyymmdd format; or explicitly convert literals to a specific style. For more information, see Writing International Transact-SQL Statements. To determine the language session of your server, run SELECT @@LANGUAGE.

  • ...n
    Specifies the number of values supplied by boundary_value, not to exceed 999. The number of partitions created is equal to n + 1. The values do not have to be listed in order. If the values are not in order, Microsoft SQL Server 2005 Database Engine sorts them, creates the function, and returns a warning that the values are not provided in order. The Database Engine returns an error if n includes any duplicate values.
  • LEFT | RIGHT
    Specifies to which side of each boundary value interval, left or right, the boundary_value [ ,...n ] belongs, when interval values are sorted by the Database Engine in ascending order from left to right. If not specified, LEFT is the default. For more information, see Examples.

Remarks

The scope of a partition function is limited to the database that it is created in. Within the database, partition functions reside in a separate namespace from the other functions.

Any rows whose partitioning column has null values are placed in the left-most partition, unless NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.

Permissions

Any one of the following permissions can be used to execute CREATE PARTITION FUNCTION:

  • ALTER ANY DATASPACE permission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.
  • CONTROL or ALTER permission on the database in which the partition function is being created.
  • CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition function is being created.

Examples

A. Creating a RANGE LEFT partition function on an int column

The following partition function will partition a table or index into four partitions.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4

Values

col1 <= 1

col1 > 1 AND col1 <= 100

col1 > 100 AND col1 <= 1000

col1 > 1000

B. Creating a RANGE RIGHT partition function on an int column

The following partition function uses the same values for boundary_value [ ,...n ] as the previous example, except it specifies RANGE RIGHT.

CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4

Values

col1 < 1

col1 >= 1 AND col1 < 100

col1 >= 100 AND col1 < 1000

col1 >= 1000

C. Creating a RANGE RIGHT partition function on a datetime column

The following partition function partitions a table or index into 12 partitions, one for each month of a year's worth of values in a datetime column.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
               '20030501', '20030601', '20030701', '20030801', 
               '20030901', '20031001', '20031101', '20031201');

The following table shows how a table or index that uses this partition function on partitioning column datecol would be partitioned.

Partition 1 2 ... 11 12

Values

datecol < February 1, 2003

datecol >= February 1, 2003 AND datecol < March 1, 2003

datecol >= November 1, 2003 AND col1 < December 1, 2003

col1 >= December 1, 2003

D. Creating a partition function on a char column

The following partition function partitions a table or index into four partitions.

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4

Values

col1 < EX...

col1 >= EX AND col1 < RXE...

col1 >= RXE AND col1 < XR...

col1 >= XR

See Also

Reference

$PARTITION (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.partition_functions (Transact-SQL)
sys.partition_parameters (Transact-SQL)
sys.partition_range_values (Transact-SQL)
sys.partitions (Transact-SQL)
sys.tables (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the Note in the boundary_value argument to clarify that datetime or smalldatetime literals are evaluated assuming us_english is the session language.
  • Added example C that shows how to create a partition function on a datetime column.