-------------------------------------------------------
-- Range Partitions Exercise 
-- FILE: Script3 - RangePartitionedTable.sql

-- SQL Server 2005
-- Scripts tested on SQL Server 2005
-- 	9.00.1187.07 (June CTP)

-- Partitioned Tables Hands-on Lab Exercises
-- July 2005
-------------------------------------------------------

USE AdventureWorks
GO

-------------------------------------------------------
-- Create the partition function
-------------------------------------------------------
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS 
RANGE RIGHT FOR VALUES 
(
		'20030701',
		'20031001',
		'20040101',
		'20040401')
GO

-------------------------------------------------------
-- Create the partition scheme
-------------------------------------------------------
CREATE PARTITION SCHEME OrderDatePScheme
AS 
PARTITION OrderDateRangePFN 
TO ([PRIMARY], [2003Q3],[2003Q4],[2004Q1],[2004Q2])

-- The first partition (in a RIGHT-based partition 
-- function will ALWAYS be empty - this will help 
-- to eliminate record relocation when data is 
-- switched in/out. Because this first partition will
-- remain empty, using the PRIMARY filegroup is 
-- acceptible as it's only a logical locator, no
-- rows will ever reside there. 
GO

-------------------------------------------------------
-- Create the OrdersRange table on the partition scheme
-------------------------------------------------------
CREATE TABLE AdventureWorks.[dbo].[OrdersRange]  
(
	[OrderID] [int] NOT NULL,
	[EmployeeID] [int] NULL,
	[VendorID] [int] NULL,
	[TaxAmt] [money] NULL,
	[Freight] [money] NULL,
	[SubTotal] [money] NULL,
	[Status] [tinyint] NOT NULL ,
	[RevisionNumber] [tinyint] NULL,
	[ModifiedDate] [datetime] NULL,
	[ShipMethodID]	tinyint NULL,
	[ShipDate] [datetime] NOT NULL, 
	[OrderDate] [datetime] NOT NULL
			CONSTRAINT OrdersRangeYear
			CHECK ([OrderDate] >= '20030701' 
				AND [OrderDate] < '20040701'), 
	[TotalDue] [money] NULL
) ON OrderDatePScheme (OrderDate)
GO

-------------------------------------------------------
-- Add data to the OrdersRange table
-------------------------------------------------------
INSERT INTO OrdersRange 
SELECT o.[OrderID] 
			, o.[EmployeeID]
			, o.[VendorID]
			, o.[TaxAmt]
			, o.[Freight] 
			, o.[SubTotal] 
			, o.[Status] 
			, o.[RevisionNumber] 
			, o.[ModifiedDate] 
			, o.[ShipMethodID] 
			, o.[ShipDate] 
			, o.[OrderDate] 
			, o.[TotalDue] 
FROM dbo.Orders AS o
GO

-------------------------------------------------------
-- Confirm Row to Partition Location
-------------------------------------------------------
SELECT OrderDate, 
		$partition.OrderDateRangePFN(OrderDate) 
			AS 'Partition Number'
FROM OrdersRange
ORDER BY OrderDate
GO

-------------------------------------------------------
-- Verify Partition Ranges
-------------------------------------------------------
SELECT $partition.OrderDateRangePFN(OrderDate) 
			AS 'Partition Number'
	, min(OrderDate) AS 'Min Order Date'
	, max(OrderDate) AS 'Max Order Date'
	, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1 
GO

-------------------------------------------------------
-- Compare the query plans on the two tables 
-- before indexes are added.
-- Turn on "Include Actual Execution Plan" from Query 
-- drop-down menu OR use Toolbar icon.
-------------------------------------------------------

SET STATISTICS IO ON
go

SELECT * FROM Orders
SELECT * FROM OrdersRange
GO

-------------------------------------------------------
-- Now create a clustered indexes on the 
-- non-partitioned table (Orders) as well
-- as the Partitioned table (OrdersPartitioned)
-------------------------------------------------------

-- First, the partitioned table
CREATE CLUSTERED INDEX OrdersRangeCLInd
ON OrdersRange(OrderDate, OrderID)
ON OrderDatePScheme(OrderDate)
GO

-- Create a clustered index on the non-partitioned table
CREATE CLUSTERED INDEX OrdersCLInd
ON Orders(OrderDate, OrderID)
ON [PRIMARY]
GO 

-------------------------------------------------------
-- Compare the query plans on the two tables 
-- after the index
-------------------------------------------------------
SELECT * FROM Orders
SELECT * FROM OrdersRange
GO