滑动窗口方案

当下一个月的数据（在本例中是 2004 年 10 月）可用时，将按特定的操作顺序使用现有的文件组、移入和移出数据。而在本销售方案中，目前 FG1 中的数据是 2002 年 10 月的数据。现在 2004 年 10 月的数据是可用的，因此，根据可用空间和存档要求，您有两个选择。记住，要将分区从表中快速移入或移出，移动操作必须只更改元数据。

特别是，必须在要移入或移出的同一个文件组中创建新表（源或目标，即伪造的分区）。如果您打算继续使用相同的文件组（本例中为 FG1），则需要确定如何满足空间和存档要求。当表中没有完整的两个年度的数据时，为了最大程度地缩短时间，并且如果拥有足够的空间，您可以将当前数据（2004 年 10 月）加载到 FG1 中，而无需删除要存档的数据（2002 年 10 月）。但是，如果没有足够的空间同时保留当前月份和要存档的月份，则需要先移出旧的分区（然后再删除它）。 

不管怎样，存档应该很容易，并且可能已经完成。好的存档做法是，加载和移入新分区之后立即备份文件组，而不要等到打算移出分区时再进行备份。例如，如果 RAID 阵列出现故障，则可以恢复文件组，而无需重新生成或重新加载数据。具体到本例中，因为数据库是最近才分区的，所以您可能已经在分区结构稳定后执行了完整的备份。

当然，完整的数据库备份并非唯一的选择。在 SQL Server 2005 中可以实现各种各样的备份策略，而且许多备份策略都可以为备份和恢复提供更好的准确性。因为这么多的数据都是不变的，所以您可以在加载后备份各个文件组。实际上，这应该是滚动分区策略的一部分。

现在，策略已经就位，您需要了解确切的处理过程和语法。语法和步骤数可能看起来很复杂，但每个月的处理过程都是相同的。通过使用动态 SQL 执行，您可以按照以下步骤轻松地使此过程自动化： 


  • 管理将要移入的分区的分段表。 
  • 管理将要移出的分区的第二个分段表。 
  • 将旧数据移出分区表，并将新数据移入分区表。 
  • 删除分段表。 
  • 备份文件组。 


后面各节详细介绍了每个步骤的语法和最佳做法，还提供了注释，以帮助您通过动态 SQL 执行使此过程自动化。



管理将要移入的分区的分段表



  • 创建分段表（将来的伪造分区）。这个分段表必须有一个约束，将其数据限制为只对要创建的分区有效的数据。为了获取更好的性能，将数据加载到未建立索引且未应用约束的堆中，然后在将表移入分区表之前添加约束（参见步骤 3）WITH CHECK。
1.  CREATE TABLE SalesDB.[dbo].[OrdersOctober2004] 
2.  (
3.  [OrderID] [int] NOT NULL,
4.  [EmployeeID] [int] NULL,
5.  [VendorID] [int] NULL,
6.  [TaxAmt] [money] NULL,
7.  [Freight] [money] NULL,
8.  [SubTotal] [money] NULL,
9.  [Status] [tinyint] NOT NULL,
10.  [RevisionNumber] [tinyint] NULL,
11.  [ModifiedDate] [datetime] NULL,
12.  [ShipMethodID] [tinyint] NULL,
13.  [ShipDate] [datetime] NOT NULL, 
14.  [OrderDate] [datetime] NOT NULL, 
15.  [TotalDue] [money] NULL
16.  ) ON [FG1]
17.  GO
  1. 复制代码 在自动化过程中：此表很容易创建，因为它总是代表当前月份。根据进程运行的时间，使用 DATENAME(m, getdate()) 等内置函数检测月份是很容易的。因为表的结构必须与现有表相匹配，所以每个月的主要变动是表名称。但是，您可以为每个月使用相同的名称，因为将表添加到分区中之后，表即不需要再存在。虽然将数据移入分区表后该表仍然存在，但您可以在移动操作完成后删除分段表。另外，必须更改日期范围。因为您要处理的是 datetime数据，而在时间的存储方式方面又存在舍入问题，所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值，最容易的方法是将正在处理的月份加上 1 个月，然后再减去 2 或 3 毫秒。不能只减去 1 毫秒，因为 59.999 会上舍入为 .000，即下个月的第一天。可以减去 2 或 3 毫秒，因为 2 毫秒将向下舍入为 .997，而 3 毫秒等于 .997；.997 是可以存储的有效值。这样即可确定 datetime
  1. DECLARE @Month nchar(2),
  2. @Year nchar(4),
  3. @StagingDateRange nchar(10)
  4. SELECT @Month = N'11', @Year = N'2004'
  5. SELECT @StagingDateRange = @Year + @Month + N'01'
  6. SELECT dateadd(ms, -2, @StagingDateRange)
  7. 复制代码
  • 每个月会重新创建表，因为它需要保留在要移入和移出数据的文件组中。要确定要处理的相应文件组，请将以下系统表查询与前面介绍的 $partition
2.  SELECT ps.name AS PSName, 
3.  dds.destination_id AS PartitionNumber, 
4.  fg.name AS FileGroupName
5.  FROM (((sys.tables AS t 
6.  INNER JOIN sys.indexes AS i 
7.  ON (t.object_id = i.object_id))
8.  INNER JOIN sys.partition_schemes AS ps 
9.  ON (i.data_space_id = ps.data_space_id))
10.  INNER JOIN sys.destination_data_spaces AS dds 
11.  ON (ps.data_space_id = dds.partition_scheme_id))
12.  INNER JOIN sys.filegroups AS fg
13.  ON dds.data_space_id = fg.data_space_id
14.  WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
15.  dds.destination_id = $partition.TwoYearDateRangePFN('20021001')
  1. 复制代码
  • 加载包含数据的分段表。如果文件是一致的，此过程应该通过 BULK INSERT 语句执行。 
    在自动化过程中：这是自动化过程最复杂的部分。您需要确保所有文件都已经加载，还应考虑并行加载这些文件。跟踪加载了哪些文件以及文件位置的表可以帮助您控制此过程。您可以创建一个 SQL Agent 作业，每隔几分钟检查一次文件，拾取新文件并执行多个 BULK INSERT 语句。 
  • 加载数据后，即可添加约束。为了使数据可信，必须添加约束 WITH CHECK。WITH CHECK 设置是默认的，因此不需要指定，但一定不能设置为 WITH NOCHECK。 
  • 为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区）具有相同的群集索引。
2.  ALTER TABLE [OrdersOctober2004]
3.  ADD CONSTRAINT OrdersOctober2004PK 
4.  PRIMARY KEY CLUSTERED (OrderDate, OrderID)
5.  ON [FG1]
6.  GO
  1. 复制代码 在自动化过程中：这是一个非常容易的步骤。使用步骤 1 中的月份和文件组信息，可以创建此群集索引。
1.  ALTER TABLE SalesDB.[dbo].[OrdersOctober2004] 
2.  WITH CHECK
3.  ADD CONSTRAINT OrdersRangeYearCK
4.  CHECK ([OrderDate] >= '20041001' 
5.  AND [OrderDate] <= '20041031 23:59:59.997'
  1. )
  2. 复制代码

管理将要移出的分区的第二个分段表。


  • 创建第二个分段表。这是一个空表，用于存储移出的分区中的数据。
1.  CREATE TABLE SalesDB.[dbo].[OrdersOctober2002] 
2.  (
3.  [OrderID] [int] NOT NULL,
4.  [EmployeeID] [int] NULL,
5.  [VendorID] [int] NULL,
6.  [TaxAmt] [money] NULL,
7.  [Freight] [money] NULL,
8.  [SubTotal] [money] NULL,
9.  [Status] [tinyint] NOT NULL,
10.  [RevisionNumber] [tinyint] NULL,
11.  [ModifiedDate] [datetime] NULL,
12.  [ShipMethodID] [tinyint] NULL,
13.  [ShipDate] [datetime] NOT NULL, 
14.  [OrderDate] [datetime] NOT NULL, 
15.  [TotalDue] [money] NULL
16.  ) ON [FG1]
17.  GO
  1. 复制代码
  • 为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区，而该分区将成为此表）具有相同的群集索引。
2.  ALTER TABLE [OrdersOctober2002]
3.  ADD CONSTRAINT OrdersOctober2002PK 
4.  PRIMARY KEY CLUSTERED (OrderDate, OrderID)
5.  ON [FG1]
6.  GO
  1. 复制代码

将旧数据移出分区表，并将新数据移入分区表


  • 移出旧数据，放入第二个分段表中。
1.  ALTER TABLE Orders
2.  SWITCH PARTITION 1
3.  TO OrdersOctober2002
4.  GO
  1. 复制代码
  • 更改分区函数以删除 2002 年 10 月的边界点。
6.  ALTER PARTITION FUNCTION TwoYearDateRangePFN()
7.  MERGE RANGE ('20021031 23:59:59.997')
8.  GO
  1. 复制代码
  • 此操作还会删除文件组与分区架构之间的关联。具体来说，FG1 将不再是分区架构的一部分。因为您将滚动相同的现有 24 个分区的新数据，所以需要使 FG1 成为“下一个使用的”分区，此分区将是下一个用于拆分的分区。
2.  ALTER PARTITION SCHEME TwoYearDateRangePScheme 
3.  NEXT USED [FG1]
4.  GO
5.            复制代码
• 更改分区函数，为 2004 年 10 月添加新的边界点。
6.  ALTER PARTITION FUNCTION TwoYearDateRangePFN() 
7.  SPLIT RANGE ('20041031 23:59:59.997')
8.  GO
9.            复制代码
• 更改基础表的约束定义（如果存在），以允许新范围的数据。因为添加约束的代价可能很昂贵（需要验证数据），所以最好的做法是继续扩大日期范围，而不是删除并重新创建约束。现在，只存在一个约束 (OrdersRangeYearCK)，但以后将存在两个约束。
10.  ALTER TABLE Orders
11.  ADD CONSTRAINT OrdersRangeMaxOctober2004
12.  CHECK ([OrderDate] < '20041101')
13.  GO
14.  ALTER TABLE Orders
15.  ADD CONSTRAINT OrdersRangeMinNovember2002
16.  CHECK ([OrderDate] >= '20021101')
17.  GO
18.  ALTER TABLE Orders
19.  DROP CONSTRAINT OrdersRangeYearCK
20.  GO
21.            复制代码
• 从第一个分段表中移入新数据。
22.  ALTER TABLE OrdersOctober2004
23.  SWITCH TO Orders PARTITION 24
24.  GO
25.            复制代码




 


删除分段表



因为下一个步骤（也是最后一个步骤）将存档所有数据，所以不再需要分段数据。删除表是最快的方式。 

    1.  DROP TABLE dbo.OrdersOctober2002
2.  GO
3.  DROP TABLE dbo.OrdersOctober2004
4.  GO


    复制代码

    备份文件组



    最后一步备份的对象是根据您的备份策略选择的。如果选择了基于文件或文件组的备份策略，则应执行文件或文件组备份。如果选择了基于整个数据库的备份策略，则可以执行完整数据库备份或差异备份。 



      1.  BACKUP DATABASE SalesDB 
2.  FILEGROUP = 'FG1' 
3.  TO DISK = 'C:\SalesDB\SalesDB.bak'
4.  GO


      复制代码

      列表分区：地区数据



      如果您的表包含的数据来自多个地区，而通常只对一个地区的数据进行分析，或者定期从每个地区接收数据，请考虑以列表的形式使用已定义的范围分区。换句话说，就是使用函数将每个分区定义为一个地区值。例如，有一家西班牙的公司，它的客户分布在西班牙、法国、德国、意大利和英国。该公司的销售数据总是按国家进行分析。该公司的表可以拥有 5 个分区，每个国家一个分区。 



      这样一个列表分区的创建过程与日期的范围分区几乎完全相同，不同之处在于，除了实际的分区键外，该范围的边界没有任何其他值。实际上，它是一个列表，而不是范围。尽管它是一个列表，但边界条件必须包含最左侧和最右侧。要创建 5 个分区，只需在分区函数中指定 4 个值。不需要为这些值排序（SQL Server 将在内部为它们排序），但是为了获得正确的分区数，最符合逻辑的方法是对分区值进行排序，将最大值指定给最后一个分区（定义为 LEFT 分区函数时），或者对分区值进行排序，并从倒数第二个值开始（对于 RIGHT）。



      因为有 5 个分区，所以必须有 5 个文件组。在本例中，这些文件组根据存储的数据命名。脚本文件 RegionalRangeCaseStudyFilegroups.sql 充分显示了此语法。每个文件组都可以使用相同的设置进行创建，但如果数据不平衡，则不必这么做。只显示了西班牙的文件组和文件；其他四个文件组和文件中都具有相同的参数，但位于不同的驱动器上，而且具有特定于国家分区的名称。 



      1.  ALTER DATABASE SalesDB
2.  ADD FILEGROUP [Spain]
3.  GO
4.  ALTER DATABASE SalesDB 
5.  ADD FILE 
6.  (NAME = N'SalesDBSpain',
7.  FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
8.  SIZE = 1MB,
9.  MAXSIZE = 100MB,
10.  FILEGROWTH = 5MB) 
11.  TO FILEGROUP [Spain]
12.  GO


      复制代码

      下一步是创建函数，该函数将指定只有四个分区使用 LEFT 作为边界条件。在本例中，列表将包括除英国以外的所有国家，因为英国在按字母顺序排列的列表中位于最后。 


      1.  CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
2.  AS
3.  RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
4.  GO

      复制代码

      要将数据放入根据数据命名的文件组中，分区架构将按字母顺序列出。所有五个文件组都必须在分区架构的语法中指定。 


      1.  CREATE PARTITION SCHEME [CustomersCountryPScheme]
2.  AS
3.  PARTITION CustomersCountryPFN 
4.  TO ([France], [Germany], [Italy], [Spain], [UK])
5.  GO

      复制代码

      最后，可以在新的 CustomersCountryPScheme 中创建 Customers 


      1.  CREATE TABLE [dbo].[Customers](
2.  [CustomerID] [nchar](5) NOT NULL,
3.  [CompanyName] [nvarchar](40) NOT NULL,
4.  [ContactName] [nvarchar](30) NULL,
5.  [ContactTitle] [nvarchar](30) NULL,
6.  [Address] [nvarchar](60) NULL,
7.  [City] [nvarchar](15) NULL,
8.  [Region] [nvarchar](15) NULL,
9.  [PostalCode] [nvarchar](10) NULL,
10.  [Country] [char](7) NOT NULL,
11.  [Phone] [nvarchar](24) NULL,
12.  [Fax] [nvarchar](24) NULL
13.  ) ON CustomersCountryPScheme (Country)
14.  GO

      复制代码

      尽管范围分区被定义为只支持范围，但它们还为执行其他类型的分区（例如列表分区）提供了一种方法。





      总结

       


      使用 SQL Server ，我们可以通过分区轻松一致地管理大量表和索引，从而使您可以在活动表之外管理数据子集。这提供了简化的管理、改善的性能以及抽象的应用程序逻辑，因为分区架构对于应用程序是完全透明的。



      如果我们的数据具有逻辑分组（范围或列表），而大量查询必须分析这些预定义的、一致的范围内的数据，还需要管理这些预定义范围内的传入数据和传出数据，则范围分区是简单的选择。如果我们查看对大量数据进行的分析（没有使用特定范围），或者所有查询都访问大多数数据（如果不是所有数据），那么使用多个没有任何特定放置方法的文件组是一种更容易的解决方案，此解决方案还可以改善性能。