/*============================================================================
	File:		0030 - Demo of multiple partitions on multiple filegroups.sql

	Summary:	This script demonstrates the different scenarios
				for the usage of partition schemes

				THIS SCRIPT IS PART OF THE TRACK:
				"Database Partitioning"

	Date:		October 2019

	SQL Server Version: 2012 / 2014 / 2016 / 2017
------------------------------------------------------------------------------
	Written by Uwe Ricken, db Berater GmbH

	This script is intended only as a supplement to demos and lectures
	given by Uwe Ricken.  
  
	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.
============================================================================*/
USE master;
GO

-- Creation of a demo database for partition schemes
IF DB_ID(N'PartitionSchemes') IS NOT NULL
	DROP DATABASE PartitionSchemes;
	GO

CREATE DATABASE PartitionSchemes
ALTER AUTHORIZATION ON DATABASE::PartitionSchemes TO sa;
ALTER DATABASE PartitionSchemes SET RECOVERY SIMPLE;
GO

-- Creation of 5 additional filegroups for the demos
ALTER DATABASE PartitionSchemes ADD FILEGROUP [FG01];
ALTER DATABASE PartitionSchemes ADD FILEGROUP [FG02];
ALTER DATABASE PartitionSchemes ADD FILEGROUP [FG03];
ALTER DATABASE PartitionSchemes ADD FILEGROUP [FG04];
ALTER DATABASE PartitionSchemes ADD FILEGROUP [FG05];
GO

-- Let's go and play around
USE PartitionSchemes;
GO

/* Schema maps each partition to a different filegroup */
IF EXISTS
(
	SELECT * FROM sys.partition_functions WHERE name = N'PF01'
)
	DROP PARTITION FUNCTION PF01;
	GO

IF EXISTS
(
	SELECT * FROM sys.partition_schemes WHERE name = N'PS01'
)
	DROP PARTITION SCHEME PS01;
	GO

CREATE PARTITION FUNCTION PF01 (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO

CREATE PARTITION SCHEME PS01
AS PARTITION PF01
TO (FG01, FG02, FG03, FG04);
GO

SELECT * FROM sys.filegroups AS FG;
SELECT * FROM sys.partition_functions AS PF;
SELECT * FROM sys.partition_schemes AS PS;
SELECT * FROM sys.destination_data_spaces AS DDS;
SELECT * FROM sys.partition_range_values AS PRV;

-- Let's combine all information to an overview
SELECT	PF.type_desc,
		F.name					AS	[FileGroup],
		PF.boundary_value_on_right,
		PRV.boundary_id,
		T.name					AS	data_type,
		CASE WHEN PF.boundary_value_on_right = 0
			THEN N'<=' + CAST(PRV.value AS NVARCHAR(MAX))
			ELSE N'>=' + CAST(PRV.value AS NVARCHAR(MAX))
		END		AS	[Value]
FROM	sys.partition_functions AS PF
		INNER JOIN sys.partition_schemes AS PS
		ON (PF.function_id = PS.function_id)
		INNER JOIN sys.destination_data_spaces AS DDS
		ON (PS.data_space_id = DDS.partition_scheme_id)
		INNER JOIN sys.filegroups AS F
		ON (DDS.data_space_id = F.data_space_id)
		INNER JOIN sys.partition_range_values AS PRV
		ON
		(
			PF.function_id = PRV.function_id
            AND DDS.destination_id = PRV.boundary_id
		)
		INNER JOIN sys.partition_parameters AS PP
		ON (PF.function_id = PP.function_id)
		INNER JOIN sys.types AS T
		ON (PP.system_type_id = T.system_type_id)
ORDER BY
		PRV.boundary_id;
GO
