However, the partition scheme is not as desired. This SPLIT results in adding the new boundary to the function as expected: Now, we need to prepare for year 2015 so we add a new filegroup, set the NEXT USED filegroup, SPLIT the function for year 2015, and insert data for 2015:
This initial setup results in 2 partitions and data properly mapped to the 2 yearly filegroups of the scheme. ( NAME = N'FG_2015_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2015_1.ndf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )ĪLTER PARTITION FUNCTION PF_DateTimeLeft() INSERT INTO dbo.PartitionedTableLeft VALUES ( NAME = N'Log_1', FILENAME = N'C:\SqlLogFiles\PartitioningDemo_Log_1.ldf', SIZE = 100MB, MAXSIZE = 10GB, FILEGROWTH = 10MB) ĬREATE PARTITION FUNCTION PF_DateTimeLeft(datetime) ASĬREATE PARTITION SCHEME PS_DateTimeLeft AS ( NAME = N'FG_2014_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2014_1.ndf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ) ( NAME = N'FG_2013_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2013_1.ndf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ), ( NAME = N'Primary', FILENAME = N'C:\SqlDataFiles\PartitioningDemo.mdf', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ), Consider this example of a RANGE LEFT partition function on a datetime column, where the initial setup is for 2 years of data (20):ĬREATE DATABASE PartitioningDemo ON PRIMARY RANGE LEFT behavior is not intuitive and trips up many DBAs but I will discuss for completeness. I generally suggest one use RANGE RIGHT instead because it is more natural, and helps avoid common pitfalls when adding incremental partition boundaries. For each table/index using the affected partition scheme(s), move rows from the existing split partition that are greater than or equal to the new boundary into the newly create partition on the right.Create a new partition to the right of the existing one on the NEXT USED filegroup for each partition scheme that uses the function.Identify existing partition to be split, which is the one that contains the new boundary ( or the first partition if no existing boundaries are less than the one being added).The actions performed by a SPLIT of a RANGE RIGHT partition function: For each table/index using the affected partition scheme(s), move rows from the existing split partition that are less than or equal to the new boundary into the newly created partition on the left.Create a new partition to the left of the existing one on the NEXT USED filegroup of each partition scheme that uses the function.Add the new boundary to the partition function, maintaining boundary order and incrementing subsequent partition numbers.Identify existing partition to be split, which is the one that contains the new boundary ( or the last partition if no existing boundaries are higher than the one being added).
The actions performed by a SPLIT of a RANGE LEFT partition function: Below are the actions performed when a LEFT or RIGHT partition is SPLIT, with important the differences in bold: A partition function SPLIT splits an existing partition into 2 separate ones, changing all of the underlying partition schemes, tables, and indexes. New partitions are created by splitting a partition function.