/*============================================================================
	File:		0001 - helper functions for workshop.sql

	Summary:	This script creates views, stored procedures and/or functions
				which will be used in the workshop for analysis and 

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

	Date:		October 2019

	SQL Server Version: 2016 / 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

IF OBJECT_ID(N'dbo.view_PartitionInformation', N'V') IS NOT NULL
	DROP VIEW dbo.view_PartitionInformation;
	GO

CREATE VIEW dbo.view_PartitionInformation
AS
	/*
		The code inside the view is thankfully from Kendra Little
		https://littlekendra.com/2017/01/31/which-filegroup-is-that-partition-using-how-many-rows-does-it-have/
	*/
	SELECT	TOP 100 PERCENT
			QUOTENAME(sc.name) + N'.' + QUOTENAME(so.name)	AS [Schema.Table],
			si.index_id										AS [Index ID],
			si.type_desc									AS [Structure],
			si.name											AS [Index],
			stat.row_count									AS [Rows],
			stat.in_row_reserved_page_count /128.0			AS [In-Row MB],
			stat.lob_reserved_page_count * 128.0			AS [LOB MB],
			p.partition_number								AS [Partition #],
			pf.name											AS [Partition Function],
			CASE pf.boundary_value_on_right
				WHEN 1 THEN 'Right / Lower'
				ELSE 'Left / Upper'
			END												AS [Boundary Type],
			prv.value										AS [Boundary Point],
			fg.name											AS [Filegroup]
	FROM	sys.partition_functions AS pf
			INNER JOIN sys.partition_schemes AS ps
			ON ps.function_id=pf.function_id
			INNER JOIN sys.indexes AS si
			ON si.data_space_id=ps.data_space_id
			INNER JOIN sys.objects AS so
			ON si.object_id = so.object_id
			INNER JOIN sys.schemas AS sc
			ON so.schema_id = sc.schema_id
			INNER JOIN sys.partitions AS p
			ON
			(
				si.object_id=p.object_id 
				AND si.index_id=p.index_id
			)
			LEFT JOIN sys.partition_range_values AS prv
			ON
			(
				prv.function_id=pf.function_id
				AND p.partition_number= CASE pf.boundary_value_on_right
											WHEN 1 THEN prv.boundary_id + 1
											ELSE prv.boundary_id
										END
			)
			INNER JOIN sys.dm_db_partition_stats AS stat
			ON
			(
				stat.object_id=p.object_id
				AND stat.index_id=p.index_id
				AND stat.index_id=p.index_id AND stat.partition_id=p.partition_id
				AND stat.partition_number=p.partition_number
			)
			INNER JOIN sys.allocation_units as au
			ON
			(
				au.container_id = p.hobt_id
				AND au.type_desc ='IN_ROW_DATA'
			)
			INNER JOIN sys.filegroups AS fg
			ON fg.data_space_id = au.data_space_id
	ORDER BY
			[Schema.Table],
			[Index ID],
			[Partition Function],
			[Partition #];
GO

IF OBJECT_ID(N'dbo.view_AllocationUnits', N'V') IS NOT NULL
	DROP VIEW dbo.view_AllocationUnits;
	GO

CREATE VIEW dbo.view_AllocationUnits
AS
	-- check the allocation units and it's dependend IAM pages
	SELECT	SIAU.total_pages,
			SIAU.used_pages,
			SIAU.data_pages,
			P.partition_number,
			sys.fn_PhysLocFormatter(SIAU.root_page)			AS root_page,
			sys.fn_PhysLocFormatter(SIAU.first_iam_page)	AS first_iam_page,
			fg.name
	FROM	sys.system_internals_allocation_units AS SIAU
			INNER JOIN sys.partitions AS P
			ON (SIAU.container_id = P.partition_id)
			INNER JOIN sys.allocation_units as au
			ON
			(
				au.container_id = p.hobt_id
				AND au.type_desc ='IN_ROW_DATA'
			)
			INNER JOIN sys.filegroups AS fg
			ON fg.data_space_id = au.data_space_id
	WHERE	P.object_id = OBJECT_ID(N'dbo.CustomerOrders', N'U');
GO

IF OBJECT_ID(N'dbo.CreateCustomerOrders', N'P') IS NOT NULL
	DROP PROC dbo.CreateCustomerOrders;
	GO

CREATE PROC dbo.CreateCustomerOrders
AS
BEGIN
	SET NOCOUNT ON;

	RAISERROR (N'Creating table...', 0, 1) WITH NOWAIT;

	IF OBJECT_ID(N'dbo.CustomerOrders', N'U') IS NOT NULL
		DROP TABLE dbo.CustomerOrders;

	CREATE TABLE dbo.CustomerOrders
	(
		Id				INT			NOT NULL	IDENTITY (1, 1),
		Customer_Id		INT			NOT NULL,
		OrderNumber		CHAR(10)	NOT NULL,
		InvoiceNumber	CHAR(10)	NOT NULL,
		OrderDate		DATE		NOT NULL,
		OrderStatus_Id	INT			NOT NULL,
		Employee_Id		INT			NOT NULL,
		InsertUser		SYSNAME		NOT NULL,
		InsertDate		DATETIME	NOT NULL
	) ON OrderDates(OrderDate);

	RAISERROR (N'Filling table with data...', 0, 1) WITH NOWAIT;
	SET IDENTITY_INSERT dbo.CustomerOrders ON;
	INSERT INTO dbo.CustomerOrders WITH (TABLOCK)
		(
			Id,
			Customer_Id,
			OrderNumber,
			InvoiceNumber,
			OrderDate,
			OrderStatus_Id,
			Employee_Id,
			InsertUser,
			InsertDate
		)
	SELECT	Id,
			Customer_Id,
			OrderNumber,
			InvoiceNumber,
			OrderDate,
			OrderStatus_Id,
			Employee_Id,
			InsertUser,
			InsertDate
	FROM	CustomerOrders.dbo.CustomerOrders;

	SET IDENTITY_INSERT dbo.CustomerOrders OFF;
	SET NOCOUNT OFF;
END
GO