sql - SQL Server 分区。为明年创建新分区
问题描述
我有一个分区方案设置，目前按年份划分。我正在尝试为明年创建一个新分区：
ALTER PARTITION SCHEME psYearlyPartition_Log
NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
SPLIT RANGE('20190101');
然而，这运行了好几个小时并且没有完成。有谁知道为什么会发生这种情况，因为似乎还没有任何 2019 年的数据？
最后一个分区是从 2016-01-01 到今天。
我在这篇文章的底部运行了查询，结果如下：
CREATE PARTITION FUNCTION [pfYearlyPartition](datetime) AS RANGE LEFT FOR VALUES('2012-01-01T00:00:00','2013-01-01T00:00:00','2014-01-01T00:00:00','2015-01-01T00:00:00','2016-01-01T00:00:00');
CREATE PARTITION SCHEME [psYearlyPartition_Table1] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table2] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table3] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table4] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table5] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table6] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table7] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table8] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table9] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table10] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table11] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table12] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table13] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table14] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table15] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table16] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table17] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table18] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table19] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table20] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table21] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table22] AS PARTTITION [pfYearlyPartition] TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup],[YEAR11FileGroup]);
SELECT
OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
,OBJECT_NAME(pstats.object_id) AS TableName
,ps.name AS PartitionSchemeName
,ds.name AS PartitionFilegroupName
,pf.name AS PartitionFunctionName
,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
,prv.value AS PartitionBoundaryValue
,c.name AS PartitionKey
,CASE
WHEN pf.boundary_value_on_right = 0
THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))
ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
END AS PartitionRange
,pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
,p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
ORDER BY TableName, PartitionNumber;
SELECT
N'CREATE PARTITION FUNCTION '
+ QUOTENAME(pf.name)
+ N'(' + t.name + N')'
+ N' AS RANGE '
+ CASE WHEN pf.boundary_value_on_right = 1 THEN N'RIGHT' ELSE N'LEFT' END
+ ' FOR VALUES('
+
(SELECT
STUFF((SELECT
N','
+ CASE
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'char', N'varchar')
THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'nchar', N'nvarchar')
THEN N'N' + QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'date'
THEN QUOTENAME(FORMAT(CAST(r.value AS date), 'yyyy-MM-dd'),'''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime'
THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss'),'''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'datetime', N'smalldatetime')
THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss.fff'),'''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime2'
THEN QUOTENAME(FORMAT(CAST(r.value AS datetime2), 'yyyy-MM-ddTHH:mm:ss.fffffff'),'''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetimeoffset'
THEN QUOTENAME(FORMAT(CAST(r.value AS datetimeoffset), 'yyyy-MM-dd HH:mm:ss.fffffff K'),'''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'time'
THEN QUOTENAME(FORMAT(CAST(r.value AS time), 'hh\:mm\:ss\.fffffff'),'''') --'HH\:mm\:ss\.fffffff'
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'uniqueidentifier'
THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN (N'binary', N'varbinary')
THEN CONVERT(nvarchar(4000), r.value, 1)
ELSE CAST(r.value AS nvarchar(4000))
END
FROM sys.partition_range_values AS r
WHERE pf.[function_id] = r.[function_id]
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
)
+ N');'
FROM sys.partition_functions pf
JOIN sys.partition_parameters AS pp ON
pp.function_id = pf.function_id
JOIN sys.types AS t ON
t.system_type_id = pp.system_type_id
AND t.user_type_id = pp.user_type_id
SELECT
N'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name)
+ N' AS PARTTITION ' + QUOTENAME(pf.name)
+ N' TO ('
+
(SELECT
STUFF((SELECT
N',' + QUOTENAME(fg.name)
FROM sys.data_spaces ds
JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
WHERE ps.data_space_id = ds.data_space_id
ORDER BY dds.destination_id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
)
+ N');'
FROM sys.partition_schemes AS ps
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
解决方案
有谁知道为什么会发生这种情况，因为似乎还没有任何 2019 年的数据？
当您拆分非空分区时，现有分区将拆分为 2 个单独的分区，并将行移动到新创建的分区中以适应新的边界。这是一个非常昂贵的操作，需要大约 4 倍于正常 DML 操作的日志记录。应避免拆分非空分区，尤其是对于您显然拥有的大表。
为避免此类数据移动，您可以使用临时表和临时分区功能/方案进行分区维护。这将允许您将要拆分的分区切换到临时表中，用于CREATE INDEX...WITH (DROP EXISTING=ON)创建新分区，最后SWITCH将分区返回到主表中。
如果您需要有关执行此操作的脚本的帮助，请将现有分区函数和方案的 CREATE DDL 添加到您的问题中。RANGE LEFT或之类的细节RIGHT很重要。我猜你有一个RANGE LEFT函数，它将所有大于“2016-01-01”且小于或等于“2019-01-01”的行移动到新创建的分区中。
编辑
我现在看到您有几十个表，它们都使用相同的底层函数进行了分区。NEXT USED生成的分区方案脚本中似乎存在错误，因为它包含一个额外的文件组，即使它实际上并未使用，它似乎也是文件组。我假设 Year11FileGroup 并未实际使用。
下面是 2 个表的示例脚本，您可以根据需要进行扩展。因为这些方案共享相同的功能，所以您需要同时对所有表执行每个步骤，因为拆分功能将影响所有方案以及使用这些方案分区的表和索引。NEXT USED我还为 2020 年未来边界添加了一个边界（在 YearFileGroup7 上），这样您就可以通过为每个方案设置文件组并拆分功能，在 2019 日历年结束之前主动创建 2021 年分区来避免这个问题。正如您最初尝试的那样。
--create temp function like original
CREATE PARTITION FUNCTION [pfYearlyPartition_temp](datetime) AS RANGE LEFT FOR VALUES('2012-01-01T00:00:00','2013-01-01T00:00:00','2014-01-01T00:00:00','2015-01-01T00:00:00','2016-01-01T00:00:00');
--create temp scheme like original for each scheme
CREATE PARTITION SCHEME [psYearlyPartition_Table1_temp] AS PARTITION pfYearlyPartition_temp TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup]);
CREATE PARTITION SCHEME [psYearlyPartition_Table2_temp] AS PARTITION pfYearlyPartition_temp TO ([YEAR1FileGroup],[YEAR2FileGroup],[YEAR3FileGroup],[YEAR4FileGroup],[YEAR5FileGroup],[YEAR6FileGroup]);
--for each table, create temporary staging table and indexes like original partitioned using temp scheme
CREATE TABLE dbo.Table1_temp(
Col1 datetime
, Col2 int
, Col3 char(1000)
) ON psYearlyPartition_Table1_temp(col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1_temp(Col2, Col1) ON psYearlyPartition_Table1_temp(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_temp(Col2) ON psYearlyPartition_Table1_temp(Col1);
CREATE TABLE dbo.Table2_temp(
Col1 datetime
, Col2 int
, Col3 char(1000)
) ON psYearlyPartition_Table2_temp(col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table2_temp(Col2, Col1) ON psYearlyPartition_Table2_temp(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table2_temp(Col2) ON psYearlyPartition_Table2_temp(Col1);
GO
--for each table, switch the partition containing the new boundary (the partition to be split) into temp table
ALTER TABLE dbo.Table1
SWITCH PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00')
TO dbo.Table1_temp PARTITION $PARTITION.pfYearlyPartition_temp('2019-01-01T00:00:00');
ALTER TABLE dbo.Table2
SWITCH PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00')
TO dbo.Table2_temp PARTITION $PARTITION.pfYearlyPartition_temp('2019-01-01T00:00:00');
GO
--for each original scheme, set NEXT USED filegrroup and split original function to create new boundaries
ALTER PARTITION SCHEME psYearlyPartition_Table1 NEXT USED [Year6FileGroup];
ALTER PARTITION SCHEME psYearlyPartition_Table2 NEXT USED [Year6FileGroup];
ALTER PARTITION FUNCTION pfYearlyPartition() SPLIT RANGE('2019-01-01T00:00:00');
ALTER PARTITION SCHEME psYearlyPartition_Table1 NEXT USED [Year7FileGroup];
ALTER PARTITION SCHEME psYearlyPartition_Table2 NEXT USED [Year7FileGroup];
ALTER PARTITION FUNCTION pfYearlyPartition() SPLIT RANGE('2020-01-01T00:00:00');
GO
--for each table, repartition temp table and indexes using the original scheme which now contains the new boundaries
CREATE CLUSTERED INDEX cdx ON dbo.Table1_temp(Col2, Col1) WITH (DROP_EXISTING=ON) ON psYearlyPartition_Table1(Col1) ;
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_temp(Col2) WITH (DROP_EXISTING=ON) ON psYearlyPartition_Table1(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table2_temp(Col2, Col1) WITH (DROP_EXISTING=ON) ON psYearlyPartition_Table2(Col1) ;
CREATE NONCLUSTERED INDEX idx ON dbo.Table2_temp(Col2) WITH (DROP_EXISTING=ON) ON psYearlyPartition_Table2(Col1);
GO
--for each table, switch temp table partitions back into main table
ALTER TABLE dbo.Table1_temp
SWITCH PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00')
TO dbo.Table1 PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00');
ALTER TABLE dbo.Table2_temp
SWITCH PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00')
TO dbo.Table2 PARTITION $PARTITION.pfYearlyPartition('2019-01-01T00:00:00');
--this partition should be empty but just in case...
ALTER TABLE dbo.Table1_temp
SWITCH PARTITION $PARTITION.pfYearlyPartition('2020-01-01T00:00:00')
TO dbo.Table1 PARTITION $PARTITION.pfYearlyPartition('2020-01-01T00:00:00');
ALTER TABLE dbo.Table2_temp
SWITCH PARTITION $PARTITION.pfYearlyPartition('2020-01-01T00:00:00')
TO dbo.Table2 PARTITION $PARTITION.pfYearlyPartition('2020-01-01T00:00:00');
GO
--drop temp tables, schemes, and function
DROP TABLE dbo.Table1_temp;
DROP TABLE dbo.Table2_temp;
DROP PARTITION SCHEME psYearlyPartition_Table1_temp;
DROP PARTITION SCHEME psYearlyPartition_Table2_temp;
DROP PARTITION FUNCTION pfYearlyPartition_temp;
GO
以下是针对每年 1M 行的测试表运行此脚本后的结果。请注意，RANGE LEFT 函数的最后一个分区是永久分区，它将始终是基础方案的一部分，因此无论 YEAR6FileGroup 是否包含数据，都将始终使用它。
我建议使用RANGE RIGHT增量时间值的函数，因为它更直观并且可以避免这个问题。另外，请注意，与RANGE LEFT边界值完全匹配的行最终将位于错误的分区中（例如，具有 '2016-01-01T00:00:00' 的行将与 2015 年数据位于同一分区中），这当划分时间包括时间分量时是一个考虑因素。我建议您仔细阅读此表分区最佳实践文章以获取更多信息。
+------------+-----------+--------------------------+------------------------+-----------------------+------------------------+-------------------+-------------------------+--------------+------------------------------------------------------------+-----------------+-------------------+-----------------+
| SchemaName | TableName | PartitionSchemeName | PartitionFilegroupName | PartitionFunctionName | PartitionFunctionRange | PartitionBoundary | PartitionBoundaryValue | PartitionKey | PartitionRange | PartitionNumber | PartitionRowCount | DataCompression |
+------------+-----------+--------------------------+------------------------+-----------------------+------------------------+-------------------+-------------------------+--------------+------------------------------------------------------------+-----------------+-------------------+-----------------+
| dbo | Table1 | psYearlyPartition_Table1 | YEAR1FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2012-01-01 00:00:00.000 | Col1 | Col1 > Infinity and Col1 <= Jan 1 2012 12:00AM | 1 | 0 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR2FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2013-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2012 12:00AM and Col1 <= Jan 1 2013 12:00AM | 2 | 1000000 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR3FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2014-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2013 12:00AM and Col1 <= Jan 1 2014 12:00AM | 3 | 1000000 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR4FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2015-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2014 12:00AM and Col1 <= Jan 1 2015 12:00AM | 4 | 1000000 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR5FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2016-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2015 12:00AM and Col1 <= Jan 1 2016 12:00AM | 5 | 1000000 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR6FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2019-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2016 12:00AM and Col1 <= Jan 1 2019 12:00AM | 6 | 3000000 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR7FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2020-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2019 12:00AM and Col1 <= Jan 1 2020 12:00AM | 7 | 0 | NONE |
| dbo | Table1 | psYearlyPartition_Table1 | YEAR6FileGroup | pfYearlyPartition | Range Left | Upper Boundary | NULL | Col1 | Col1 > Jan 1 2020 12:00AM and Col1 <= Infinity | 8 | 0 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR1FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2012-01-01 00:00:00.000 | Col1 | Col1 > Infinity and Col1 <= Jan 1 2012 12:00AM | 1 | 0 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR2FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2013-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2012 12:00AM and Col1 <= Jan 1 2013 12:00AM | 2 | 1000000 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR3FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2014-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2013 12:00AM and Col1 <= Jan 1 2014 12:00AM | 3 | 1000000 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR4FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2015-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2014 12:00AM and Col1 <= Jan 1 2015 12:00AM | 4 | 1000000 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR5FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2016-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2015 12:00AM and Col1 <= Jan 1 2016 12:00AM | 5 | 1000000 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR6FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2019-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2016 12:00AM and Col1 <= Jan 1 2019 12:00AM | 6 | 3000000 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR7FileGroup | pfYearlyPartition | Range Left | Upper Boundary | 2020-01-01 00:00:00.000 | Col1 | Col1 > Jan 1 2019 12:00AM and Col1 <= Jan 1 2020 12:00AM | 7 | 0 | NONE |
| dbo | Table2 | psYearlyPartition_Table2 | YEAR6FileGroup | pfYearlyPartition | Range Left | Upper Boundary | NULL | Col1 | Col1 > Jan 1 2020 12:00AM and Col1 <= Infinity | 8 | 0 | NONE |
+------------+-----------+--------------------------+------------------------+-----------------------+------------------------+-------------------+-------------------------+--------------+------------------------------------------------------------+-----------------+-------------------+-----------------+
推荐阅读
- elm - 如何让 Wms 端点与 elm-mapbox 一起使用？
- python - 列出多个匹配实例的交集
- tensorflow - 如何（手动）测量我的 GPU 内存有多少已使用/可用
- jsf - 动态子菜单不响应点击
- html - 使用 spring boot 从输入 type=date(freemarker) 获取日期的问题
- python-3.x - Pandas：使用 apply 将特定列中的行值复制到新列中
- c# - button.text 不会改变按钮文本 c#
- excel - 使用 R1C1 并将 Range 格式清除到工作表末尾的正确语法是什么？
- angular - 减轻在列表视图中调整 FullCalendar 的大小
- node.js - Nodejs错误无法读取未定义的属性'then'