Databases Reference
In-Depth Information
8
1
12.5
8
2
0
9
0
0
9
1
0
9
2
0
10
0
0
10
1
0
10
2
0
You've all but eliminated the fragmentation and certainly all the fragmentation that matters. An index
rebuild will remove all the fragmentation problems and this demonstration is just to show that a drop
and recreate of an index can remove more fragmentation than a rebuild.
Section 4: Partitioned Tables and Indexes
Now you'll get started with digging into the details of some of these awesome new features, start-
ing with one that you should be particularly excited about: the new partitioned tables and indexes.
You'll start with why you would need to use this new feature, and then how you should use it. You'll
also discover more about what partition tables and indexes are and find out more about how you
use them.
Reasons for UsingPartitioned Tables and Indexes
Partitioned tables are a way to spread a single table over multiple partitions, and while doing so each
partition can be on a separate file group. There are several reasons for doing this, which are covered here.
FasterandEasierDataLoading
If your database has a very large amount of data to load, you might want to consider using a partition
table.
A very large amount of data
doesn't mean a specific amount of data, but any time the load operation
takes longer than is acceptable in the production cycle.
A partition table lets you load the data to an empty table that's not in use by the live data and so has
less impact on concurrent live operations. Clearly there will be an impact to the I/O subsystem, but if
you also have separate file groups on different physical disks, even this has a minimal impact on overall
system performance.
Once the data is loaded to the new table, you can perform a switch to add the new table to the live data.
This switch is a simple metadata change that executes very quickly. So partition tables are a great way to
load large amounts of data, with limited impact on users touching the rest of the data in a table.
FasterandEasierDataDeletionorArchiving
For the very same reasons, partition tables also help you to delete or archive data. If your data is parti-
tioned on boundaries that are also the natural boundaries on which you add or remove data, then the
data is considered to be aligned. When your data is aligned, deleting or archiving data is as simple as
switching a table out of the current partition and then unloading or archiving it at your leisure.
Search WWH ::
Custom Search