	/*============================================================================
	File:		0110 - Implemenation - creation of partitioning function.sql

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

	Date:		February 2015

	SQL Server Version: 2008 / 2012 / 2014
------------------------------------------------------------------------------
	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

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

IF EXISTS (SELECT * FROM sys.partition_functions AS PF WHERE name = 'pf_OrderDate')
	DROP PARTITION FUNCTION pf_OrderDate;
	GO

-- Now we can create a partition function for the separation
-- of orders by year of orderdate
CREATE PARTITION FUNCTION pf_OrderDate(DATE)
AS RANGE LEFT FOR VALUES
(
	'20001231', '20011231', '20021231', '20031231', '20041231',
	'20051231', '20061231', '20071231', '20081231', '20091231',
	'20101231', '20111231', '20121231', '20131231', '20141231',
	'20151231', '20161231', '20171231', '20181231', '20191231'
);
GO

/*
	The following system views (DMV) can be used to investigate
	the current settings
*/
-- What partition functions do we have in the database?
SELECT	name,
		function_id,
		type,
		type_desc,
		fanout,
		boundary_value_on_right,
		is_system,
		create_date,
		modify_date
FROM	sys.partition_functions;
GO

-- What parameter does the partition function use?
SELECT	PF.name,
		PP.parameter_id,
		PP.system_type_id,
		T.name,
		PP.max_length,
		PP.precision,
		PP.scale,
		PP.collation_name,
		PP.user_type_id
FROM	sys.partition_functions AS PF
		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)
GO

-- How many partition boundaries exist?
SELECT	PF.type_desc,
		PF.boundary_value_on_right,
		PRV.boundary_id,
		PRV.parameter_id,
		PRV.value
FROM	sys.partition_functions AS PF
		INNER JOIN sys.partition_range_values AS PRV
		ON (PF.function_id = PRV.function_id)
ORDER BY
		PRV.boundary_id;
GO