Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 86 of 212

74 C H A P T E R 5 | Broader data access Note You can also partition the current table and switch data into a partition with system versioning enabled. However, you cannot switch data out of the current table until you disable system versioning. To start, you need to configure the initial partitioning configuration for the history table. Let's assume that you want historical data for up to three months in separate monthly partitions and that you enabled system versioning in January 2016. That means you need to create three partitions for the sliding window and one more empty partition to ensure a destination for new history rows added after the last month's partition range, as shown in Example 5-9. Example 5-9: Creating partitions for a history table -- Create partition function CREATE PARTITION FUNCTION [fn_Partition_ProductInventoryHistory_By_ValidTo] (datetime2(7)) AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.999' , N'2016-02-29T23:59:59.999' , N'2016-03-31T23:59:59.999'); -- Create partition scheme CREATE PARTITION SCHEME [sch_Partition_ProductInventoryHistory_By_ValidTo] AS PARTITION [fn_Partition_ProductInventoryHistory_By_ValidTo] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]); -- Re-create index for alignment with partition schema CREATE CLUSTERED INDEX [ix_ProductInventoryHistory] ON [History].[ProductInventory_History] ([ValidFrom] ASC, [ValidTo] ASC) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , DATA_COMPRESSION = PAGE) ON [sch_Partition_ProductInventoryHistory_By_ValidTo] ([ValidTo]); When the three partitions are full, you start the recurring partition maintenance task and schedule it to run once per month thereafter. Before you start, create a staging table with the same structure as the history table and add a clustered index on the period column dates and a constraint on the end- period column for the last day of the partition, as shown in Example 5-10. Example 5-10: Creating a staging table for partition switching -- Create staging table CREATE TABLE [History].[Staging_ProductInventory_History] ( [ProductID] [int] NOT NULL, [LocationID] [smallint] NOT NULL, [Shelf] [nvarchar](10) NOT NULL, [Bin] [tinyint] NOT NULL, [Quantity] [smallint] NOT NULL, [ModifiedDate] [datetime] NOT NULL, [ValidFrom] datetime2(7) NOT NULL, [ValidTo] datetime2(7) NOT NULL) -- Create clustered index on same filegroup as partition to switch CREATE CLUSTERED INDEX [IX_Staging_ProductInventory_History] ON [History].[Staging_ProductInventory_History] ( [ValidTo] ASC, [ValidFrom] ASC )

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016