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

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


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.


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:

Tables referenced by Orders_View before any changes

The static query inside the view looks like this:

CREATE VIEW dbo.Orders_View
  SELECT <columns> FROM dbo.Ord_2023_01 
  SELECT <columns> FROM dbo.Ord_2023_02 
  … all the tables in the middle …
  SELECT <columns> FROM dbo.Ord_2023_11 
  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:

Adding a new, 13th table to the view

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
  SELECT <columns> FROM dbo.Ord_2023_01 
  SELECT <columns> FROM dbo.Ord_2023_02 
  … all the tables in the middle …
  SELECT <columns> FROM dbo.Ord_2023_11 
  SELECT <columns> FROM dbo.Ord_2023_12
 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
    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),'-','_')),
  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).

Removing the oldest table from the view

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);

  -- 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 categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand 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

Article Last Updated: 2022-11-21

Comments For This Article

Monday, January 2, 2023 - 10:28:23 AM - carlos benito Back To Top (90792)
We accomplish the same approach but instead of recreating the views we do it using synonyms. This way allows us to change the names in one place no matter how many views were created.

Tuesday, November 29, 2022 - 4:10:05 AM - Francesco Mantovani Back To Top (90724)
Thank You Aaron. You blow my mind every time you write something.

get free sql tips
agree to terms