USE master
go

IF DATABASEPROPERTYEX('ChargeTesting', 'COLLATION') IS NOT NULL
	DROP DATABASE ChargeTesting
go

CREATE DATABASE ChargeTesting
ON PRIMARY 
	(NAME = N'ChargeTestingData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ChargeTestingData.mdf', 
	SIZE = 1500,
	FILEGROWTH = 10%,
	MAXSIZE = 1500)
LOG ON 
	(NAME = N'ChargeTestingLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ChargeTestingLog.ldf', 
	SIZE = 150, FILEGROWTH = 10%)
go
ALTER DATABASE ChargeTesting
	SET RECOVERY SIMPLE
go

-- NOTE - Simple recovery is fine here. Delete performance (i.e. TIME) is NOT impacted by
-- Recovery Model setting. Only the amount of time they hold the log space is affected. With
-- this setting the log activity will need to accrue (for each transaction) but once completed
-- the log space may be removed.

USE ChargeTesting
go

IF OBJECTPROPERTY(object_id('ChargeHeap'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeHeap
IF OBJECTPROPERTY(object_id('ChargeCLPKForDelete'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLPKForDelete
IF OBJECTPROPERTY(object_id('ChargeCLDateForDelete'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLDateForDelete
IF OBJECTPROPERTY(object_id('ChargeCLDateForDeleteWithCompPK'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLDateForDeleteWithCompPK
IF OBJECTPROPERTY(object_id('ChargeCLReallyBadForDelete'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLReallyBadForDelete
IF OBJECTPROPERTY(object_id('ChargeHeapWNCIndexes'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeHeapWNCIndexes
IF OBJECTPROPERTY(object_id('ChargeCLPKForDeleteWNCIndexes'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLPKForDeleteWNCIndexes
IF OBJECTPROPERTY(object_id('ChargeCLDateForDeleteWNCIndexes'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLDateForDeleteWNCIndexes
IF OBJECTPROPERTY(object_id('ChargeCLDateForDeleteWithCompPKWNCIndexes'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLDateForDeleteWithCompPKWNCIndexes
IF OBJECTPROPERTY(object_id('ChargeCLReallyBadForDeleteWNCIndexes'), 'IsUserTable') = 1
	DROP TABLE dbo.ChargeCLReallyBadForDeleteWNCIndexes
go

--------------------------------------------
-- ChargeHeap
--------------------------------------------
SELECT * 
INTO dbo.ChargeHeap
FROM Credit.dbo.Charge
go

--------------------------------------------
-- ChargeCLPKForDelete
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLPKForDelete
FROM Credit.dbo.Charge
go

ALTER TABLE dbo.ChargeTesting.dbo.ChargeCLPKForDelete
ADD CONSTRAINT ChargeCLPKForDeletePK
	PRIMARY KEY CLUSTERED (Charge_NO)
go

--------------------------------------------
-- ChargeCLDateForDelete
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLDateForDelete
FROM Credit.dbo.Charge
go

CREATE CLUSTERED INDEX dbo.ChargeCLDateForDelete
ON ChargeTesting.dbo.ChargeCLDateForDelete (Charge_DT)
go

--------------------------------------------
-- ChargeCLDateForDeleteWithCompPK
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLDateForDeleteWithCompPK
FROM Credit.dbo.Charge
go

ALTER TABLE dbo.ChargeTesting.dbo.ChargeCLDateForDeleteWithCompPK
ADD CONSTRAINT ChargeCLDateForDeleteWithCompPK_PK
	PRIMARY KEY CLUSTERED (Charge_DT, Charge_NO)
go

--------------------------------------------
-- ChargeCLReallyBadForDelete
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLReallyBadForDelete
FROM Credit.dbo.Charge
go

CREATE CLUSTERED INDEX dbo.ChargeCLReallyBadForDelete
ON ChargeTesting.dbo.ChargeCLReallyBadForDelete (Charge_amt)
go

--------------------------------------------
-- ChargeHeapWNCIndexes
--------------------------------------------
SELECT * 
INTO dbo.ChargeHeapWNCIndexes
FROM Credit.dbo.Charge
go

CREATE NONCLUSTERED INDEX ChargeHeapWNCIndexesNC2
ON dbo.ChargeHeapWNCIndexes (Statement_NO)
go

CREATE NONCLUSTERED INDEX ChargeHeapWNCIndexesNC3
ON dbo.ChargeHeapWNCIndexes (Member_no)
go

--------------------------------------------
-- ChargeCLPKForDeleteWNCIndexes
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLPKForDeleteWNCIndexes
FROM Credit.dbo.Charge
go

ALTER TABLE ChargeTesting.dbo.ChargeCLPKForDeleteWNCIndexes
ADD CONSTRAINT ChargeCLPKForDeleteWNCIndexesPK
	PRIMARY KEY CLUSTERED (Charge_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLPKForDeleteWNCIndexesNC2
ON dbo.ChargeCLPKForDeleteWNCIndexes (Statement_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLPKForDeleteWNCIndexesNC3
ON dbo.ChargeCLPKForDeleteWNCIndexes (Member_no)
go

--------------------------------------------
-- ChargeCLDateForDeleteWNCIndexes
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLDateForDeleteWNCIndexes
FROM Credit.dbo.Charge
go

CREATE CLUSTERED INDEX ChargeCLDateForDeleteWNCIndexes
ON ChargeTesting.dbo.ChargeCLDateForDeleteWNCIndexes (Charge_DT)
go

CREATE NONCLUSTERED INDEX ChargeCLDateForDeleteWNCIndexesNC2
ON dbo.ChargeCLDateForDeleteWNCIndexes(Statement_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLDateForDeleteWNCIndexesNC3
ON dbo.ChargeCLDateForDeleteWNCIndexes(Member_no)
go

--------------------------------------------
-- ChargeCLDateForDeleteWithCompPKWNCIndexes
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLDateForDeleteWithCompPKWNCIndexes
FROM Credit.dbo.Charge
go

ALTER TABLE ChargeTesting.dbo.ChargeCLDateForDeleteWithCompPKWNCIndexes
ADD CONSTRAINT ChargeCLDateForDeleteWithCompPKWNCIndexes_PK
	PRIMARY KEY CLUSTERED (Charge_DT, Charge_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLDateForDeleteWithCompPKWNCIndexesNC2
ON dbo.ChargeCLDateForDeleteWithCompPKWNCIndexes (Statement_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLDateForDeleteWithCompPKWNCIndexesNC3
ON dbo.ChargeCLDateForDeleteWithCompPKWNCIndexes (Member_no)
go

--------------------------------------------
-- ChargeCLReallyBadForDeleteWNCIndexes
--------------------------------------------
SELECT * 
INTO dbo.ChargeCLReallyBadForDeleteWNCIndexes
FROM Credit.dbo.Charge
go

CREATE CLUSTERED INDEX ChargeCLReallyBadForDeleteWNCIndexesCL
ON ChargeTesting.dbo.ChargeCLReallyBadForDeleteWNCIndexes (Charge_amt)
go

CREATE NONCLUSTERED INDEX ChargeCLReallyBadForDeleteWNCIndexesNC2
ON dbo.ChargeCLReallyBadForDeleteWNCIndexes (Statement_NO)
go

CREATE NONCLUSTERED INDEX ChargeCLReallyBadForDeleteWNCIndexesNC3
ON dbo.ChargeCLReallyBadForDeleteWNCIndexes (Member_no)
go

--------------------------------------------
-- Verify the data
--------------------------------------------
-- exec sp_helpindex ChargeHeap
-- exec sp_helpindex ChargeCLPKForDelete
-- exec sp_helpindex ChargeCLDateForDelete
-- exec sp_helpindex ChargeCLDateForDeleteWithCompPK
-- exec sp_helpindex ChargeCLReallyBadForDelete
-- exec sp_helpindex ChargeHeapWNCIndexes
-- exec sp_helpindex ChargeCLPKForDeleteWNCIndexes
-- exec sp_helpindex ChargeCLDateForDeleteWNCIndexes
-- exec sp_helpindex ChargeCLDateForDeleteWithCompPKWNCIndexes
-- exec sp_helpindex ChargeCLReallyBadForDeleteWNCIndexes

-- SELECT min(charge_dt), max(charge_dt) FROM ChargeCLDateForDelete
-- SELECT count(*) FROM ChargeCLDateForDelete WHERE charge_dt < '19990622'
-- 80560 rows

-- Backup the database so that you can restore for each test run...

CREATE PROCEDURE dbo.DeleteTestRun
	@DateToDeleteTo	datetime = '19990701'
AS
DECLARE @TestGroupName	varchar(20),
		@TableName		sysname,
		@Start			datetime,
		@Time			int,
		@ExecString		nvarchar(1000),
		@TestRunID		tinyint,
		@RowCount		int

SELECT @TestGroupName = 'TestRun' + CONVERT(varchar(2), LastTestRun + 1)
FROM (SELECT MAX(convert(int, substring(TestGroupName, 8, LEN(TestGroupName)))) AS LastTestRun 
		FROM (SELECT DISTINCT TestGroupName 
				FROM ChargeTestingResults.dbo.DeleteTestResults) 
			  AS GroupList) 
	  AS LastValue

DECLARE TablesToProcess CURSOR FAST_FORWARD FOR 
	SELECT TableName
	FROM ChargeTestingResults.dbo.TablesToProcess

OPEN TablesToProcess 

FETCH NEXT FROM TablesToProcess 
	INTO @TableName

WHILE (@@fetch_status <> -1)
BEGIN
	DBCC FREEPROCCACHE
	DBCC DROPCLEANBUFFERS
	SELECT @ExecString = N'DELETE ChargeTesting.dbo.' + QUOTENAME(@TableName, ']') + N' WHERE charge_dt < ' + QUOTENAME(@DateToDeleteTo, '''')
	SELECT @Start = getdate()
	EXEC(@ExecString)
	SELECT @RowCount = @@Rowcount, @Time = datediff(ms, @Start, getdate()) -- [Total Time (ms) - HEAP]
	INSERT ChargeTestingResults.dbo.DeleteTestResults
			 (TestGroupName, TableName, TestRunStartTime, RowsDeleted, DeleteTime)
		VALUES (@TestGroupName, @TableName, @Start, @RowCount, @Time)
	SELECT @TestRunID = @@Identity

	SELECT @ExecString = N'DBCC SHOWCONTIG (' + QUOTENAME(@TableName, '''') + N') WITH ALL_INDEXES, TABLERESULTS'

	INSERT ChargeTestingResults.dbo.FragmentationResults 
				(ObjectName, ObjectId, IndexName, IndexId, [Level], Pages, [Rows],
				MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords,
				Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity,
				ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
		EXEC(@ExecString)

	UPDATE ChargeTestingResults.dbo.FragmentationResults 
		SET TestRunID = @TestRunID
		WHERE TestRunID IS NULL

	FETCH NEXT FROM TablesToProcess 
		INTO @TableName
END

DEALLOCATE TablesToProcess
go

-- Backup the database so that you can restore for each test run...
BACKUP DATABASE ChargeTesting
	TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ChargeTesting.bak'
	WITH INIT
go