/*============================================================================
	File:		0120 - implementation - create the partition scheme.sql

	Summary:	This script creates the partitioning schema for the distribution
				of order dates into the different filegroups

	Date:		September 2019

	SQL Server Version: 2008 / 2012 / 2014 / 2017 / 2019
------------------------------------------------------------------------------
	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 demo_db;
GO

CREATE PARTITION SCHEME [OrderDates]
AS PARTITION pf_OrderDate
TO
(
	[P_2000], [P_2001], [P_2002], [P_2003], [P_2004],
	[P_2005], [P_2006], [P_2007], [P_2008], [P_2009],
	[P_2010], [P_2011], [P_2012], [P_2013], [P_2014],
	[P_2015], [P_2016], [P_2017], [P_2018], [P_2019]
	,[PRIMARY]
)
GO

/*
	The following system views (DMV) can be used to investigate
	the information about partition schemes
*/
-- 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
