In order to choose the best partition column for a table, it's critical to understand how the partition function works, because the partition function includes a data type that must be the same data type as the table's partition column. In this section, we'll look at how the partition function works for a simple integer value. In the next section, "Choosing a Partition Column," we'll take a closer look at other data types.
Partition functions are stored within the database you create them in, but they are not equivalent to user-defined functions. Instead, they are special objects used only with table partitioning.
Although user-created, partition functions differ from user-defined functions in a number of ways:
· Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
· Partition functions are not contained by a database schema.
· Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
· Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.
You create the partition function with the CREATE PARTITION FUNCTION command, specifying:
· An input parameter to the partition function declaring the data type of the partition boundaries.
· The input parameter must be compatible with subsequent table partition columns.
· A range type (either LEFT or RIGHT), which specifies how the boundary values of the partition function will be put into the resulting partitions.
· A set of constants as an initial list of boundary values. These mark the terminating points of partitions within the stated range. The boundary points must have the same data type as the input parameter. The boundary values can be modified later using ALTER PARTITION FUNCTION.
You can script out an existing partition function with Object Explorer in SQL Server Management Studio. Expand the Storage node of a database, expand the Partition Functions node, right-click the partition function name, and then click Script Partition Function as.
When you choose a data type for a partition function, you are committing yourself to creating partitions within the range of all possible values of that data type. Every allowable SQL Server data type determines an ordered range of possible values:
· The values are discrete and finite in number.
· The values are implicitly ordered from a minimum to a maximum.
· NULL is considered smaller than the minimum value of the data type’s values.