/*============================================================================
  File:     RegionalRangeCaseStudyPartitionedTable.sql

  MSDN Whitepaper: Partitioned Tables and Indexes in SQL Server 2005
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

  Summary:  This script creates the partitioned table. This script was 
			originally included with the Partitioned Tables and Indexes 
			Whitepaper released on MSDN and written by Kimberly
			L. Tripp. To get more details about this whitepaper please 
			access the whitepaper on MSDN.

  IMPORTANT: For this script to succeed you must have the Northwind database 
			on your server. This is required in order to copy over a regionally 
			based set of sample data. If you do not already have the Northwind
			database on your SQLDev01 instance of SQL Server 2005, use the 
			Backup2000Restore2005.sql script to backup the Northwind database
			from your SQL Server 2000 default instance and restore it to your
			SQLLaunchVPC\SQLDev01 instance.
			
  Date:     January 2006

  SQL Server Version: 9.00.1399.06 (RTM)
------------------------------------------------------------------------------
  Copyright (C) 2005/2006 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.


  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  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 SalesDB
GO

-------------------------------------------------------
-- Create the partition function
-------------------------------------------------------
CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS 
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain' )
GO

-------------------------------------------------------
-- Create the partition scheme
-------------------------------------------------------
CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS 
PARTITION CustomersCountryPFN TO ([France], [Germany], [Italy], [Spain], [UK])
GO

-------------------------------------------------------
-- Create the Customers table on the partition scheme
-------------------------------------------------------
CREATE TABLE [dbo].[Customers](
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [char](7) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)

-------------------------------------------------------
-- Add data
-------------------------------------------------------
INSERT Customers
	SELECT * 
		FROM northwind.dbo.customers 
		WHERE Country IN ('France', 'Germany', 'Italy', 'Spain', 'UK' )

-------------------------------------------------------
-- Verify Partition Ranges
-------------------------------------------------------

SELECT $partition.CustomersCountryPFN(Country) 
			AS 'Partition Number'
	, count(*) AS 'Rows In Partition'
FROM Customers
GROUP BY $partition.CustomersCountryPFN(Country)
ORDER BY 'Partition Number'
GO

-------------------------------------------------------
-- To see the partition information row by row
-------------------------------------------------------
SELECT CompanyName, Country, 
	$partition.CustomersCountryPFN(Country) 
		AS 'Partition Number'
FROM Customers
GO