I have some very large tables in my database, but a substantial chunk of this data is "old".

Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other limitation is that I cannot modify the database, meaning add filegroups to it. The way things stand right now, everything resides on the PRIMARY file group.

I was thinking to partition these tables into a few partitions, such as "new", "old", "archived" and similar. I do have a "status" column I would like to use for this purpose.

Given the described scenario and limitations, I was wondering if partitioning makes any sense here. In other words, if my table is partitioned in this way, but all of the partitions sit on the same filegroup, will SQL Server be smart enough to find that special area in the underlying file where my "new" data resides and not touch the area with "old" data?

To put it differently, if, let's say, 80% of my data is "old". Does SQL Server have a mechanism to avoid accessing 100% of the underlying files and access only 20% which contains "new" data (assuming, of course, I specify my partitioning column in the WHERE clause of the queries).

I guess to answer this, one would need to understand how the partitioning is implemented internally. I appreciate any pointers.


2 Answers 2


There are two advantages to partitioning a table in the same filegroup:

  1. Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance. Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
  2. Leveraging partition elimination and (possibly) partition level locking to improve query maintenance. I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

  • If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
  • To avoid performance issues, you should align your partitions. This means all your indexes should include your partition key, whether as an include or as part of the index itself.
  • Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012). If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it. Kendra Little has an excellent list of resources where you can get started.

  • If I have partitioned Clustered index, don't all non-clustered indexes already contain the partitioning column as a row locator?
    – Zikato
    May 20, 2019 at 12:09
  • Can you please help me understand why "This means all your indexes should include your partition key, whether as an include or as part of the index itself." is in the suggestion? Why it is important to have the key in all indexes even if I am not querying on the basis of that key / column ? Oct 11, 2021 at 9:32

The answer is "yes". It does have a mechanism on any query that filters inputs based on the logic used to define the partitions.

You have to have the appropriate filter, though, or all the partition will be scanned. This would typically involve having date filters (in your case) to choose the partition.

One way to enforce this is to have views that access only one partition, with the right logic in the view.

  • I wonder how much the performance gain would be for partitioning to the same physical disc..
    – yakya
    Apr 17, 2017 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.