SQL Server 2005 Ch 6

1. SQL Server 2005 Ch 6 Creating Partitions

2. Partitions • Allows DBA to split specific pieces of a table or index onto individual file groups • Aids in managing large tables by allowing segragating older records to different file groups • Can target only certain filegroups for backups (speeds backup and restore operations) • To partition a table requires 3 steps • Create a partition function • Create a partition scheme mapped to a partition function • Create the table or index on the partition scheme • Partitioning cannot be completed using the GUI in SSMS,must be coded

3. Creating a Partition Function • Partition function is a stand alone object that defines the boundary points for data partitioning • Command: CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] • Input parameter types cannot be of text, ntext, varchar(max), image, xml, timestamp, nvarchar(max), varbinary(max), UDT, CLR • Range defines which partition a boundary point belongs to • Values specifies the list of boundary points • Example: CREATE PARTITION FUNCTION partfunc (int) as RANGE LEFT FOR VALUES(1000, 2000, 3000, 4000, 5000); • The number of filegroups needed will be one more then the count of boundaries

4. Creating a Partition Scheme • Partition Scheme defines the physical storage structures or filegroups that will be used with a specific partition function • Command: CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) [ ; ] • PARTITION names a partition function that was previously created • TO specifies the filegroups that will hold the data • Example: CREATE PARTITION SCHEME partscheme AS PARTITION partfunc TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6]);

5. Partitioning New Tables • After creating the partition function and partition scheme, you create your tables or indexes on the partition scheme utilizing the ON clause, identifying the column that will be used for partitioning • Example: CREATE TABLE dbo.CustomerAddress (CustomerAddressID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, AddressTypeID tinyint NOT NULL, AddressLine1 varchar(30) NOT NULL, AddressLine2 varchar(30) NOT NULL, City varchar(50) NOT NULL, StateProvinceID int NULL, PostalCode char(10) NULL, CountryID int NULL) on partscheme(CustomerAddressID);

6. Partitioning New Indexes • For clustered index on partition, indexed column must match partition function parameter type • Example: CREATE CLUSTERED INDEX idx_CustomerAddress_CustomerAddressID ON dbo.CustomerAddress(CustomerAddressID) ON partscheme(CustomerAddressID); • For non-clustered index on partition, indexed column does not have to match partition function parameter type • Example: CREATE NONCLUSTERED INDEX idx_CustomerAddress_City ON dbo.CustomerAddress(City) ON partscheme(CustomerAddressID);

7. Partitioning on Existing Tables or Indexes • You can partition existing tables or partitions without dropping and recreating them. • Use the following steps to perform this procedure • Create partition function • Create partition scheme • Drop existing clustered index • Recreate the clustered index on partition scheme • You can partition each nonclustered index by using a different partition function and scheme then the table, but you cannot partition the clustered index differently then the table.

8. Querying Partitions • To query data on partitions for normal operations, you do nothing different • If you need to query information for a particular partition you use the $PARTITION function • $PARTITION function returns a partition number based on the column values for the particular partition function • Example: SELECT $PARTITION.PARTFUNC (2784) AS [PartitionNum]; • Example: SELECT * FROM dbo.CustomerAddress WHERE $PARTITION.PARTFUNC (CustomerAddressID) = 3

9. Managing Partitions • Use the alter partition function and alter partition scheme functions adjust a partition • Alter partition function will add or delete boundary points for a partition • Command: ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) } [ ; ] • Alter scheme function adds more filegroups to the partition scheme or re-points a former filegroup to be used next when alter partition function is used • Command: ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ] [ ; ]

10. Managing Partitions Cont. • Adding or removing rows from a table can be an extensive I/O operation • If you are using partitions, you can use the switch operation of the alter table statement to change the pointers for data pages that can add or remove rows of data • In order for this to work, 4 things be true • A full partition must be switched with and empty partition • Both tables must be aligned(same partition function and partition scheme) • Both tables must have exactly the same structure for tables as well as indexes • The range of values in the partition being switched must not exist in the target table • Command: ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ] }[ ; ]