/*============================================================================
	File:		0131 - implementation - CLUSTERED - distribute data over partitions.sql

	Summary:	This script uses the partition function to distribute the
				data from the CustomerOrders table over all partitions

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

	Date:		October 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

-- Create and fill the table CustomerOrders
EXEC dbo.CreateCustomerOrders;
GO

SELECT * FROM dbo.view_PartitionInformation;
GO
/*
	Msg 1908, Level 16, State 1, Line 84
	Column 'OrderDate' is partitioning column of the index 'cuix_CustomerOrders_Id'.
	Partition columns for a unique index must be a subset of the index key.
*/
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders (Id)
ON OrderDates(OrderDate);
GO

-- This will work IF the C.I. is not UNIQUE!
CREATE CLUSTERED INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders (Id)
ON OrderDates(OrderDate);
GO

SET STATISTICS TIME, IO ON;
GO
/*
	Table 'CustomerOrders'. Scan count 21, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	(1 row affected)

	 SQL Server Execution Times:
	   CPU time = 16 ms,  elapsed time = 240 ms.
*/
SELECT * FROM dbo.CustomerOrders
WHERE	Id = 1000014
OPTION	(QUERYTRACEON 9130);
GO

SELECT * FROM dbo.view_PartitionInformation;
GO

-- Otherwise the partition key has to be part of the UNIQUE index
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders
(
	Id,
	OrderDate
)
WITH DROP_EXISTING
ON OrderDates(OrderDate);
GO

/*
	Table 'CustomerOrders'.
		Scan count 21,
		logical reads 60

	(1 row affected)

	 SQL Server Execution Times:
	   CPU time = 0 ms,  elapsed time = 191 ms.
*/
SELECT * FROM dbo.CustomerOrders
WHERE	Id = 1000014
OPTION	(QUERYTRACEON 9130);
GO

/*
	How SQL Server determines the page where Order 1000014 is placed?
*/
SELECT * FROM dbo.view_AllocationUnits;
GO

DBCC TRACEON (3604);
DBCC PAGE (0, 3, 50, 3);
GO

DBCC PAGE (0, 3, 9608, 3);
GO

DBCC PAGE (0, 3, 9576, 3) WITH TABLERESULTS;
GO

-- what happens if we search for a date only?
/*
	Table 'CustomerOrders'. Scan count 5, logical reads 12433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	SQL Server Execution Times:
	CPU time = 343 ms,  elapsed time = 881 ms.
*/
SELECT	*
FROM	dbo.CustomerOrders
WHERE	OrderDate >= '2017-01-01'
		AND OrderDate < '2017-02-01'
OPTION	(QUERYTRACEON 9130);
GO

-- The index will only be performant if we look for BOTH predicates!
/*
	Table 'CustomerOrders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	 SQL Server Execution Times:
	   CPU time = 0 ms,  elapsed time = 194 ms.
*/
SELECT * FROM dbo.CustomerOrders
WHERE	Id = 1000014
		AND OrderDate >= '2017-01-01'
		AND OrderDate <= '2017-12-31'
OPTION	(QUERYTRACEON 9130);
GO

-- turn the order of the index columns and see the difference!
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders (OrderDate, Id)
WITH (DROP_EXISTING = ON)
ON OrderDates(OrderDate);
GO

/*
	Table 'CustomerOrders'. Scan count 21, logical reads 71172, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	 SQL Server Execution Times:
	   CPU time = 922 ms,  elapsed time = 405 ms.
*/
SELECT	*
FROM	dbo.CustomerOrders
WHERE	Id = 1000014
OPTION	(QUERYTRACEON 9130);
GO

/*
	Table 'CustomerOrders'.
		Scan count 1,
		logical reads 1008

	 SQL Server Execution Times:
	   CPU time = 125 ms,  elapsed time = 966 ms.
*/
SELECT	*
FROM	dbo.CustomerOrders
WHERE	OrderDate >= '20170101'
		AND OrderDate < '20170201'
OPTION	(QUERYTRACEON 9130);
GO

/*
	Table 'CustomerOrders'.
		Scan count 1,
		logical reads 1008

	 SQL Server Execution Times:
	   CPU time = 125 ms,  elapsed time = 966 ms.
*/
SELECT * FROM dbo.CustomerOrders
WHERE	Id = 1000014
		AND OrderDate >= '20170101'
		AND OrderDate < '20170201'
OPTION	(QUERYTRACEON 9130);
GO