[SQL Snacks Video] SQL Server Table Partitioning 101

I hope you’re hungry for another SQL Snack! In fact, this will be one of a series of snacks (dare I call it a SQL Snack pack?). Table partitioning is a fantastic feature that is easy to learn and can significantly improve your OLTP and Data-warehouse environments. It can be a little intimidating because it is tricky to get started with, but once you get the basics down you’ll realize it’s pretty straight forward and a very useful feature to have.  I will be providing the code and outline for each of the SQL Snacks related to table partitioning so that you have a chance to practice on your own.  Happy partitioning!

In this first video I will discuss the basics of Table Partitioning and go through an example which accomplishes the following:
Create a database with multiple files and filegroups
Create a Partition Function and a Partition Scheme based on date
Create a Table on the Partition
Insert Data into the Table
Investigate how the data is stored according to partition
 
Code:

----Partitioning 101
USE master
IF EXISTS(select name from sys.databases where name='PartitionTest')
DROP DATABASE PartitionTest


CREATE DATABASE [PartitionTest]
 ON  PRIMARY 
( NAME = N'PartitionTest', FILENAME = N'Z:\SQLDATA\PartitionTest.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), 
FILEGROUP [FGARCHIVE] 
( NAME = N'FGARCHIVE', FILENAME = N'Z:\SQLDATA\FGARCHIVE.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FG2013] 
( NAME = N'FG2013', FILENAME = N'Z:\SQLDATA\FG2013.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2014] 
( NAME = N'FG2014', FILENAME = N'Z:\SQLDATA\FG2014.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2015] 
( NAME = N'FG2015', FILENAME = N'Z:\SQLDATA\FG2015.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'PartitionTest_log', FILENAME = N'Y:\SQLLOGS\PartitionTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

USE PartitionTest

CREATE PARTITION FUNCTION partFn_Date (datetime)
AS RANGE RIGHT FOR VALUES ('2013-01-01','2014-01-01')
GO

CREATE PARTITION SCHEME part_Date
AS PARTITION partFn_Date
TO (FGARCHIVE,FG2013,FG2014,FG2015)
GO

Create Table Orders
(
OrderID Int Identity (10000,1),
OrderDesc Varchar(50),
OrderDate datetime,
OrderAmount money
)
ON part_Date (OrderDate)
GO

---Insert 2012 Orders
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2012-01-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2012-02-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2012-03-01',RAND()*100)

---Insert 2013 Orders
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2013-01-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2013-02-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2013-03-01',RAND()*100)

---Insert 2014 Orders
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2014-01-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2014-02-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2014-03-01',RAND()*100)

---Insert 2015 Orders
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2015-01-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2015-02-01',RAND()*100)
INSERT INTO Orders Values ('Order ' + Cast(SCOPE_IDENTITY() as varchar(10)),'2015-03-01',RAND()*100)

---Find rows and corresponding partitions
SELECT *,$Partition.partFn_Date(ORDERDate)
FROM Orders

---find ranges for partitions
select *
from sys.partition_functions f
inner join sys.partition_range_values rv on f.function_id=rv.function_id
where f.name = 'partFn_Date'

----Find number of rows per partition
select p.*
from sys.partitions p
inner join sys.tables t on p.object_id=t.object_id
and t.name = 'orders'

----other useful system views with paritioning info

select *
from sys.partition_schemes