/*============================================================================
  File:     CreditPTOnDriveC.sql

  Summary:  This script sets up the same database I often setup on
			USB Keys on Drive C:. This is so that you can see all of
			the Partitioning Aspects without having 4 external drives.

  Date:     May 2005

  SQL Server Version: 9.00.1116.08 (April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Before this script will work you will need to:
-- (1) Create a C:\SQLskills subdirectory.
--		Copy the CreditBackup80.bak file (from the zip) to this directory.
--		You can find the zip on the Past Events page from SQLskills.com.
-- (2) Create the multiple filegroup and partitioned table scenario
--		using the script: "CreditPTOnDriveC.sql" 
-- Once CreditPT is created, you can use this script to create a snapshot!
-- NOTE: This script has many comments which are not commented out...
-- In other words, don't try to just "execute" this script, you should
-- work your way through it slowly!

USE master
GO

IF DATABASEPROPERTYEX('CreditPT', 'Collation') IS NOT NULL
  DROP DATABASE [CreditPT]
GO


----------------------------------------------------------------------------------------------
-------------------------------- Database Setup (~1 min) ------------------------------------
----------------------------------------------------------------------------------------------

USE master
GO

/* ******* pre-step *******  */
RESTORE DATABASE CreditPT
          FROM DISK = 'C:\SQLskills\CreditBackup80.BAK'
WITH MOVE N'CreditData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditPTData.mdf'
        , MOVE N'CreditLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditPTLog.ldf'
        , REPLACE
go
/* ******* pre-step ******* */

USE CreditPT
go

sp_dbcmptlevel CreditPT, 90
GO

ALTER DATABASE [CreditPT] 
	SET RECOVERY SIMPLE
GO

ALTER DATABASE [CreditPT] 
	ADD FILEGROUP PartitionedTablesFG1
GO

ALTER DATABASE [CreditPT] 
	ADD FILE
		(NAME = N'CreditPTFG1File1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPTFG1File1.ndf', 
		SIZE = 100, FILEGROWTH = 10, MAXSIZE = 500) 
		TO FILEGROUP [PartitionedTablesFG1]
GO

ALTER DATABASE CreditPT 
	ADD FILEGROUP PartitionedTablesFG2
GO

ALTER DATABASE CreditPT 
	ADD FILE
		(NAME = N'CreditPTFG2File1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPTFG2File1.ndf', 
		SIZE = 100, FILEGROWTH = 10, MAXSIZE = 500) 
		TO FILEGROUP [PartitionedTablesFG2]
GO

ALTER DATABASE CreditPT 
	ADD FILEGROUP PartitionedTablesFG3
GO

ALTER DATABASE CreditPT 
	ADD FILE
		(NAME = N'CreditPTFG3File1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPTFG3File1.ndf', 
		SIZE = 100, FILEGROWTH = 10, MAXSIZE = 500) 
		TO FILEGROUP [PartitionedTablesFG3]
GO

ALTER DATABASE CreditPT 
	ADD FILEGROUP PartitionedTablesFG4
GO

ALTER DATABASE CreditPT 
	ADD FILE
		(NAME = N'CreditPTFG4File1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPTFG4File1.ndf', 
		SIZE = 100, FILEGROWTH = 10, MAXSIZE = 500) 
		TO FILEGROUP [PartitionedTablesFG4]
GO

-- Move a "staging" copy into it's own filegroup...
ALTER DATABASE CreditPT 
	ADD FILEGROUP ChargesStaging
GO

ALTER DATABASE CreditPT 
	ADD FILE
		(NAME = N'CreditPTChargesStaging', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPTChargesStaging.NDF' , 
		SIZE = 100, FILEGROWTH = 10, MAXSIZE = 500) 
		TO FILEGROUP [ChargesStaging]
GO

-- Roughly 1 mins to here.

----------------------------------------------------------------------------------------------
------------------------------ Partition Function --------------------------------------------
----------------------------------------------------------------------------------------------
CREATE PARTITION FUNCTION Credit4MonthPFN(datetime)
AS 
RANGE RIGHT FOR VALUES ('20040601',		-- Jun 2004
						'20040701',		-- Jul 2004
						'20040801',		-- Aug 2004
						'20040901')		-- Sep 2004	
GO

----------------------------------------------------------------------------------------------
------------------------------ Partition Scheme ----------------------------------------------
----------------------------------------------------------------------------------------------
CREATE PARTITION SCHEME [Credit4MonthPS]
AS 
PARTITION [Credit4MonthPFN] TO 
		( [PRIMARY], [PartitionedTablesFG1], [PartitionedTablesFG2], 
		  [PartitionedTablesFG3], [PartitionedTablesFG4])
GO

----------------------------------------------------------------------------------------------
------------------------------ Partition Table -----------------------------------------------
----------------------------------------------------------------------------------------------
CREATE TABLE ChargesPT
( 	charge_no		int			NOT NULL	IDENTITY,
	member_no		int			NOT NULL
						CONSTRAINT ChargesPTMemberNoFK
							REFERENCES dbo.Member(Member_No),
	provider_no		int			NOT NULL
						CONSTRAINT ChargesPTProviderNoFK
							REFERENCES dbo.Provider(Provider_No),
	category_no		int			NOT NULL
						CONSTRAINT ChargesPTCategoryNoFK
							REFERENCES dbo.Category(Category_No),
	charge_dt		datetime 	NOT NULL
						CONSTRAINT ChargesPTChargeDtCK
							CHECK (Charge_dt >= '20040601' 
									AND Charge_dt < '20041101'),
	charge_amt		money		NOT NULL,
	statement_no	int			NOT NULL,
	charge_code		char(2)		NOT NULL
) ON Credit4MonthPS (charge_dt)
GO

ALTER TABLE ChargesPT
ADD CONSTRAINT ChargesPTPK
		PRIMARY KEY CLUSTERED (charge_dt, charge_no) 
--			ON Credit4MonthPS (charge_dt)
GO

EXEC sp_help ChargesPT
GO

----------------------------------------------------------------------------------------------
------------------------------ DATA LOAD (~3.5 mins)--------------------------------------------
----------------------------------------------------------------------------------------------
INSERT ChargesPT (member_no, provider_no, category_no
						, charge_dt, charge_amt
						, statement_no, charge_code)
	SELECT member_no, provider_no, category_no
			, dateadd(yy, 5, charge_dt), (charge_amt + charge_no)/10
			, statement_no, charge_code 
	FROM CreditPT.dbo.Charge
	WHERE month(charge_dt) IN (6, 7, 8, 9)
	ORDER BY charge_dt, charge_no
GO

----------------------------------------------------------------------------------------------
------------------------------ VERIFY DATA DISTRIBUTION --------------------------------------
----------------------------------------------------------------------------------------------
SELECT $partition.Credit4MonthPFN(CPT.Charge_dt) 
			AS [Partition Number]
	, min(CPT.Charge_dt) AS [Min Order Date]
	, max(CPT.Charge_dt) AS [Max Order Date]
	, count(*) AS [Rows In Partition]
FROM dbo.ChargesPT AS CPT
GROUP BY $partition.Credit4MonthPFN(CPT.Charge_dt) 
ORDER BY [Partition Number]
GO

set statistics io on
go
SELECT * FROM ChargesPT
WHERE Charge_dt >= '20040914' 
AND Charge_dt < '20040915' 
----------------------------------------------------------------------------------------------
------------------------------ SET RECOVERY TO FULL and BACKUP! ------------------------------
----------------------------------------------------------------------------------------------
ALTER DATABASE [CreditPT]
	SET RECOVERY FULL
GO

BACKUP DATABASE [CreditPT]
TO DISK = N'C:\SQLskills\CreditPT.bak' WITH INIT
GO

--RESTORE DATABASE [CreditPT]
--FROM DISK = N'C:\SQLskills\CreditPT.bak' WITH REPLACE
--GO