CRM-ReCreate-AuditBase-Partitioning.sql

-- Change DB
use [org_MSCRM]

-- 1. PartitionCreate

-- Drop any existing partition function and scheme
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'AuditPScheme') 
BEGIN
	print 'Dropping Partition Scheme AuditPScheme'
	DROP PARTITION SCHEME [AuditPScheme]
END

IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN') 
BEGIN
	print 'Dropping Partition Function AuditPFN'
	DROP PARTITION FUNCTION [AuditPFN]		
END

-- Create partition fucntion
DECLARE @currentDate DATETIME;
SET @currentDate  = GETDATE();		

DECLARE @firstPartition DATETIME;
DECLARE @secondPartition DATETIME;

SET @firstPartition = DATEADD(ms, -3, (DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));
SET @secondPartition = DATEADD(ms, -3, (DATEADD(qq, 2, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));

print 'Current Date = ' + CONVERT(varchar(50), @currentDate)
print 'First Partition Date = ' + CONVERT(varchar(50), @firstPartition)
print 'Second Partition Date = ' + CONVERT(varchar(50), @secondPartition)


CREATE PARTITION FUNCTION AuditPFN(datetime) 
AS RANGE LEFT FOR VALUES (@firstPartition,  @secondPartition)

-- Create partition scheme
CREATE PARTITION SCHEME AuditPScheme AS PARTITION AuditPFN ALL TO ([PRIMARY])


-- 2. AuditTableCreate

-- Drop Audit table
IF EXISTS (SELECT * FROM sys.tables WHERE Name = 'AuditBase')
BEGIN
	print 'Dropping Audit table AuditBase'
	DROP Table AuditBase
END


-- Create Audit table on AuditScheme
CREATE TABLE [dbo].[AuditBase](
	[AttributeMask] [nvarchar](max) NULL,
	[TransactionId] [uniqueidentifier] NOT NULL,
	[Action] [int] NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[ObjectIdName] [nvarchar](1) NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[ChangeData] [nvarchar](max) NULL,
	[CreatedOn] [datetime] NOT NULL,
	[Operation] [int] NOT NULL,
	[AuditId] [uniqueidentifier] DEFAULT (newsequentialid()) NOT NULL,
	[CallingUserId] [uniqueidentifier] NULL,
	[ObjectTypeCode] [int] NULL,
) ON AuditPScheme (CreatedOn)


-- 3. IndexCreateOrRecreatePartition

IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit_Primary' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
	DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit_Primary]
END

-- Create an index on the primary so that select top N query works faster
CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase] 
(
	[CreatedOn] DESC,
	[AuditId] DESC
) ON [PRIMARY]

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name='AuditPScheme')
BEGIN

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'cndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[cndx_PrimaryKey_Audit]
	END

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit]
	END

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_ObjectId' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[ndx_ObjectId]
	END

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_UserId' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[ndx_UserId]
	END

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_ObjectTypeCode' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[fndx_ObjectTypeCode]
	END

	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
	BEGIN
		DROP INDEX [dbo].[AuditBase].[fndx_Action]
	END

	CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase] 
	(
		[CreatedOn] DESC,
		[AuditId]  DESC
	) ON AuditPScheme (CreatedOn)

	CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase] 
	(
		[AuditId] ASC
	) ON AuditPScheme (CreatedOn)

	CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase] 
	(
		[ObjectId] ASC
	) ON AuditPScheme (CreatedOn)

	CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase] 
	(
		[UserId] ASC
	) ON AuditPScheme (CreatedOn)

	CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase] 
	(
		[ObjectTypeCode] ASC,
		[Action] ASC
	) 
	WHERE ([ObjectTypeCode] IS NOT NULL)
	ON AuditPScheme (CreatedOn)
END

I see that you are dropping the AuditBase table with a table backup ? is the Audit history not needed ?

Hi. Yes. Backup is needed.
select * into AuditBase_back from AuditBase
Full post with detailed explanation can be found here: https://tunnik.name/crm-audit-log-and-sql-partitioning/