T-SQL Recipes之Organizing and Archiving Data
The Problem
当我们处理存档数据或内存数据时,我们想要自定义命名表名,数据库,架构加上日期,时间,或者应用名时,用标准的TSQL来实现是比较困难的。
假设我们有一张日志表,增长速度异常快。但是你不需要超过一个星期的数据,应该怎么做呢?
表分区当然是最佳选择了,但只有企业版里面有这项功能,难道我们就没有其他选择了吗?当然是有的,先来准备点数据吧。
准备数据:
USE AdventureWorks2014; GO IF OBJECT_ID('dbo.Database_Log') IS NOT NULL BEGIN DROP TABLE dbo.Database_Log END CREATE TABLE dbo.Database_Log ( log_id INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Database_Log PRIMARY KEY CLUSTERED , Log_Time DATETIME , Log_Data NVARCHAR(1000) ); DECLARE @datetime DATETIME = CURRENT_TIMESTAMP; DECLARE @datediff TABLE ( previous_hour SMALLINT ); DECLARE @count SMALLINT = 0; WHILE @count <= 360 BEGIN INSERT INTO @datediff ( previous_hour ) SELECT @count; SELECT @count = @count + 1 END SELECT @count = 0; WHILE @count <= 1000 BEGIN INSERT INTO Database_Log ( Log_Time , Log_Data ) SELECT DATEADD(HOUR, -1 * previous_hour, CURRENT_TIMESTAMP) , CAST(DATEADD(HOUR, -1 * previous_hour, CURRENT_TIMESTAMP) AS NVARCHAR) FROM @datediff; SELECT @count = @count + 1; END
DECLARE @year_offset TINYINT = 5; WHILE @year_offset > 0 BEGIN INSERT INTO dbo.Database_Log ( Log_Time , Log_Data ) SELECT TOP 10000 DATEADD(YEAR, -1 * @year_offset, Log_Time) , CAST(DATEADD(YEAR, -1 * @year_offset, Log_Time) AS NVARCHAR) FROM Database_Log SELECT @year_offset = @year_offset - 1; END
The Solution
假设我们存档数据格式是,每年为数据库,每星期为一个表。如:dbo.database_log_2016为数据库,其中 dbo.database_log_2016_32为表。
我们动态SQL应该如何实现呢? 先整理一下思路
- 获取日志记录通过时间段
- 创建数据库或表(如果他们不存在)
- 进行插入操作
- 删除原始数据
代码:
DECLARE @sql_command NVARCHAR(MAX); DECLARE @parameter_list NVARCHAR(MAX) = '@start_of_week DATETIME, @end_of_week DATETIME'; DECLARE @min_datetime DATETIME; SELECT @min_datetime = MIN(Log_Time) FROM Database_Log; DECLARE @previous_min_time DATETIME = '1/1/1900'; DECLARE @start_of_week DATETIME = CAST(DATEADD(dd, -1 * (DATEPART(dw, @min_datetime) - 1), @min_datetime) AS DATE); DECLARE @end_of_week DATETIME = DATEADD(WEEK, 1, @start_of_week); DECLARE @current_year SMALLINT; DECLARE @current_week TINYINT; DECLARE @database_name NVARCHAR(128); DECLARE @table_name NVARCHAR(128); WHILE (@previous_min_time <> @min_datetime) BEGIN SELECT @current_year = DATEPART(YEAR, @start_of_week); SELECT @current_week = DATEPART(WEEK, @start_of_week); SELECT @database_name = 'Database_Log_' + CAST(@current_year AS NVARCHAR); -- Create the yearly database if it does not already exist SELECT @table_name = 'Database_Log_' + CAST(@current_year AS NVARCHAR) + '_' + CAST(@current_week AS NVARCHAR) IF NOT EXISTS(SELECT *FROM sys.databases WHERE databases.name = @database_name) BEGIN SELECT @sql_command = 'CREATE DATABASE [' + @database_name + ']'; EXEC sp_executesql @sql_command; END -- Create the weekly table if it does not already exist SELECT @sql_command = ' USE [' + @database_name + ']; IF NOT EXISTS (SELECT * FROM sys.tables WHERE tables.name = ''' + @table_name + ''') BEGIN CREATE TABLE [dbo].[' + @table_name + '] (Log_Id INT NOT NULL CONSTRAINT PK_Database_Log_' + CAST(@current_year AS NVARCHAR) + '_' + CAST(@current_week AS NVARCHAR) + ' PRIMARY KEY CLUSTERED, Log_Time DATETIME, Log_Data NVARCHAR(1000)); END' EXEC sp_executesql @sql_command; SELECT @sql_command = ' INSERT INTO [' + @database_name + '].[dbo].[' + @table_name + '] (Log_Id, Log_Time, Log_Data) SELECT Log_Id, Log_Time, Log_Data FROM AdventureWorks2014.dbo.Database_Log WHERE Log_Time >= @start_of_week AND Log_Time <= @end_of_week AND Log_Time < DATEADD(WEEK, -1, CURRENT_TIMESTAMP); DELETE FROM AdventureWorks2014.dbo.Database_Log WHERE Log_Time >= @start_of_week AND Log_Time <= @end_of_week AND Log_Time < DATEADD(WEEK, -1, CURRENT_TIMESTAMP);' EXEC sp_executesql @sql_command,@parameter_list,@start_of_week,@end_of_week SELECT @previous_min_time = @min_datetime; SELECT @min_datetime = MIN(Log_Time) FROM Database_Log; SELECT @start_of_week = CAST(DATEADD(dd, -1 * (DATEPART(dw, @min_datetime) - 1), @min_datetime) AS DATE); SELECT @end_of_week = DATEADD(WEEK, 1, @start_of_week); END