I do highly recommend the link above then. I actually gave a presentation on this to my SQL User Group a few months ago. Here is a possible table structure for you including a partition aligned non-clustered index, as well as the procedure I used to rotate the partitions around. It should help you get started.
Link to article: http://kejser.org/table-pattern-rotating-log-ring-buffer/
CREATE PARTITION FUNCTION [PF_100](int) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)
GO
CREATE PARTITION SCHEME [PS_100_dbo] AS PARTITION [PF_100] ALL TO (DBO)
GO
CREATE TABLE dbo.MyLog
(
UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_UniqueID DEFAULT (NEWSEQUENTIALID())
, LookupID INT NOT NULL
, TypeID INT NOT NULL
, Created DATETIME2(7) NOT NULL
, PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
, CONSTRAINT PK_MyLog_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
) ON PS_100_dbo (PartitionOffset)
CREATE NONCLUSTERED INDEX IDX_MyLog_LookupID_TypeID_Created
ON dbo.MyLog (LookupID, TypeID, Created, PartitionOffset)
ON PS_100_dbo (PartitionOffset)
CREATE TABLE dbo.MyLog_SwitchOffset
(
UniqueID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_MyLog_SwitchOffset_UniqueID DEFAULT (NEWSEQUENTIALID())
, LookupID INT NOT NULL
, TypeID INT NOT NULL
, Created DATETIME2(7) NOT NULL
, PartitionOffset AS (CONVERT(INT, DATEDIFF(DAY,CONVERT(DATETIME, 0),Created)%(100))) PERSISTED NOT NULL
, CONSTRAINT PK_MyLog_SwitchOffset_UniqueID PRIMARY KEY CLUSTERED (UniqueID, PartitionOffset)
) ON PS_100_dbo (PartitionOffset)
GO
CREATE OR ALTER PROCEDURE dbo.usp_SwichPartitions
@SourceTableName NVARCHAR(128)
, @TargetTableName NVARCHAR(100)
, @PartitionsToKeep INT
, @IsDebug BIT = 0
AS
BEGIN
/** Procedure dbo.usp_SwichPartitions
Based on http://kejser.org/table-pattern-rotating-log-ring-buffer/
This procedure:
* does a few sanity checks.
* creates a small table with all possible dates, assuming that today is the most recent partition and that the offset modulo is as described.
* Determines how many should be kept (most recent X partitions)
* for any partitions that we want to discard, AND has data, switch to the @TargetTableName
* truncate @TargetTableName
INPUTS:
@SourceTableName NVARCHAR(100) The base table (include schema!)
@TargetTableName NVARCHAR(100) The table to switch to (include schema!)
@PartitionsToKeep INT = 5 The number of partitions to keep (keep most recent X days)
@IsDebug BIT = 0 Debug mode?
@SourceTableName and @TargetTableName MUST exist (they must also have the exact same definition [indexes optional]
@PartitionsToKeep must be greater than 1.
@IsDebug (default 0), if set to 1, shows you a "what if" scenario, scripting out all of the required commands.
NO CHANGES WILL BE MADE
*/
/** For Testing values...
DECLARE @SourceTableName nvarchar(100) = 'dbo.LogTable'
DECLARE @TargetTableName nvarchar(100) = 'dbo.LogTable_SwitchTarget'
DECLARE @PartitionsToKeep int = 5
DECLARE @IsDebug bit = 0
*/
SET NOCOUNT ON
/** INPUT VALIDATION **/
DECLARE @ThrowMessage NVARCHAR(1000);
IF OBJECT_ID(@SourceTableName) IS NULL
BEGIN
SET @ThrowMessage = 'Specified @SourceTableName: ' + COALESCE(@SourceTableName, 'NULL') + ' does not exist (include schema).';
;THROW 50000, @ThrowMessage, 1;
END
IF OBJECT_ID(@TargetTableName) IS NULL
BEGIN
SET @ThrowMessage = 'Specified @TargetTableName: ' + COALESCE(@TargetTableName, 'NULL') + ' does not exist (include schema).';
;THROW 50000, @ThrowMessage, 1;
END
IF COALESCE(@PartitionsToKeep, 0) <= 1
BEGIN
SET @ThrowMessage = 'You must keep at least one (1) partition, you specified @PartitionsToKeep: ' + COALESCE(CAST(@PartitionsToKeep AS VARCHAR(10)), 'NULL') + '.';
;THROW 50000, @ThrowMessage, 1;
END
/** VARIABLE DECLARATION **/
DECLARE @SQLCommand NVARCHAR(4000);
DECLARE @AllPartitionCount INT;
DECLARE @PartitionToSwitch INT;
DECLARE @PartitionDate DATE;
DECLARE @PartitionRowCount BIGINT;
DECLARE @TimeToUse_UTC DATETIME2(7);
DECLARE @RotateLogID INT;
SET @TimeToUse_UTC = SYSUTCDATETIME();
SET @AllPartitionCount = ( SELECT COUNT(*)
FROM sys.partitions AS P
WHERE P.object_id = OBJECT_ID(@SourceTableName)
AND P.index_id IN (0, 1)
);
DECLARE @PartitionsTable TABLE
(
PartitionTableID INT NOT NULL PRIMARY KEY
, PartitionDate DATE NOT NULL UNIQUE
, PartitionOffset INT NULL
, PartitionNumber INT NULL
, IsKeepPartition BIT NOT NULL DEFAULT (0)
, PartitionRowCount BIGINT NOT NULL DEFAULT (0)
);
IF @AllPartitionCount IS NULL
OR @PartitionsToKeep > @AllPartitionCount
BEGIN
;THROW 50000, 'Cannot keep more partitions than the current buffer size', 1;
END
/** Build up a table of dates (starting from today) and going back for as long as many days as there are partitions.
- PartitionOffset should match the computed column in the log table (# of Days since epoch % Total Number of partitions) [-1 because of 0]
**/
;WITH CTE_Number AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 1
FROM CTE_Number
WHERE N <= @AllPartitionCount
)
INSERT INTO @PartitionsTable
(PartitionTableID, PartitionDate, IsKeepPartition)
SELECT TOP (@AllPartitionCount)
N
, DATEADD(DAY, (N *-1), @TimeToUse_UTC)
, CASE WHEN N < @PartitionsToKeep THEN 1 ELSE 0 END
FROM CTE_Number
ORDER BY N
OPTION (MAXRECURSION 1000);
UPDATE @PartitionsTable
SET PartitionOffset = CONVERT(INT, (DATEDIFF(DAY, CAST(0 AS DATETIME), PartitionDate) % @AllPartitionCount))
UPDATE @PartitionsTable
SET PartitionNumber = PartitionOffset + 1
--Get Row Count...
UPDATE @PartitionsTable
SET PartitionRowCount = P.row_count
FROM @PartitionsTable AS T
INNER JOIN sys.dm_db_partition_stats AS P ON P.object_id = OBJECT_ID(@SourceTableName) AND P.index_id IN (0, 1) AND (P.partition_number) = T.PartitionNumber;
IF @IsDebug = 1
BEGIN
SELECT PartitionTableID
, PartitionDate
, PartitionOffset
, PartitionNumber
, IsKeepPartition
, PartitionRowCount
FROM @PartitionsTable
ORDER BY PartitionDate DESC;
END
IF @IsDebug = 1
BEGIN
PRINT '/** Variables ';
PRINT ' @TimeToUse_UTC = ' + CONVERT(VARCHAR(20), @TimeToUse_UTC, 121);
PRINT ' @PartitionsToKeep = ' + CAST(@PartitionsToKeep AS VARCHAR(10));
PRINT ' @SourceTableName = ' + COALESCE(@SourceTableName, 'NULL');
PRINT ' @TargetTableName = ' + COALESCE(@TargetTableName, 'NULL');
PRINT ' @AllPartitionCount = ' + CAST(@AllPartitionCount AS VARCHAR(10));
PRINT ' **/';
PRINT ' ';
END
/** Loop through every partitions that needs to be deleted that also
has a PartitionRowCount. Order by PartitionDate, removing the oldest first.
*/
DECLARE curPartLoop CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT PartitionNumber
, PartitionDate
, PartitionRowCount
FROM @PartitionsTable
WHERE IsKeepPartition = 0
AND PartitionRowCount > 0
ORDER BY PartitionDate;
OPEN curPartLoop;
FETCH NEXT FROM curPartLoop
INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;
IF @IsDebug = 1
AND NOT(@@FETCH_STATUS = 0)
BEGIN
PRINT '/** NO PARTITIONS DETECTED AS NEEDING TO BE REMOVED **/';
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCommand = CONCAT('ALTER TABLE ', @SourceTableName ,' SWITCH PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)), ' TO ', @TargetTableName, ' PARTITION ', CAST(@PartitionToSwitch AS NVARCHAR(10)));
IF @IsDebug = 1
BEGIN
PRINT '/** @PartitionToSwitch = ' + CAST(@PartitionToSwitch AS VARCHAR(10));
PRINT ' @PartitionDate = ' + CONVERT(VARCHAR(20), @PartitionDate, 121);
PRINT ' @PartitionRowCount = ' + CAST(@PartitionRowCount AS VARCHAR(10));
PRINT ' */';
PRINT @SQLCommand;
PRINT ' ';
END
IF @IsDebug = 0
BEGIN
EXEC sp_executesql @SQLCommand;
END
FETCH NEXT FROM curPartLoop
INTO @PartitionToSwitch, @PartitionDate, @PartitionRowCount;
END --WHILE @@FETCH_STATUS = 0
--Cleanup.
CLOSE curPartLoop;
DEALLOCATE curPartLoop;
--Truncate the switch table.
SET @SQLCommand = CONCAT('TRUNCATE TABLE ', @TargetTableName);
IF @IsDebug = 1
BEGIN
PRINT '/* TRUNCATE SWITCH TABLE */';
PRINT @SQLCommand;
END
IF @IsDebug = 0
BEGIN
EXEC sp_executesql @SQLCommand;
END
END
