Basics of Table Partitions
Partitioning is the way to segregate your data into subsets. The main reason for doing this is for query
performance. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required instead of whole table.
Benefits of Partitioning
Manageability
Query Performance
Data Archival
Partitioned objects will increase the performance of Select, Insert, Update and Delete queries.
Note : This feature is available only in Enterprise Edition
Steps to Partitioning SQL Server Table
1. Create Partition Function
2. Create Partition Scheme
3. Create partition Table
Step 1 -- Partition Function - The partition function defines how you want to partition of Data. This partition function is not dependent on any table, we are generically defining a technique for splitting data. We need to define partitions by specifying the boundaries, for example we have a sales table that contains information of sales data for last 3 years. We can partition that table into three partitions using the following function.
Create partition function PartitionByYear (datetime) as range left for values
('2011-01-01T00:00:00', '2012-01-01T00:00:00', '2013-01-01T00:00:00')
If I used “RANGE RIGHT” the first partition include all values less than 2011, and second contains values between 2011 and 2012 and third values between 2012 and 2013. Range Left will have include values less than or equal to 31-12-2011, the second partition starts from 1-1-2012 onwards and third from 1-1-2013 onwards.
Step 2 – Partition Scheme Creation – After defining partition function we need to create a partition
scheme defining where you want to partition it. This scheme directly links with partitions to filegroups. If we have 3 filegroups (fg1, fg2, fg3 then below is the syntax to create partition scheme).
Create Partition Scheme Sales_PartScheme as partition partitionByYear to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])
If you observe the above example that partition scheme is also an independent object still we haven’t linked it to any specific table in the database, which means this is a reusable object where we can use this partition scheme on any number of database tables.
Step 3 – Partitioning a Table -- After creating partition scheme now we are ready to create a partitioned table. It is a straight forward process where we need to add partition scheme name to ON clause at the time of table creation.
For Example
Create table sales (sales_id bigint, item_name varchar(30), item_desc varchar(200), item_make varchar(100), sale_date datetime) on Sales_PartScheme(sale_date);
Below views used to view partitions
sys.partitions, sys.partition_functions, sys.partition_schemes
Partitioning is the way to segregate your data into subsets. The main reason for doing this is for query
performance. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required instead of whole table.
Benefits of Partitioning
Manageability
Query Performance
Data Archival
Partitioned objects will increase the performance of Select, Insert, Update and Delete queries.
Note : This feature is available only in Enterprise Edition
Steps to Partitioning SQL Server Table
1. Create Partition Function
2. Create Partition Scheme
3. Create partition Table
Step 1 -- Partition Function - The partition function defines how you want to partition of Data. This partition function is not dependent on any table, we are generically defining a technique for splitting data. We need to define partitions by specifying the boundaries, for example we have a sales table that contains information of sales data for last 3 years. We can partition that table into three partitions using the following function.
Create partition function PartitionByYear (datetime) as range left for values
('2011-01-01T00:00:00', '2012-01-01T00:00:00', '2013-01-01T00:00:00')
If I used “RANGE RIGHT” the first partition include all values less than 2011, and second contains values between 2011 and 2012 and third values between 2012 and 2013. Range Left will have include values less than or equal to 31-12-2011, the second partition starts from 1-1-2012 onwards and third from 1-1-2013 onwards.
Step 2 – Partition Scheme Creation – After defining partition function we need to create a partition
scheme defining where you want to partition it. This scheme directly links with partitions to filegroups. If we have 3 filegroups (fg1, fg2, fg3 then below is the syntax to create partition scheme).
Create Partition Scheme Sales_PartScheme as partition partitionByYear to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])
If you observe the above example that partition scheme is also an independent object still we haven’t linked it to any specific table in the database, which means this is a reusable object where we can use this partition scheme on any number of database tables.
Step 3 – Partitioning a Table -- After creating partition scheme now we are ready to create a partitioned table. It is a straight forward process where we need to add partition scheme name to ON clause at the time of table creation.
For Example
Create table sales (sales_id bigint, item_name varchar(30), item_desc varchar(200), item_make varchar(100), sale_date datetime) on Sales_PartScheme(sale_date);
Below views used to view partitions
sys.partitions, sys.partition_functions, sys.partition_schemes
No comments: