Table Partitioning: Adding\Removing partitions using SPLIT, MERGE

source In this tip we’ll see one of the advantage of partitioning a very large table, which is the ability to add or remove partitions instantaneously to help you with sliding window scenarios.

follow Lets first create partition function and partition scheme. Here i m using monthly partition scheme.

where can i buy estrace cream CREATE PARTITION FUNCTION [PF_MONTHLY](date) AS RANGE LEFT FOR VALUES ('2016-01-31', '2016-02-29', N'2016-03-31', N'2016-04-30', N'2016-05-31') CREATE PARTITION SCHEME [PS_MONTHLY] AS PARTITION [PF_MONTHLY] ALL TO ([XYZ_FILE_GROUP])

Now lets add new partition for next month using partition split.

ALTER PARTITION SCHEME ps_monthly NEXT used XYZ_FILE_GROUP

ALTER PARTITION FUNCTION pf_function() SPLIT RANGE ('2016-06-30')

SQL Server Fragmentation: What it is, what are it’s types (Part 1)

In this post, we’ll talk about what fragmentation is, what are its different types in SQL Server, how it impacts performance (and when it doesn’t).

This is the first post in the multi part series. In the following posts in this series, we’ll see what causes fragmentation part-2, how to check for it(using DMVs) part-3, how to address it(remove it) part-4, and how to avoid it(control it) part-5.
Read more SQL Server Fragmentation: What it is, what are it’s types (Part 1)