/*
	============================================================================
	File:		0200 - Maintenance - Add a new partition.sql

	Summary:	This script demonstrates how to add another new partition
				for new values to an existing partitioned table

	Date:		September 2019

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

-- Add a new filegroup for the new data
IF NOT EXISTS
(
	SELECT * FROM sys.filegroups WHERE name = N'P_2020'
)
ALTER DATABASE demo_db
ADD FILEGROUP [P_2020];
GO

-- Add a new file for the new data for the new filegroup
IF NOT EXISTS
(
	SELECT	*
	FROM	sys.master_files AS MF
	WHERE	database_id = DB_ID()
			AND MF.name = N'P_2020'
)
ALTER DATABASE demo_db
ADD FILE
(
	NAME = N'Orders_2020',
	FILENAME = N'F:\MSSQL15.SQL_2019\MSSQL\DATA\ORDERS_2020.ndf',
	SIZE = 128MB,
	FILEGROWTH = 128MB
)
TO FILEGROUP [P_2020];
GO

-- Alter the partition schema and add the new filegroup as USED NEXT
ALTER PARTITION SCHEME OrderDates NEXT USED [P_2020];
GO

-- Alter the partition function and add the new boundery value
ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('20201231');
GO

SELECT * FROM dbo.view_PartitionInformation;
GO

-- Now we can add the new values for 
BEGIN TRANSACTION;
GO
	INSERT INTO dbo.CustomerOrders
	(
		Customer_Id,
		OrderNumber,
		InvoiceNumber,
		OrderDate,
		OrderStatus_Id,
		Employee_Id,
		InsertUser,
		InsertDate
	)
	SELECT	Customer_Id,
			OrderNumber,
			InvoiceNumber,
			DATEADD(YEAR, 20, OrderDate)	AS	OrderDate,
			OrderStatus_Id,
			Employee_Id,
			InsertUser,
			InsertDate
	FROM	CustomerOrders.dbo.CustomerOrders
	WHERE	OrderDate >= '20000101'
			AND OrderDate < '20010101';
	GO

	SELECT * FROM sys.dm_tran_locks
	WHERE	request_session_id = @@SPID;
COMMIT TRANSACTION;
GO

SELECT * FROM dbo.view_PartitionInformation
ORDER BY [Partition #];
GO

TRUNCATE TABLE dbo.CustomerOrders WITH (PARTITIONS (1));
GO
ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('20001231')

-- DROP FILE 2000
-- DROP FILEGROUP 2000

ALTER INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders REBUILD PARTITION = 1
WITH (DATA_COMPRESSION = PAGE);
GO

ALTER TABLE dbo.CustomerOrders
ADD [TestCol] NVARCHAR(10) NULL DEFAULT ('Test');
GO

SET STATISTICS IO, TIME ON;
GO
SELECT * FROM dbo.CustomerOrders
WHERE	$PARTITION.pf_OrderDate(OrderDate) = 1;
GO

SELECT * FROM dbo.CustomerOrders
WHERE	$PARTITION.pf_OrderDate(OrderDate) = 2;
GO

