/*============================================================================
  Prepared for Partitioning Sessions presented by Rick Heiges

  Date:     February 2006

  Version:	SQL Server 2005 RTM (Tested on March 2006 CTP SP1)
  Modified: May 2007 for SQL Teach (Test on 2005 RTM SP2)

------------------------------------------------------------------------------
  Copyright (C) 2006-7 Rick Heiges,  All rights reserved.
  
 
  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.
============================================================================*/

-- Create the demo DB - RickPartition 
CREATE DATABASE [RickPartition] ON  PRIMARY 
( NAME = N'RickPartition', FILENAME = N'C:\DATA2005\RickPartition.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
FILEGROUP [SECONDARY] 
( NAME = N'RickPartition2', FILENAME = N'C:\DATA2005\RickPartition2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'RickPartition_log', FILENAME = N'C:\DATA2005\RickPartition_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'RickPartition', @new_cmptlevel=90
GO
EXEC [RickPartition].[dbo].[sp_fulltext_database] @action = 'disable'
GO
ALTER DATABASE [RickPartition] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [RickPartition] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [RickPartition] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [RickPartition] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [RickPartition] SET ARITHABORT OFF 
GO
ALTER DATABASE [RickPartition] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [RickPartition] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [RickPartition] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [RickPartition] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [RickPartition] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [RickPartition] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [RickPartition] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [RickPartition] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [RickPartition] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [RickPartition] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [RickPartition] SET RECOVERY FULL 
GO
ALTER DATABASE [RickPartition] SET  MULTI_USER 
GO
ALTER DATABASE [RickPartition] SET PAGE_VERIFY CHECKSUM  
GO

use RickPartition
go



-- Create Partiton Function that has 3 dividing points -> 4 partitions
CREATE PARTITION FUNCTION pfn_tens(int)
AS RANGE RIGHT
FOR VALUES ( 10, 20, 30 ) 
go

-- Select to prove RangeRight - also replace "null" below with 10 
-- to see the result
SELECT $PARTITION.pfn_tens (null) ;
GO

-- Create Partiton Function that has 3 dividing points -> 4 partitions
-- Change to LEFT
CREATE PARTITION FUNCTION pfn_tensL(int)
AS RANGE LEFT
FOR VALUES ( 10, 20, 30 ) 
go

-- Select to prove RangeLeft - also replace "null" below with 10 
-- to see the result
SELECT $PARTITION.pfn_tensL (null) ;
GO

-- Remove pfn_tensL for clarity 
DROP PARTITION FUNCTION [pfn_tensL]
go

-- Create a Partition Scheme that uses pfn_tens values to separate data 
-- into partitions.
-- All Partitions will be in the same filegroup in this example.
CREATE PARTITION SCHEME ps_empnum
AS PARTITION pfn_tens
 ALL  TO (  [PRIMARY] )
go

-- Create a Table that utilizes the partitioning scheme.
CREATE TABLE dbo.Emp1
	(
	EmpID int NOT NULL,
	Name varchar(50) NOT NULL,
	SSN char(10) NULL
	)  ON ps_empnum(EmpID)
GO
ALTER TABLE dbo.Emp1 ADD CONSTRAINT
	PK_Emp1 PRIMARY KEY CLUSTERED 
	(
	EmpID
	) WITH( STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON) ON ps_empnum(EmpID)

GO

-- Insert Some Values
INSERT INTO dbo.Emp1 VALUES(4, 'Nazium, Jim', '133224444')
go
INSERT INTO dbo.Emp1 VALUES(10, 'Shunary, Dick', '188228844')
go
INSERT INTO dbo.Emp1 VALUES(15, 'Duzz, Betty', '134747444')
go
INSERT INTO dbo.Emp1 VALUES(2, 'Scott, Montgomery', '193930014')
go
INSERT INTO dbo.Emp1 VALUES(-12, 'Maha, Gilad', '144884494')
go
INSERT INTO dbo.Emp1 VALUES(33, 'Nazium, Jim', '133224144')
go
INSERT INTO dbo.Emp1 VALUES(40, 'Claws, Sandy', '199338444')
go
INSERT INTO dbo.Emp1 VALUES(24, 'Morgan, Jim', '122334444')
go
INSERT INTO dbo.Emp1 VALUES(40001, 'De Lion, Dan', '133242444')
go

-- Look at the data
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp1
go


-- Create a Partition Scheme that uses pfn_tens values to separate data
-- into partitions.
-- Partitions will alternate between PRIMARY and SECONDARY partitions
CREATE PARTITION SCHEME ps_empnum2
AS PARTITION pfn_tens
TO (  [PRIMARY], [SECONDARY], [PRIMARY], [SECONDARY] )
go

-- Create a Table that utilizes the partitioning scheme.
CREATE TABLE dbo.Emp2
	(
	EmpID int NOT NULL,
	Name varchar(50) NOT NULL,
	SSN char(10) NULL
	)  ON ps_empnum2(EmpID)
GO
ALTER TABLE dbo.Emp2 ADD CONSTRAINT
	PK_Emp2 PRIMARY KEY CLUSTERED 
	(
	EmpID
	) WITH( STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON) ON ps_empnum2(EmpID)

GO

-- Insert Values into Emp2 Table
INSERT INTO dbo.Emp2 VALUES(4, 'Nazium, Jim', '233224444')
go
INSERT INTO dbo.Emp2 VALUES(10, 'Shunary, Dick', '288228844')
go
INSERT INTO dbo.Emp2 VALUES(15, 'Duzz, Betty', '234747444')
go
INSERT INTO dbo.Emp2 VALUES(2, 'Scott, Montgomery', '293930014')
go
INSERT INTO dbo.Emp2 VALUES(-12, 'Maha, Gilad', '244884494')
go
INSERT INTO dbo.Emp2 VALUES(33, 'Nazium, Jim', '323224444')
go
INSERT INTO dbo.Emp2 VALUES(40, 'Claws, Sandy', '299338444')
go
INSERT INTO dbo.Emp2 VALUES(42, 'Morgan, Jim', '222334444')
go
INSERT INTO dbo.Emp2 VALUES(40001, 'De Lion, Dan', '232342444')
go

-- Look at the data
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp2
go

-- ***********************************************








-- Create an Index that is Aligned that is also unique
CREATE UNIQUE INDEX IX_UniqueSSNEmp1 
    ON dbo.Emp1 ( SSN ASC ) 
    ON  ps_empnum(EmpID) 
go
-- The above will create an error because a unique index cannot 
-- span multiple partitions even if all partitions are in the 
-- same filegroup.


-- Create an Index that is Aligned that is also unique
CREATE UNIQUE INDEX IX_UniqueSSNEmp2 
    ON dbo.Emp2 ( SSN ASC ) 
    ON  ps_empnum2(EmpID) 
go
-- The above code should generate an error because a unique index
-- cannot span multiple partitions.  However, a unique index can still
-- be created on this table, but it has to be on a single filegroup and
-- not utilize a partitioning scheme

-- Create an Index that is NOT Aligned that is unique
CREATE UNIQUE INDEX IX_UniqueSSNEmp2FG 
    ON dbo.Emp2 ( SSN ASC ) 
    ON  SECONDARY
go

-- Create a Non-Clustered Non-Unique Index on SSN
CREATE INDEX IX_SSNEmp2FG 
    ON dbo.Emp2 ( SSN ASC ) 
    ON  ps_empnum2(EmpID) 
go

-- Attempt to Rebuild ONLY the portion of the index in Partition 1
ALTER INDEX IX_SSNEmp2FG
ON dbo.Emp2
REBUILD Partition = 1 WITH (ONLINE = ON);
-- The above statement should generate an error because the ONLINE
-- option is not available for a single Partition.  It is available
-- for the all partitions.

-- Rebuild the index just for Partition 1
ALTER INDEX IX_SSNEmp2FG
ON dbo.Emp2
REBUILD Partition = 1

-- Rebuild Index with the Online option
ALTER INDEX IX_SSNEmp2FG
ON dbo.Emp2
REBUILD WITH (ONLINE = ON);

-- ***********************************************







-- Create a Table that will be used for SWITCHing.
CREATE TABLE dbo.EmpSwitch
	(
	EmpID int NOT NULL,
	Name varchar(50) NOT NULL,
	SSN char(10) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.EmpSwitch ADD CONSTRAINT
	PK_EmpSwitch PRIMARY KEY CLUSTERED 
	(
	EmpID
	) WITH( STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- Create Constraint to match Partition 1
ALTER TABLE [dbo].[EmpSwitch]  WITH CHECK 
ADD  CONSTRAINT [CK_EmpSwitch] CHECK  (([EmpID]<(10)))
go

-- See the rows in Emp1
SELECT * from dbo.Emp1
GO

-- See the rows from EmpSwitch - no rows at this time
SELECT * from dbo.EmpSwitch
GO

-- Switch the 1st partition between Emp1 and EmpSwitch
ALTER TABLE dbo.Emp1 SWITCH PARTITION 1 TO dbo.EmpSwitch ;
GO

-- See the rows in Emp1 - no data with EmpID < 10
SELECT * from dbo.Emp1
GO

-- See the rows from EmpSwitch - 3 rows
SELECT * from dbo.EmpSwitch
GO

-- Switch Data into EMPTY Partition 1
ALTER TABLE dbo.EmpSwitch SWITCH TO dbo.Emp1 PARTITION 1 ;
GO

-- See the rows in Emp1
SELECT * from dbo.Emp1
GO

-- See the rows from EmpSwitch
SELECT * from dbo.EmpSwitch
GO

-- Try to switch data between matching Partition Tables
ALTER TABLE dbo.Emp1 SWITCH PARTITION 1 TO dbo.Emp2 PARTITION 1 ;
GO
-- The above statement will fail. Because there is data in Partition 1
-- of Emp2.  Modify rows in Partition 1 of Emp2.
UPDATE dbo.Emp2
SET EmpID = EmpID+300
WHERE EmpID < 11
go
-- Try to switch data between partitions again
ALTER TABLE dbo.Emp1 SWITCH PARTITION 1 TO dbo.Emp2 PARTITION 1 ;
GO
-- The above switch will fail because there is a NON-ALIGNED index
-- on Emp2.
-- Drop the Index causing the problem
DROP INDEX IX_UniqueSSNEmp2FG on dbo.Emp2;
GO

-- Try to switch data between partitions again
ALTER TABLE dbo.Emp1 SWITCH PARTITION 1 TO dbo.Emp2 PARTITION 1 ;
GO
-- This will fail too because of the non-unique index on SSN. Drop it.
DROP INDEX IX_SSNEmp2FG on dbo.Emp2;
GO

-- Try to switch data between partitions again
ALTER TABLE dbo.Emp1 SWITCH PARTITION 1 TO dbo.Emp2 PARTITION 1 ;
GO
-- The above statement WILL work now that the Partition 1 of Emp2
-- is empty and the non-aligned idex was removed.

-- See the rows in Emp1
SELECT * from dbo.Emp1
GO

-- See the rows from Emp2
SELECT * from dbo.Emp2
GO

-- Switch the rows back to Emp1
ALTER TABLE dbo.Emp2 SWITCH PARTITION 1 TO dbo.Emp1 PARTITION 1 ;
GO

-- See the rows in Emp1
SELECT * from dbo.Emp1
GO

-- See the rows from Emp2
SELECT * from dbo.Emp2
GO

-- System Views for Partitioning
select * from sys.partition_functions
go
select * from sys.partition_range_values
go
select * from sys.data_spaces
go
select * from sys.partitions
go
select * from sys.partition_schemes
go
select * from sys.destination_data_spaces
go
-- Dynamic Management View for Partitions
select * from sys.dm_db_partition_stats
go

-- My View creted from System Views
USE [RickPartition]
GO
/****** Object:  View [dbo].[PartitionInfo]    Script Date: 02/14/2006 13:14:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PartitionInfo]
AS
SELECT     sys.data_spaces.name, sys.data_spaces.data_space_id, sys.destination_data_spaces.partition_scheme_id, 
                      sys.destination_data_spaces.destination_id, sys.partition_schemes.name AS Partition_Scheme_Name, 
                      sys.partition_functions.name AS PartFuncName
FROM         sys.data_spaces INNER JOIN
                      sys.destination_data_spaces ON sys.data_spaces.data_space_id = sys.destination_data_spaces.data_space_id INNER JOIN
                      sys.partition_schemes ON sys.partition_schemes.data_space_id = sys.destination_data_spaces.partition_scheme_id INNER JOIN
                      sys.partition_functions ON sys.partition_functions.function_id = sys.partition_schemes.function_id

GO


-- See the Partition Info
select * from dbo.PartitionInfo where Partition_Scheme_Name = 'ps_empnum2'
go
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp2
go

-- Modify the Partition Function for a MERGE operation
ALTER PARTITION FUNCTION pfn_tens()
MERGE RANGE (10);
go
-- See the Partition Info
select * from dbo.PartitionInfo where Partition_Scheme_Name = 'ps_empnum2'
go
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp2
go

-- Tell the system to use the SECONDARY filegroup if a SPLIT occurs
ALTER PARTITION SCHEME ps_empnum2
NEXT USED SECONDARY;
go
-- Add another Partition using SPLIT
ALTER PARTITION FUNCTION pfn_tens()
SPLIT RANGE (80);
go


-- See the Partition Info
select * from dbo.PartitionInfo where Partition_Scheme_Name = 'ps_empnum2'
go
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp2
go

--Remove Boundary Point at 30 
ALTER PARTITION FUNCTION pfn_tens()
MERGE RANGE (30);
go

--Examine the data again - notice the partition numbers
select *, $PARTITION.pfn_tens(EmpID) as PartitionNumber from Emp2
go


SELECT [name]
      ,[data_space_id]
      ,[partition_scheme_id]
      ,[destination_id]
      ,[Partition_Scheme_Name]
      ,[PartFuncName]
  FROM [RickPartition].[dbo].[PartitionInfo]