USE Master;
GO
--- Step 1 : Create New Test Database with two different filegroups.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionDB')
	DROP DATABASE PartitionDB;
GO
CREATE DATABASE PartitionDB ON PRIMARY
	(NAME='PartitionDB_Part1',FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part1.mdf',SIZE=3, MAXSIZE=100,FILEGROWTH=1 );
GO

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part2
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part3
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part4
GO

-------------------------------------------------------------------------------

alter database PartitionDB 
	add file (NAME = 'PartitionDB_Part2',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part2.ndf', 
			SIZE  =3,MAXSIZE=100,FILEGROWTH=1 ) to filegroup PartitionDB_Part2;
	
alter database PartitionDB 	
	add file (NAME = 'PartitionDB_Part3',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part3.ndf', 
			SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part3;
GO
alter database PartitionDB 	
	add file (NAME = 'PartitionDB_Part4',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part4.ndf', 
			SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part4;
GO

USE PartitionDB;
GO

--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
	AS RANGE LEFT FOR VALUES (500,1000,2500);
GO



--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME PartitionDB_PartitionScheme
	AS PARTITION PartitionDB_PartitionRange TO ([PRIMARY],[PartitionDB_Part2],[PartitionDB_Part3],[PartitionDB_Part4]);
GO


--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
	(
	ID INT identity 
	,Date DATETIME
	) ON PartitionDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON TestTable(ID)
	ON PartitionDB_PartitionScheme (ID);
GO

declare @count int 
set @count =1 

While (@count < = 5000)
begin 
	INSERT INTO TestTable (Date) VALUES (GETDATE());
	set @count = @count + 1 
end
GO



--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO





CREATE TABLE TestTable_Archive
	(
	ID INT identity 
	,Date DATETIME
	) 
GO

declare @count int 
set @count =1 

While (@count < = 40)
begin 
	INSERT INTO TestTable_Archive (Date) VALUES (GETDATE());
	set @count = @count + 1 
end
GO


CREATE UNIQUE CLUSTERED INDEX IX_TestTable_Archive ON TestTable_Archive(ID)
	
GO

-- make the switch 
ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1; 
GO 

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ALTER TABLE TestTable SWITCH PARTITION 1 TO TestTable_Archive ; 

declare @count int 
set @count =31 

While (@count < = 40)
begin 
	INSERT INTO TestTable_Archive (id,Date) VALUES (@count,GETDATE());
	set @count = @count + 1 
end
GO

ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1; 

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass