/*============================================================================
	File:		0031 - Demo - multiple partitions on one 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

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

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

CREATE PARTITION SCHEME PS01
AS PARTITION PF01
ALL TO ([PRIMARY]);
GO

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

-- Create a table based on the partition schema
CREATE TABLE dbo.Numbers (Id INT NOT NULL)
ON PS01(Id)
WITH (DATA_COMPRESSION = PAGE);
GO

;WITH
	L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
	L1 AS (SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
	L2 AS (SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
	L3 AS (SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
	L4 AS (SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B),
	NS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id FROM L4)
SELECT * FROM NS;
GO

