-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Walk your way through this script... there's a TON of coolstuff in here. 
-- (1) You'll setup for Partitioned Views by creating four files and four filegroups 
--     for the Partitioned View and a last file/filegroup for staging data. 
-- (2) You'll copy data into a staging area and then build the clustered indexes
--     on the appropriate filegroup.
-- (3) Once all of the data is partitioned then you'll create a partitioned view using 
--     UNION ALL.
-- (4) You'll run queries against the Partitioned View and you'll see how the optimizer
--     removes redundant data from the execution plan. 
-- (5) What about indexed views used with partitioned views. No, you can't index a partitioned
--     view however, you can create views and then index them - against the base tables.
--     Take for example the sum of charge by member. If you've partitioned June/July/Aug, etc.
--     then summing charges means summing the sums (from the partitions). Is the optimizer
--     smart enough... I guess you'll have to get to the end of the script to see ;).

-- Have fun,
-- kt

USE master
GO

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

USE CreditPV
go


ALTER DATABASE [CreditPV] 
	SET RECOVERY SIMPLE
GO

USE CreditPV
go

ALTER DATABASE CreditPV 
	ADD FILEGROUP PartitionedViewsFilegroup1
go

ALTER DATABASE CreditPV
	ADD FILE
		(NAME = N'CreditPVFGFile1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPVFGFile1.NDF' , 
		SIZE = 50, FILEGROWTH = 10%) 
		TO FILEGROUP [PartitionedViewsFilegroup1]
go

ALTER DATABASE CreditPV 
	ADD FILEGROUP PartitionedViewsFilegroup2
go

ALTER DATABASE CreditPV
	ADD FILE
		(NAME = N'CreditPVFGFile2', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPVFGFile2.NDF' , 
		SIZE = 50, FILEGROWTH = 10%) 
		TO FILEGROUP [PartitionedViewsFilegroup2]
go

ALTER DATABASE CreditPV
	ADD FILEGROUP PartitionedViewsFilegroup3
go

ALTER DATABASE CreditPV
	ADD FILE
		(NAME = N'CreditPVFGFile3', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPVFGFile3.NDF' , 
		SIZE = 50, FILEGROWTH = 10%) 
		TO FILEGROUP [PartitionedViewsFilegroup3]
go

ALTER DATABASE CreditPV
	ADD FILEGROUP PartitionedViewsFilegroup4
go

ALTER DATABASE CreditPV
	ADD FILE
		(NAME = N'CreditPVFGFile4', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPVFGFile4.NDF' , 
		SIZE = 50, FILEGROWTH = 10%) 
		TO FILEGROUP [PartitionedViewsFilegroup4]
go

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

ALTER DATABASE CreditPV
	ADD FILE
		(NAME = N'ChargesStaging', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\CreditPVChargesStaging.NDF' , 
		SIZE = 50, FILEGROWTH = 10%) 
		TO FILEGROUP [ChargesStaging]
go

-- Load the data into the Staging Area and then rebuild the clustered index on the filegroup to move
-- the object once and to get better contiguousness...

----------------------------------------------------------------------------------------------
--------------------------------------- JUNE --------------------------------------------
----------------------------------------------------------------------------------------------
CREATE TABLE ChargeJune04
( 	charge_no		numeric_id		NOT NULL	IDENTITY,
	member_no		numeric_id 	NOT NULL
						CONSTRAINT ChargeJune04MemberNoFK
							REFERENCES dbo.Member(Member_No),
	provider_no		numeric_id
						CONSTRAINT ChargeJune04ProviderNoFK
							REFERENCES dbo.Provider(Provider_No),
	category_no	numeric_id
						CONSTRAINT ChargeJune04CategoryNoFK
							REFERENCES dbo.Category(Category_No),
	charge_dt		datetime 	NOT NULL
						CONSTRAINT ChargeJune04ChargeDtCK
							CHECK (Charge_dt >= '20040601' AND Charge_dt < '20040701'),
	charge_amt		money		NOT NULL,
	statement_no	numeric_id,
	charge_code	status_code
) ON ChargesStaging
go

INSERT ChargeJune04 (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 CreditPV.dbo.Charge
	WHERE month(charge_dt) = 6
	ORDER BY charge_dt, charge_no
go

EXEC sp_help ChargeJune04
-- Will be on ChargesStaging
go

ALTER TABLE ChargeJune04
ADD CONSTRAINT ChargeJune04PK
		PRIMARY KEY CLUSTERED (charge_no) 
			ON [PartitionedViewsFilegroup1]
go

EXEC sp_help ChargeJune04
-- Will be on PartitionedViewsFilegroup1
go

----------------------------------------------------------------------------------------------
--------------------------------------- JULY --------------------------------------------
----------------------------------------------------------------------------------------------
CREATE TABLE ChargeJuly04
( 	charge_no		numeric_id		NOT NULL	IDENTITY,
	member_no		numeric_id 	NOT NULL
						CONSTRAINT ChargeJuly04MemberNoFK
							REFERENCES dbo.Member(Member_No),
	provider_no		numeric_id
						CONSTRAINT ChargeJuly04ProviderNoFK
							REFERENCES dbo.Provider(Provider_No),
	category_no	numeric_id
						CONSTRAINT ChargeJuly04CategoryNoFK
							REFERENCES dbo.Category(Category_No),
	charge_dt		datetime 	NOT NULL
						CONSTRAINT ChargeJuly04ChargeDtCK
							CHECK (Charge_dt >= '20040701' AND Charge_dt < '20040801'),
	charge_amt		money		NOT NULL,
	statement_no	numeric_id,
	charge_code	status_code
) ON ChargesStaging
go

INSERT ChargeJuly04 (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 CreditPV.dbo.Charge
	WHERE month(charge_dt) = 7
	ORDER BY charge_dt, charge_no
go

EXEC sp_help ChargeJuly04
-- Will be on ChargesStaging
go

ALTER TABLE ChargeJuly04
ADD CONSTRAINT ChargeJuly04PK
		PRIMARY KEY CLUSTERED (charge_no) 
			ON [PartitionedViewsFilegroup2]
go

EXEC sp_help ChargeJuly04
-- Will be on PartitionedViewsFilegroup2
go

----------------------------------------------------------------------------------------------
--------------------------------------- August-------------------------------------------
----------------------------------------------------------------------------------------------
CREATE TABLE ChargeAug04
( 	charge_no		numeric_id		NOT NULL	IDENTITY,
	member_no		numeric_id 	NOT NULL
						CONSTRAINT ChargeAug04MemberNoFK
							REFERENCES dbo.Member(Member_No),
	provider_no		numeric_id
						CONSTRAINT ChargeAug04ProviderNoFK
							REFERENCES dbo.Provider(Provider_No),
	category_no	numeric_id
						CONSTRAINT ChargeAug04CategoryNoFK
							REFERENCES dbo.Category(Category_No),
	charge_dt		datetime 	NOT NULL
						CONSTRAINT ChargeAug04ChargeDtCK
							CHECK (Charge_dt >= '20040801' AND Charge_dt < '20040901'),
	charge_amt		money		NOT NULL,
	statement_no	numeric_id,
	charge_code	status_code
) ON ChargesStaging
go

INSERT ChargeAug04 (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 CreditPV.dbo.Charge
	WHERE month(charge_dt) = 8
	ORDER BY charge_dt, charge_no
go

EXEC sp_help ChargeAug04
-- Will be on ChargesStaging
go

ALTER TABLE ChargeAug04
ADD CONSTRAINT ChargeAug04PK
		PRIMARY KEY CLUSTERED (charge_no) 
			ON [PartitionedViewsFilegroup3]
go

EXEC sp_help ChargeAug04
-- Will be on PartitionedViewsFilegroup3
go

----------------------------------------------------------------------------------------------
--------------------------------------- Sept ----------------------------------------------
----------------------------------------------------------------------------------------------
CREATE TABLE ChargeSept04
( 	charge_no		numeric_id		NOT NULL	IDENTITY,
	member_no		numeric_id 	NOT NULL
						CONSTRAINT ChargeSept04MemberNoFK
							REFERENCES dbo.Member(Member_No),
	provider_no		numeric_id
						CONSTRAINT ChargeSept04ProviderNoFK
							REFERENCES dbo.Provider(Provider_No),
	category_no	numeric_id
						CONSTRAINT ChargeSept04CategoryNoFK
							REFERENCES dbo.Category(Category_No),
	charge_dt		datetime 	NOT NULL
						CONSTRAINT ChargeSept04ChargeDtCK
							CHECK (Charge_dt >= '20040901' AND Charge_dt < '20041001'),
	charge_amt		money		NOT NULL,
	statement_no	numeric_id,
	charge_code	status_code
) ON ChargesStaging
go

INSERT ChargeSept04 (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 CreditPV.dbo.Charge
	WHERE month(charge_dt) = 9
	ORDER BY charge_dt, charge_no
go

EXEC sp_help ChargeSept04
-- Will be on ChargesStaging
go

ALTER TABLE ChargeSept04
ADD CONSTRAINT ChargeSept04PK
		PRIMARY KEY CLUSTERED (charge_no) 
			ON [PartitionedViewsFilegroup4]
go

EXEC sp_help ChargeSept04
-- Will be on PartitionedViewsFilegroup4
go

----------------------------------------------------------------------------------------------
---------------------------------- Create View --------------------------------------
----------------------------------------------------------------------------------------------
CREATE VIEW dbo.ChargePV
AS
SELECT * FROM dbo.ChargeJune04
UNION ALL
SELECT * FROM dbo.ChargeJuly04
UNION ALL
SELECT * FROM dbo.ChargeAug04
UNION ALL
SELECT * FROM dbo.ChargeSept04
go

SET STATISTICS IO ON
go

SELECT * 
FROM ChargePV
WHERE Charge_dt >= '20040914' 
	AND Charge_dt < '20040915' 
-- Reviewing statistics IO you should see scan count 0 and logical reads 0 for all tables except September
-- (13344 row(s) affected)
-- Table 'ChargeSept04'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0.
-- Table 'ChargeAug04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'ChargeJuly04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'ChargeJune04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

SELECT OBJECTPROPERTY(object_id('ChargeSept04ChargeDtCK'), 
	'CnstIsNotTrusted')
go

UPDATE STATISTICS ChargeJune04
UPDATE STATISTICS ChargeJuly04
UPDATE STATISTICS ChargeAug04
UPDATE STATISTICS ChargeSept04
go

CREATE VIEW dbo.SumOfAllChargesByMemberJune04
	WITH SCHEMABINDING  -- required if you plan to index this view!
AS
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM dbo.ChargeJune04 AS c
GROUP BY c.member_no
go

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex
	ON dbo.SumOfAllChargesByMemberJune04 (MemberNo) 
go

CREATE VIEW dbo.SumOfAllChargesByMemberJuly04
	WITH SCHEMABINDING  -- required if you plan to index this view!
AS
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM dbo.ChargeJuly04 AS c
GROUP BY c.member_no
go

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex
	ON dbo.SumOfAllChargesByMemberJuly04 (MemberNo) 
go

CREATE VIEW dbo.SumOfAllChargesByMemberAug04
	WITH SCHEMABINDING  -- required if you plan to index this view!
AS
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM dbo.ChargeAug04 AS c
GROUP BY c.member_no
go

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex
	ON dbo.SumOfAllChargesByMemberAug04 (MemberNo) 
go

CREATE VIEW dbo.SumOfAllChargesByMemberSept04
	WITH SCHEMABINDING  -- required if you plan to index this view!
AS
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM dbo.ChargeSept04 AS c
GROUP BY c.member_no
go

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex
	ON dbo.SumOfAllChargesByMemberSept04 (MemberNo) 
go

-- Aggregating the aggregates!
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM ChargePV AS c
GROUP BY c.member_no
go

SELECT c.member_no AS MemberNo, 
	AVG(c.charge_amt) AS TotalSales
FROM ChargePV AS c
GROUP BY c.member_no
ORDER BY c.member_no
go
