Table Partitioning

/*PARTITIONING EXAMPLE
I am going to use the Employee table in metroalt for this partition
though I am not going to make the partitions in MetroAlt itself,
Rather I will make a new database and a new table and copy the data
from metroAlt into that database.
For the partitions we will use 1995-1999, 2000-2004, 2004-2009, 2010 forward
Our database will need to have five file groups.
Here's the pattern:
Create database PartitionTest
go
Alter database PartitionTest
Add FileGroup Sales2005;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2005',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2005File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )
 To filegroup Sales 2005
*/


if exists
   (Select name from sys.Databases 
   where name = 'EmployeePartition')
Begin
Drop Database EmployeePartition
end
Go
-- create the database.
Create database EmployeePartition

go
--add the first file group and file
alter database EmployeePartition
Add Filegroup Employees1995Group
Go
Alter database EmployeePartition
Add File
(name='Employees1995',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees1995File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to Filegroup Employees1995Group

go

alter database EmployeePartition
Add Filegroup Employees2000Group
Go
Alter database EmployeePartition
Add File
(name='Employees2000',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2000File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2000Group
go

alter database EmployeePartition
Add Filegroup Employees2005Group
Go
Alter database EmployeePartition
Add File
(name='Employees2005',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2005File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To Filegroup Employees2005group
go

alter database EmployeePartition
Add Filegroup Employees2010Group
Go
Alter database EmployeePartition
Add File
(name='Employees2010',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2010File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2010Group

go

alter database EmployeePartition
Add Filegroup Employees2015Group
Go
Alter database EmployeePartition
Add File
(name='Employees2015',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2015File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To filegroup Employees2015Group

/*Next we want to create the partion function and
the partition scheme.
*/

Use EmployeePartition

Create Partition Function Fx_Hiredate (date)
As range left
For values('19941231', '19991231','20041231','20091231')

Go

--create the partition schema
Create Partition scheme sch_HireDate
As Partition fx_HireDate
to (Employees1995Group, Employees2000Group, Employees2005Group, Employees2010Group, Employees2015Group)

--now create the table that uses the partition schema
CREATE TABLE [dbo].[Employee](
 [EmployeeKey] [int],
 [EmployeeLastName] [nvarchar](255),
 [EmployeeFirstName] [nvarchar](255),
 [EmployeeAddress] [nvarchar](255),
 [EmployeeCity] [nvarchar](255),
 [EmployeeZipCode] [nchar](5),
 [EmployeePhone] [nchar](10),
 [EmployeeEmail] [nvarchar](255),
 [EmployeeHireDate] [date] 
) on sch_HireDate(EmployeeHireDate)

--insert into the table
Insert into Employee(EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate)
Select EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate
From MetroAlt.Dbo.Employee

Select * from Employee

--1995-1999
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=2

--2000-2004
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=3

--2005-2009
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=4

--2010-2014

Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=5