/*============================================================================
	File:		0020 - Partitioning without Partitioning.sql

	Summary:	This script demonstrates how partitioning prior
				to SQL Server 2016 (SP1) was possible without
				having an Enterprise Edition

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

	Date:		December 2019

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

-- A "manual" partitioning by OrderYear requires a separate table for each order year
-- for 2000 up the current year we create the table for each year in a cursor!
DECLARE	@Year	INT;
DECLARE @exec	NVARCHAR(1024);
DECLARE	@stmt	NVARCHAR(1024) = N'
DROP TABLE IF EXISTS dbo.Orders_%s;

SELECT	*
INTO	dbo.Orders_%s
FROM	CustomerOrders.dbo.CustomerOrders
WHERE	YEAR(OrderDate) = @Year;';

DECLARE	c CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
	SELECT	DISTINCT(YEAR(OrderDate))
	FROM	CustomerOrders.dbo.CustomerOrders
	ORDER BY
			YEAR(OrderDate);

OPEN c;

FETCH NEXT FROM c INTO @YEAR
WHILE @@FETCH_STATUS <> -1
BEGIN
	SET	@exec = REPLACE(@stmt, '%s', CAST(@YEAR AS NVARCHAR(4)));
	EXEC sys.sp_executesql @exec, N'@YEAR INT', @YEAR;

	FETCH NEXT FROM c INTO @YEAR;
END

CLOSE c;
DEALLOCATE c;
GO

-- when all tables are created an access object (view) gets created which covers
-- all entities with a UNION ALL operation.
DROP VIEW IF EXISTS dbo.CustomerOrders;
GO

CREATE VIEW dbo.CustomerOrders
AS
	SELECT * FROM dbo.Orders_2000
	UNION ALL
	SELECT * FROM dbo.Orders_2001
	UNION ALL
	SELECT * FROM dbo.Orders_2002
	UNION ALL
	SELECT * FROM dbo.Orders_2003
	UNION ALL
	SELECT * FROM dbo.Orders_2004
	UNION ALL
	SELECT * FROM dbo.Orders_2005
	UNION ALL
	SELECT * FROM dbo.Orders_2006
	UNION ALL
	SELECT * FROM dbo.Orders_2007
	UNION ALL
	SELECT * FROM dbo.Orders_2008
	UNION ALL
	SELECT * FROM dbo.Orders_2009
	UNION ALL
	SELECT * FROM dbo.Orders_2010
	UNION ALL
	SELECT * FROM dbo.Orders_2011
	UNION ALL
	SELECT * FROM dbo.Orders_2012
	UNION ALL
	SELECT * FROM dbo.Orders_2013
	UNION ALL
	SELECT * FROM dbo.Orders_2014
	UNION ALL
	SELECT * FROM dbo.Orders_2015
	UNION ALL
	SELECT * FROM dbo.Orders_2016
	UNION ALL
	SELECT * FROM dbo.Orders_2017
	UNION ALL
	SELECT * FROM dbo.Orders_2018
	UNION ALL
	SELECT * FROM dbo.Orders_2019
GO

-- First test (awesome)
SET STATISTICS TIME, IO ON;
GO

SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate >= '20190101'
		AND OrderDate < '20190201';
GO

SET STATISTICS TIME, IO OFF;
GO

-- OK - for optimization we create a "partition index" on the order date
-- for a better performance of the query
-- we create a Clustered index on the OrderDate because this attribute is
-- mostly used in this scenario.
DECLARE	@Year	INT;
DECLARE @exec	NVARCHAR(1024);
DECLARE @stmt NVARCHAR(1024) = N'CREATE CLUSTERED INDEX cix_Orders_%s ON dbo.Orders_%s (OrderDate);'

DECLARE	c CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
	SELECT	DISTINCT(YEAR(OrderDate))
	FROM	CustomerOrders.dbo.CustomerOrders
	ORDER BY
			YEAR(OrderDate);

OPEN c;

FETCH NEXT FROM c INTO @Year;
WHILE @@FETCH_STATUS <> -1
BEGIN
	SET @exec = REPLACE(@stmt, '%s', CAST(@Year AS NVARCHAR(4)));
	EXEC sys.sp_executesql @exec;
	FETCH NEXT FROM c INTO @Year;
END

CLOSE c;
DEALLOCATE c;
GO

-- second test (awesome and we need the money!)
SET STATISTICS TIME, IO ON;
GO

SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate >= '20190101'
		AND OrderDate < '20190201';
GO

SET STATISTICS TIME, IO OFF;
GO

-- unfortunately it has NOTHING to do with one benefit of partitioning
-- Partition Elimination
DECLARE	@Year	INT;
DECLARE @exec	NVARCHAR(1024);
DECLARE @stmt NVARCHAR(1024) = N'
ALTER TABLE dbo.Orders_%s
ADD CONSTRAINT chk_OrderDate_%s
CHECK (OrderDate >= ''%s0101'' AND OrderDate < ''%t0101'');'

DECLARE	c CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
	SELECT	DISTINCT(YEAR(OrderDate))
	FROM	CustomerOrders.dbo.CustomerOrders
	ORDER BY
			YEAR(OrderDate);

OPEN c;

FETCH NEXT FROM c INTO @Year;
WHILE @@FETCH_STATUS <> -1
BEGIN
	SET @exec = REPLACE(@stmt, '%s', CAST(@Year AS NVARCHAR(4)));
	SET @exec = REPLACE (@exec, '%t', CAST(@Year + 1 AS NVARCHAR(4)));

	EXEC sys.sp_executesql @exec;
	FETCH NEXT FROM c INTO @Year;
END
GO

-- third test (cool - partitioning without partitioning!)
SET STATISTICS TIME, IO ON;
GO

SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate >= '20190101'
		AND OrderDate < '20190201';
GO

SET STATISTICS TIME, IO OFF;
GO

-- Clean the kitchen
DECLARE @stmt NVARCHAR(1024);
DECLARE c CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
	SELECT	N'DROP TABLE IF EXISTS ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N';'
	FROM	sys.tables
	WHERE	type = N'U';

OPEN c;

FETCH NEXT FROM c INTO @stmt;
WHILE @@FETCH_STATUS <> -1
BEGIN
	EXEC sp_executesql @stmt;
	FETCH NEXT FROM c INTO @stmt;
END

CLOSE c;
DEALLOCATE c;
GO