Partitions

A more full description of this process can be found in the book.

Partitioning a table divides the data into separate files on separate file groups. The database developer can determine which data goes on which file. This can make it easier to manage and allow faster access. It is possible to query just one of the file groups which can make queries much quicker.

In order to be able to create partitions, you must have a database with multiple filegroups. We will start by creating a database:

---

Use Master
Go
Create database PartitionTest
Go

---

Now we will alter the database to add 3 filegroups

---

Alter Database PartitionTest
Add FileGroup FG1
Alter Database PartitionTest
Add FileGroup FG2
Alter Database PartitionTest
Add FileGroup FG3

Go

---

Now we have to add files to those file groups

---

Alter Database PartitionTest
Add File
(Name=FG1_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_3.ndf', size=2mb)
To FIleGroup FG1

Alter Database PartitionTest
Add File
(Name=FG2_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_4.ndf', size=2mb)
To FIleGroup FG2

Alter Database PartitionTest
Add File
(Name=FG3_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_5.ndf', size=2mb)
To FIleGroup FG3

---

Now we need to change the context to the current database

---

Use PartitionTest

---

To partition a table or view you must first create a partition function and a partitio scheme. The partition function shows what the dividing values are. The scheme determines which file groups the data will reside on

---

Create Partition Function donationdateFunc (datetime)
As
Range Right
For Values('2/1/2010','4/1/2010')
Go

Create Partition Scheme donationDateScheme
As
Partition donationdateFunc
To (FG1, FG2, FG3)
Go

---

Now we will create a table that uses the partition. We cannot declare the primary key in the table, because, in order for the partition to work, the column we are using to divide the data on must be a clustered index. The only difference between this and a normal table definition is the on donationDateScheme clause that follows the table definition.

---

Create table Donation
(
 DonationKey int not null, 
 DonationDate Datetime not null, 
 DonationAmount money, 
 PersonKey int, 
 EmployeeKey int
)
on donationDateScheme(DonationDate)

---

Now we will alter the table to add a clustered index and a primary key. The primary key must include the DonationKey, the DonationDate and the partition scheme.

---

Create clustered Index ix_DonationDate on Donation(DonationDate)
on donationDateScheme(DonationDate)

Alter table Donation
Add Constraint pk_Donation Primary Key nonClustered (DonationKey, DonationDate)
on donationDateScheme(DonationDate)

---

Now we are going to add some data. We are going to copy all the information from the CommunityAssist donation table into our new donation table

---

Insert into Donation(DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey)
Select DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey
From CommunityAssist.dbo.Donation

---

Finally, we will do some queries. The first one just returns the whole table. The second one returns the data on the first partition, and the third on returns the data on the second partition

---

Select * from Donation

Select * from Donation 
where $partition.donationDateFunc(DonationDate)=2

Select * from Donation 
where $partition.donationDateFunc(DonationDate)=3