Using Partitioned Tables with VLDB

1
Using Partitioned Tables with VLDB

• Presented by Geoff Orr

2
About

• Geoff is a Senior Software Architect at SSW,
  specialising in SQL Server.

3
Tonights Agenda

• What is a File Group/Partitioned Table?
• Creating a Partitioned Table
• Adding File Groups
• Archiving File Groups

4
What is a File Group/Partitioned Table?

• File Groups
• Consists of a group of physical files
• Tables and indexes are created on them using the
  on statement
• If there is no on statement the default file
  group is used.
• Non Partitioned Tables
• Non partitioned tables exist on only one file
  group
• Partitioned Tables
• Partitioned tables exist on a series of file
  groups called a scheme.
• A scheme uses a function to determine which file
  group to store a row. Once allocated Rows stay
  put.

5
Default Case
Normal Database
Normal.mdf
Normal.ldf
Primary File Group
Logs
Default File Group
6
Default Case
Bigger Database
Bigger.mdf
Bigger.ldf
Logs
Primary File Group
Default File Group
7
Default Case
Partitioned Database
Quarter Based Scheme
Primary File Group
Logs
Part.mdf
Part.ldf
Default File Group
8
Creating a Partitioned Table

• Create File Groups (with files)
• Create a Partition Function to pick groups
• Create a Partition Scheme to associate the
  function with the file group
• Create the table / indexes on the scheme

9
Adding File Groups

• Create a new file group
• Alter the scheme to use file group as next to be
  used
• Split the range in the function

10
Archiving File Groups - Removing

• Create a table to move data
• Must be exactly the same as partition table
• Must be on the File Group to be archived
• Switch the partition to table using alter table
• Merge the range in the partition function

11
Archiving File Groups - Adding

• Add file group to partition
• Destination table must exist
• Add table to partition table using switch within
  alter table

12
Summary

• What is a File Group/Partitioned Table?
• Creating a Partitioned Table
• Adding File Groups
• Archiving File Groups

13
2 things
GeoffOrr_at_ssw.com.au
14
Thank You!
Partitioned Function is Cool