/*============================================================================
	File:		0130 - implementation - HEAP - 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 / 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

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

SELECT * FROM dbo.view_PartitionInformation;
GO

-- Check the benefits of partitioning when querying data
SET STATISTICS IO, TIME ON;
GO

/*
	(41 rows affected)
	Table 'CustomerOrders'. Scan count 21, logical reads 71021, 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 = 530 ms,  elapsed time = 293 ms.
*/
SELECT	*
FROM	dbo.CustomerOrders
WHERE	Customer_Id = 10
OPTION	(QUERYTRACEON 9130);
GO

SELECT	*
FROM	dbo.CustomerOrders
WHERE	Customer_Id = 10
		AND OrderDate >= '2005-01-01'
		AND OrderDate <= '2005-12-31'
OPTION	(QUERYTRACEON 9130);
GO

/*
	(1339 rows affected)
	Table 'CustomerOrders'. Scan count 6, logical reads 23683, 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 = 171 ms,  elapsed time = 408 ms.
*/
SELECT	*
FROM	dbo.CustomerOrders
WHERE	OrderDate >= '20170101'
		AND OrderDate < '20170102'
OPTION	(QUERYTRACEON 9130);
GO
