Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 87 of 212

75 C H A P T E R 5 | Broader data access WITH ( PAD_INDEX = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON) ON PRIMARY; -- Create constraints to match the partition to switch ALTER TABLE [History].[Staging_ProductInventory_History] WITH CHECK ADD CONSTRAINT [CHK_Staging_ProductInventory_History_Partition_1] CHECK ( [ValidTo] <= N'2016-01-31T23:59:59.999'); ALTER TABLE [History].[Staging_ProductInventory_History] CHECK CONSTRAINT [CHK_Staging_ProductInventory_History_Partition_1]; Then create a dynamic script for the recurring partition maintenance task, which consists of the following steps: 1. Truncate the staging table, and then switch a partition between the history table and the staging table, like this: ALTER TABLE [History].[ProductInventory_History] SWITCH PARTITION 1 TO [History].[Staging_ProductInventory_History]; 2. Merge partition 1 (now empty) with partition 2, like this: ALTER PARTITION FUNCTION [fn_Partition_ProductInventoryHistory_By_ValidTo]() MERGE RANGE (N'2016-02-29T23:59:59.999'); 3. Create a new empty partition, like this: ALTER PARTITION SCHEME [sch_Partition_ProductInventoryHistory_By_ValidTo] NEXT USED; ALTER PARTITION FUNCTION [fn_Partition_ProductInventoryHistory_By_ValidTo]() SPLIT RANGE (N'2016-04-30T23:59:59.999'); 4. Optionally archive the data in the staging table.
 Custom cleanup script A third option is to use a custom cleanup script to delete data from the history table. This approach requires you to first disable system versioning on the current table by using the SYSTEM_VERSIONING = OFF argument in the ALTER TABLE statement. To reduce the impact of running the cleanup script, you should run it at a time when workloads are light and modify the script shown in Example 5-11 as needed to keep the number of rows to delete in a single transaction below 10,000. Example 5-11: Creating a cleanup script for a history table CREATE PROCEDURE sp_CleanupHistoryData @temporalTableSchema sysname, @temporalTableName sysname, @cleanupOlderThanDate datetime2 AS DECLARE @disableVersioningScript nvarchar(max) = ''; DECLARE @deleteHistoryDataScript nvarchar(max) = '' DECLARE @enableVersioningScript nvarchar(max) = '' DECLARE @historyTableName sysname DECLARE @historyTableSchema sysname

Articles in this issue

Archives of this issue

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