SQL Server at Scale - Automate Archive Table Creation - Part 3

By: Aaron Bertrand   |   Updated: 2022-11-21   |   Comments (2)   |   Related: 1 | 2 | 3 | More > Database Administration

Problem

Earlier in this series (part 1 | part 2), I wrote at a high level about how to solve issues with ever-growing log tables without large delete operations or data movement to a secondary archive table. In this tip, I'll share a few code snippets you can use to automate the generation of objects to help make these solutions hands-free.

Solution

The problem with abstracting partitions or physical table boundaries behind a view is keeping the view accurate. Starting with the table solution, let's say for the year 2023 the original Orders table has been removed since it has fallen out of retention. You will have a layout (below) where the view sits over 12 active tables for the year 2023:

The static query inside the view looks like this:

CREATE VIEW dbo.Orders_View
AS
  SELECT <columns> FROM dbo.Ord_2023_01 
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_02 
  UNION ALL
  … all the tables in the middle …
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_11 
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_12;

When January 2024 comes around, you need to add a new table to be ready to accept order data for that month:

You create the new table like you would create any other table:

CREATE TABLE dbo.Ord_2024_01(…columns…);

Then you change the view so that it incorporates the upcoming month in addition to the previous 12 months. Manually that would look like this:

CREATE VIEW dbo.Orders_View
AS
  SELECT <columns> FROM dbo.Ord_2023_01 
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_02 
  UNION ALL
  … all the tables in the middle …
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_11 
  UNION ALL
  SELECT <columns> FROM dbo.Ord_2023_12
 UNION ALL
 SELECT <columns> FROM dbo.Ord_2024_01;

But you can do this dynamically, and schedule it to run monthly, so you don't have to think about it going forward:

DECLARE @view   nvarchar(max), 
        @months int  = 13,
        @cols   nvarchar(4000) = N'col1, col2, ...',
        @cur    date = DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));

;WITH src(m) AS
(
    SELECT @months
    UNION ALL 
    SELECT m-1 FROM src WHERE m > 0
)
SELECT @view = N'CREATE OR ALTER VIEW dbo.Orders_View AS 
    ' + STRING_AGG(
    CONCAT(N'SELECT ', @cols, N' FROM dbo.Ord_',
    TRANSLATE(CONVERT(char(7), DATEADD(MONTH, -m, @cur), 120),'-','_')),
    N'
    UNION ALL 
    ')
  FROM src;

SELECT @view;
-- EXEC sys.sp_executesql @view;

After January 2024 ends, you'll repeat the same process to add February 2024. At that point, you can drop the oldest table (January 2023).

This can be part of the same monthly routine, by adding the code below to the same script:

-- remove the oldest table now that it is no longer in the view:
DECLARE @sql    nvarchar(max),
        @oldest date = DATEADD(MONTH, -(@months+1), @cur);

SET @sql = N'DROP TABLE IF EXISTS dbo.Ord_'
  -- or drop and re-create
  -- or just TRUNCATE
  + TRANSLATE(CONVERT(char(7), @oldest, 120),'-','_');

SELECT @sql;
-- EXEC sys.sp_executesql @sql;

It's possible to create all the future tables in advance and even include them in the view, but that's an implementation detail that doesn't alter the complexity here much. The important part is dynamically removing the trailing months from the view (and from the disk, of course).

Using partitioning? No problem. You can use the exact same kind of logic but, instead of generating commands for dropping tables and altering view definitions, you generate the same type of code for switching out or truncating the oldest partition and splitting the newest partition. Nat Sundar gives a great walkthrough of this sliding window scenario in Implementation of Sliding Window Partitioning in SQL Server to Purge Data.

Remember to take (or archive) a backup before purging data that is older than your "hot/online" retention period, but newer than your "cold/offline" retention period. And move that backup to storage that will outlast your cold retention period (as opposed to locations provisioned for regular backups that probably get cleaned out regularly).

Next Steps

See these tips and other resources:

• SQL Server at Scale - Using an Archive Table, Part 1
• SQL Server at Scale - Using an Archive Table - Part 2
• Delete Data from Large SQL Server Tables with Minimal Logging
• Fastest way to Delete Large Number of Records in SQL Server
• Handling Large SQL Server Tables with Data Partitioning
• Implementation of Sliding Window Partitioning in SQL Server to Purge Data
• How to Partition a SQL Server Table with a Columnstore Index

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips