Archiving old data in SQL Server

Mindy Curnutt, practice manager at SQLRx.com, had an idea the other day regarding the subject of moving off old records in SQL Server that I thought I would share with you. The subject is never mentioned, really, much in reference books, books online, or anywhere else for that matter, and many of us revert to a variety of "homespun" ways to acocomplish this task. After all, if the data is not being used on a daily basis - and especially if its already in a data mart or data warehouse, wouldn't maintaining indexes, running various administrative commands, and searching across old data be a waste of time and space?

The idea is a clever one, and very simple - move data off into a table by way of the FOR XML clause. So, trying it the first time, I ran into a little problem:

No problem. Using the AdventureWorks database:

-- you have to create a table I called mine OldPurchaseOrderHeader
-- from the Purchasing.PurchaseOrder table
-- has datetime, int, and XML attribute columns...

INSERT INTO OldPurchaseOrderHeader

       ( logdate
        ,maxpurchaseorder
        ,data
       )

SELECT GETDATE()
      ,30
      ,(
         SELECT *
         FROM Purchasing.PurchaseOrderDetail d
         INNER JOIN Purchasing.PurchaseOrderHeader o
              ON o.PurchaseOrderId=d.PurchaseOrderId
         FOR XML AUTO, ELEMENTS
         );

GO      

Which gave me the following: 

SELECT logdate
       ,maxpurchaseorder
       ,data
FROM OldPurchaseOrderHeader

Not a bad way to put several records into one row of data type XML. Now your question; how do you query the thing?  I'll put that one in a blog for next time.

Lee 

------------------------------- 

Great idea!!!