/*
	============================================================================
	File:		0132 - implementation - NONCLUSTERED - 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;

CREATE NONCLUSTERED INDEX nix_CustomerOrders_OrderDate
ON dbo.CustomerOrders (OrderDate)
ON OrderDates(OrderDate);
GO

SELECT * FROM dbo.view_PartitionInformation;
GO

DROP INDEX nix_CustomerOrders_OrderDate
ON dbo.CustomerORders;
GO

/*
	Interesting!!!
	After deleting the NCI, the table (HEAP) stays partitioned!
*/
SELECT * FROM dbo.view_PartitionInformation;
GO

SELECT	Id,
		Customer_Id,
		OrderNumber,
		InvoiceNumber,
		OrderDate,
		OrderStatus_Id,
		Employee_Id,
		InsertUser,
		InsertDate
FROM	dbo.CustomerOrders
WHERE	OrderDate >= '20190101'
		AND OrderDate < '20190201'
OPTION	(QUERYTRACEON 9130);
GO