Table Partitioning 2013

Here is a script for table partiioning

Use Master
/*************************
this creates a database with several
files and file groups
so we can partition a large table 
across file groups.
This can improve performance
in large tables

*************************/
if exists
 (SELECT name from sys.Databases where Name='PartitionTest')
Begin
Drop Database PartitionTest
End

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 Sales2005
Go
Alter database PartitionTest
Add FileGroup Sales2006;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2006',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2006File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )
To filegroup Sales2006
Go
Alter database PartitionTest
Add FileGroup Sales2007;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2007',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2007File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )

to filegroup Sales2007

Go
Alter database PartitionTest
Add FileGroup Sales2008;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2008',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2008File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )

to filegroup Sales2008
Go
Alter database PartitionTest
Add FileGroup Sales2009;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2009',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2009File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )

to filegroup Sales2009
go

Here is a picture of the database properties showing the file groups

-- next we need to create a partition function
--this tells it where to break
--range left means the numbers in the values
--represent the highest value or endpoint
--for that partition
--there must always be one more file group than partition
go
use partitionTest
Create Partition Function Fx_orderdate (dateTime)
As range left
For values('20051231', '20061231','20071231','20081231')
go
--now we create a partitioning scheme that uses the 
--partition function above
Create Partition scheme sch_orderdate
As Partition fx_orderdate
to (Sales2005, sales2006, sales2007, sales2008, sales2009)

--now we will create a table using the partition scheme
go
CREATE TABLE [PurchaseOrderHeader](
 [PurchaseOrderID] [int] NOT NULL,
 [RevisionNumber] [tinyint] NOT NULL,
 [Status] [tinyint] NOT NULL,
 [EmployeeID] [int] NOT NULL,
 [VendorID] [int] NOT NULL,
 [ShipMethodID] [int] NOT NULL,
 [OrderDate] [datetime] NOT NULL,
 [ShipDate] [datetime] NULL,
 [SubTotal] [money] NOT NULL,
 [TaxAmt] [money] NOT NULL,
 [Freight] [money] NOT NULL,
 [TotalDue]  MONEY NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 
) on sch_orderdate(orderDate)
 

--now we will populate the table from adventure works
go
Insert into PurchaseOrderHeader(
PurchaseOrderID, 
RevisionNumber, 
Status, 
EmployeeID, 
VendorID, 
ShipMethodID, 
OrderDate, 
ShipDate, 
SubTotal, 
TaxAmt, 
Freight, 
TotalDue, 
ModifiedDate)
SELECT 
PurchaseOrderID, 
RevisionNumber, 
Status, 
EmployeeID, 
VendorID, 
ShipMethodID, 
OrderDate, 
ShipDate, 
SubTotal, 
TaxAmt, 
Freight, 
TotalDue, 
ModifiedDate
FROM AdventureWorks2012.Purchasing.PurchaseOrderHeader

--partition queries

Select * from PurchaseOrderHeader

--2006
Select * from PurchaseOrderHeader
where $partition.Fx_OrderDate(OrderDate)=2

--2007
Select * from PurchaseOrderHeader
where $partition.fx_orderDate(OrderDate)=3

--2008
Select * from PurchaseOrderHeader
where $partition.fx_orderDate(OrderDate)=4

--etc.