Data
can be partitioned by two ways; one is dividing columns into multiple tables
and second is dividing data into multiple filegroups. The data in partitioned
tables is horizontally divided into units that can be spread across more than
one filegroup in a database. Partitioning can make large tables more manageable
and scalable.
Partitioning
large tables or indexes can have the following manageability and performance
benefits.
·
You can transfer or access subsets of data quickly and
efficiently, while maintaining the integrity of a data collection.
·
You can perform maintenance operations on one or more partitions
more quickly. The operations are more efficient because they target only these
data subsets, instead of the whole table.
·
You may improve query performance; the query optimizer can
process equi-join queries between two or more partitioned tables faster when
the partitioning columns in the tables are the same, because the partitions
themselves can be joined.
·
When SQL Server performs data sorting for I/O operations, it
sorts the data first by partition. SQL Server accesses one drive at a time, and
this might reduce performance. To improve data sorting performance, stripe the
data files of your partitions across more than one disk by setting up a RAID.
In this way, although SQL Server still sorts data by partition, it can access
all the drives of each partition at the same time.
·
In addition, you can improve performance by enabling lock
escalation at the partition level instead of a whole table. This can reduce
lock contention on the table.
Creating
a partitioned table or index typically happens in four parts:
1.
Create a filegroup or filegroups and corresponding files that
will hold the partitions specified by the partition scheme.
2. Create a partition function that maps the rows of a table into partitions based on the values of a specified column.
3. Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
4. Create or modify a table and specify the partition scheme as the storage location.
5. Insert sample data into SQLAudit table
6. Partition wise count along with partition number can be returned from following query
7. You can query partition wise data using following query.
8. Partition information can be viewed using following query.
9. Performance can be increased by adding index on Partition.
