I have a table that consumes almost 1.2 TB of space and has lots of historical data which are mostly irrelevant. Since performing bulk DELETE is going to cause a lot of heavy-lifting, I'm trying to use the concept of horizontal partitioning to create multiple partitions, each containing early data (for eg: 2017,2018... 2021) so that I can simply truncate those partitions which is less than an year, so that I can only retain the relevant operational data.
This is the plan of action that I have in my mind
- Create a partition function that would split the data on a yearly range
- Define the partition Scheme.
- Modify the table to use and apply partition
- Truncate partitions containing irrelevant historical data
I stated the above operations in SQL Server (except the truncate part) like
-> Create partition
CREATE PARTITION FUNCTION Yearly_Exp (datetime2)
AS RANGE RIGHT FOR VALUES
('2016-01-01 00:00:00', '2017-01-01 00:00:00', '2018-01-01 00:00:00'
'2019-01-01 00:00:00', '2020-01-01 00:00:00', '2021-01-01 00:00:00');
-> Define the partition Scheme.
CREATE PARTITION SCHEME Yearly_Scheme
AS PARTITION Yearly_Exp ALL TO ([PRIMARY]);
-> Modify the table to use and apply partition
CREATE UNIQUE CLUSTERED INDEX Partition_Ind ON existing_table(some_timestamp_col, id) ON Yearly_Scheme(some_timestamp_col);
Now coming to my actual question, since the table has been around for a while, there are pre-existing indexes in the table. To be more precise the table has
- Two non-clustered indexes in which one of the indexes has my partitioning key as the index column
- One clustered Index applied through the Primary Key constraint.
Should I take any action to alter these indexes prior to partitioning? (Since there is an index already present with the partitioning key as the indexing column) or should I leave them as it is?
Does the index performance gets affected after partitioning?
SELECT * FROM existing_table WHERE id = 1;
will change from a seek to a full scan if you specifysome_timestamp_col
as the first indexed column.