/*
	============================================================================
	File:		0210 - Maintenance - remove an existing partition.sql

	Summary:	This script demonstrates different techniques to delete
				an existing partition.

	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

SELECT	*
FROM	dbo.view_PartitionInformation
GO

/***********************************************************************/
-- Example 1: Delete data and remove the partition
/***********************************************************************/
BEGIN TRANSACTION;
GO
	-- Delete all data from a dedicated time range
	DELETE	dbo.CustomerOrders
	WHERE	OrderDate >= '20000101'
			AND OrderDate < '20010101';
	GO

	-- It holds an SCH-M-Lock on the table!
	SELECT	OBJECT_NAME(resource_associated_entity_id) AS Object_Name,
			request_mode,
			request_type,
			request_status
	FROM	sys.dm_tran_locks
	WHERE	resource_type = N'OBJECT'
			AND request_session_id = @@SPID;
	GO

	-- Now we can MERGE the partition with the next partition
	ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('20001231');
	GO

COMMIT TRANSACTION;
GO

-- Remove the not used file
ALTER DATABASE [demo_db] REMOVE FILE [Orders_2000]
GO

-- and the filegroup
ALTER DATABASE [demo_db] REMOVE FILEGROUP [P_2000]
GO

/***********************************************************************/
-- Example 2: New feature since Microsoft SQL Server 2016 (!)
/***********************************************************************/
SELECT	*
FROM	dbo.view_PartitionInformation
GO

BEGIN TRANSACTION;
GO
	TRUNCATE TABLE dbo.CustomerOrders WITH (PARTITIONS (1));
	GO

	-- It holds an SCH-M-Lock on the table!
	SELECT	OBJECT_NAME(resource_associated_entity_id) AS Object_Name,
			request_mode,
			request_type,
			request_status
	FROM	sys.dm_tran_locks
	WHERE	resource_type = N'OBJECT'
			AND request_session_id = @@SPID;
	GO

	-- Now we can MERGE the partition with the next partition
	ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('20011231');
	GO

COMMIT TRANSACTION;
GO

-- Remove the not used file
ALTER DATABASE [demo_db] REMOVE FILE [Orders_2001]
GO

-- and the filegroup
ALTER DATABASE [demo_db] REMOVE FILEGROUP [P_2001]
GO

/***********************************************************************/
-- Example 3: Switch out data (!)
/***********************************************************************/
SELECT	*
FROM	dbo.view_PartitionInformation
GO

IF OBJECT_ID(N'dbo.CustomerOrders_Stage') IS NOT NULL
	DROP TABLE dbo.CustomerOrders_Stage;
	GO

CREATE TABLE dbo.CustomerOrders_Stage
(
	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

/*
	Msg 4913, Level 16, State 1, Line 128
	The Staging Table needs to have the same alligned indexes
	as the partitioned table!
*/
ALTER TABLE dbo.CustomerOrders SWITCH PARTITION 1
TO dbo.CustomerOrders_Stage
GO

-- o.k. - let's create all alligned indexes, too
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Stage
ON dbo.CustomerOrders_Stage
(
	OrderDate,
	Id
);
GO

/*
	Msg 4939, Level 16, State 1, Line 146
	The indexes of the staging table must be in the same filegroups
*/
ALTER TABLE dbo.CustomerOrders SWITCH PARTITION 1
TO dbo.CustomerOrders_Stage
GO

-- o.k. - let's create all alligned indexes ON same filegroups
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Stage
ON dbo.CustomerOrders_Stage
(
	OrderDate,
	Id
)
WITH (DROP_EXISTING = ON)
ON OrderDates(OrderDate);
GO

BEGIN TRANSACTION;
GO
	ALTER TABLE dbo.CustomerOrders SWITCH PARTITION 1
	TO dbo.CustomerOrders_Stage PARTITION 1;
	GO

	SELECT	OBJECT_NAME(resource_associated_entity_id)	AS	Object_Name,
			request_mode,
			request_type,
			request_status
	FROM	sys.dm_tran_locks
	WHERE	request_session_id = @@SPID
			AND resource_type = N'OBJECT';
	GO

	DROP TABLE dbo.CustomerOrders_Stage;
	GO
COMMIT TRANSACTION;
GO

SELECT	*
FROM	dbo.view_PartitionInformation
GO

DBCC SHOW_STATISTICS (N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate');

UPDATE STATISTICS dbo.CustomerOrders(cuix_CustomerOrders_OrderDate)
WITH FULLSCAN, INCREMENTAL = ON;
GO
