Step by Step - database partition SQL Server 2008 +

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.

Benefits of Partitioning

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.