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.