로그와 보관 정책

-- 같은 로그 표를 둘 만들고 한쪽에만 인덱스를 겁니다.
CREATE TABLE Board.T_LOG1 (
    num       int IDENTITY PRIMARY KEY,
    cont_type int NOT NULL,
    cont_num  int NOT NULL,
    val       int NOT NULL,
    reg_num   int NULL,
    reg_addr  varchar(50) NOT NULL,
    reg_date  datetime2(0) NOT NULL
);

-- T_LOG2 는 같은 모양에 인덱스 셋을 더 답니다.
CREATE INDEX IX_LOG2_cont ON Board.T_LOG2 (cont_type, cont_num);
CREATE INDEX IX_LOG2_reg  ON Board.T_LOG2 (reg_date);
CREATE INDEX IX_LOG2_user ON Board.T_LOG2 (reg_num);

CREATE TABLE Logging.WEEK1 (
    num       INT IDENTITY NOT NULL
        CONSTRAINT PK_WEEK1 PRIMARY KEY (num DESC),
    cont_type INT NOT NULL,             -- 1:게시판, 2:스도쿠 …
    [type]    INT NOT NULL              -- 1:조회, 2:추천, 4:신고 …
        CONSTRAINT CK_WEEK1_type CHECK ([type] IN (1, 2, 4, 8, 16)),
    cont_num  INT NOT NULL,
    val       INT NOT NULL,
    reg_num   INT NULL,
    reg_addr  VARCHAR(50) NOT NULL
        CONSTRAINT DF_WEEK1_reg_addr DEFAULT (''),
    reg_date  DATETIME NOT NULL
        CONSTRAINT DF_WEEK1_reg_date DEFAULT (GETDATE())
);

-- 인덱스는 실제로 하는 조회 하나만 받칩니다.
CREATE INDEX IX_WEEK1 ON Logging.WEEK1 (cont_type, [type], cont_num, usable)
    INCLUDE (val);

CREATE PROCEDURE Logging.P_WEEK_SAVE
    @cont_type INT, @type INT, @cont_num INT, @val INT,
    @reg_num INT = NULL, @reg_addr VARCHAR(50) = '',
    @rst INT = 0 OUTPUT
AS
    SET NOCOUNT ON;

    DECLARE @week INT = DATEPART(WEEKDAY, GETDATE()),   -- 1~7
            @sql NVARCHAR(MAX), @sqlp NVARCHAR(MAX);

    -- 표 이름만 이어 붙이고, 값은 매개 변수로 넘깁니다(4.9).
    SET @sql = N'INSERT INTO Logging.WEEK' + CONVERT(NCHAR(1), @week) + N' (
        [type], cont_num, val, reg_num, reg_addr
    ) VALUES (@type, @cont_num, @val, @reg_num, @reg_addr);
    SET @rst = @@ROWCOUNT;';

    SET @sqlp = N'@type INT, @cont_num INT, @val INT,
        @reg_num INT, @reg_addr VARCHAR(50), @rst INT OUTPUT';

    EXEC sp_executesql @sql, @sqlp,
        @type = @type, @cont_num = @cont_num, @val = @val,
        @reg_num = @reg_num, @reg_addr = @reg_addr, @rst = @rst OUTPUT;

-- 매달 초에 다음 달 표를 미리 만듭니다.
DECLARE @name sysname = N'ACCESS_' + FORMAT(DATEADD(month, 1, GETDATE()), 'yyyyMM');

IF NOT EXISTS (SELECT 1 FROM sys.tables
                WHERE schema_id = SCHEMA_ID('Logging') AND name = @name)
BEGIN
    DECLARE @sql nvarchar(MAX) = N'CREATE TABLE Logging.' + QUOTENAME(@name) + N' (…)';
    EXEC sp_executesql @sql;
END

-- 보관 기간이 지난 표를 지웁니다. 이름으로 찾습니다.
DECLARE @cut nvarchar(6) = FORMAT(DATEADD(month, -13, GETDATE()), 'yyyyMM');

SELECT N'DROP TABLE Logging.' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE schema_id = SCHEMA_ID('Logging')
  AND name LIKE 'ACCESS_%'
  AND RIGHT(name, 6) < @cut;

-- 원본 로그: 하루 100만 건
-- 요약: 하루 몇백 건
CREATE TABLE Statistics.DAILY_VIEW (
    stat_date date NOT NULL,
    cont_type int  NOT NULL,
    cont_num  int  NOT NULL,
    view_cnt  int  NOT NULL,
    CONSTRAINT PK_DAILY_VIEW PRIMARY KEY CLUSTERED (stat_date, cont_type, cont_num)
);
GO

-- 새벽에 어제치를 요약합니다.
DECLARE @day date = CAST(DATEADD(day, -1, GETDATE()) AS date);

-- 다시 돌려도 안전하도록 그날 것을 먼저 지웁니다(6.3).
DELETE FROM Statistics.DAILY_VIEW WHERE stat_date = @day;

INSERT INTO Statistics.DAILY_VIEW (stat_date, cont_type, cont_num, view_cnt)
SELECT @day, cont_type, cont_num, COUNT(*)
FROM Logging.WEEK1
WHERE reg_date >= @day AND reg_date < DATEADD(day, 1, @day)
  AND [type] = 1                       -- 조회만
GROUP BY cont_type, cont_num;