/*
	============================================================================
	File:		0220 - Maintenance - Fast Data Load.sql

	Summary:	This script demonstrates how to use the SWITCH command to
				insert new data into a 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

/*
	Standard functionality when inserting new data into partitioned table
*/

SELECT * FROM dbo.view_PartitionInformation
WHERE	[Schema.Table] = 'dbo.CustomerOrders'
ORDER BY
	[Partition #];

-- 1. Step: we add a new filegroup for the data of 2021
IF NOT EXISTS
(
	SELECT * FROM sys.filegroups WHERE name = N'P_2021'
)
ALTER DATABASE demo_db
ADD FILEGROUP [P_2021];
GO

IF NOT EXISTS
(
	SELECT	*
	FROM	sys.master_files AS MF
	WHERE	database_id = DB_ID()
			AND MF.name = N'Orders_2021'
)
ALTER DATABASE demo_db
ADD FILE
(
	NAME = N'Orders_2021',
	FILENAME = N'F:\MSSQL15.SQL_2019\MSSQL\DATA\ORDERS_2021.ndf',
	SIZE = 128MB,
	FILEGROWTH = 128MB
)
TO FILEGROUP [P_2021];
GO

-- 2. Step: we modify the new partition and the partition function
ALTER PARTITION SCHEME OrderDates NEXT USED [P_2021];
GO

ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('20211231');
GO

-- 3. Step: we create a staging table for the new data of 2021
IF OBJECT_ID(N'dbo.NewCustomerOrders', N'U') IS NOT NULL
	DROP TABLE dbo.NewCustomerOrders;
	GO

CREATE TABLE dbo.NewCustomerOrders
(
	Id				INT			NOT NULL,
	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);
GO

-- ... and fill it with data
INSERT INTO dbo.NewCustomerOrders WITH (TABLOCK)
(
	Id, Customer_Id, OrderNumber, InvoiceNumber, OrderDate,
	OrderStatus_Id, Employee_Id, InsertUser, InsertDate
)
SELECT	Id,
        Customer_Id,
        OrderNumber,
        InvoiceNumber,
        DATEADD(YEAR, 3, OrderDate) AS OrderDate,
        OrderStatus_Id,
        Employee_Id,
        InsertUser,
        InsertDate
FROM	CustomerOrders.dbo.CustomerOrders
WHERE	OrderDate >= '20180101'
		AND OrderDate < '20190101';
GO

/*
	Let's try to switch the new data into the partitioned table
*/
ALTER TABLE dbo.NewCustomerOrders
SWITCH TO dbo.CustomerOrders PARTITION 20;
GO

CREATE UNIQUE CLUSTERED INDEX cuix_NewCustomerOrders
ON dbo.NewCustomerOrders
(
	OrderDate,
	Id
);

/*
	fails because the index is not in the same filegroup
*/
ALTER TABLE dbo.NewCustomerOrders
SWITCH TO dbo.CustomerOrders PARTITION 20;
GO

CREATE UNIQUE CLUSTERED INDEX cuix_NewCustomerOrders
ON dbo.NewCustomerOrders
(
	OrderDate,
	Id
)
WITH (DROP_EXISTING = ON)
ON OrderDates(OrderDate);
GO

/*
	fails because the index is not in the same filegroup
*/
ALTER TABLE dbo.NewCustomerOrders
SWITCH PARTITION 20
TO dbo.CustomerOrders PARTITION 20;
GO

DROP TABLE dbo.NewCustomerOrders;
GO
