Sql-server – the best way to remove this table partition

partitioningsql serversql-server-2016

I have to change a partitioned table in Sql Server 2008 R2 to a normal table to make my database compatible with Sql Server 2016 Standard Edition.

Actually the table has 5 partitions with the following number of rows:

> boundary, rows 
2009-01-01 00:00:00.000 419 
2010-01-01 00:00:00.000 386031 
2011-01-01 00:00:00.000 1307990 
2012-01-01 00:00:00.000 673183 
NULL                    9743057

The table contains a BLOB (image) column. The total size of the table is around 25 GB.

I have read through the question How to remove a table partition but although it has answered there is no accepted answer and the answers do not address my question entirely.

I realized the ALTER PARTITION FUNCTION MERGE RANGE command, but I do not really understand what will happen. Will the data be merget into one of the existing filegroups and afterwards I will still have a partitioned table?

Do I instead have to copy all the data into a new table with the same structure (may take quite a while…)?

I will have to perform this action during a downtime, so I need a procedure that is as efficient as possible.

Best Answer

To remove table partitioning entirely, rebuild all indexes with a filegroup specification instead of partition scheme. Use CREATE INDEX with the DROP_EXISTING=ON option to do that efficiently.

See MSDN: CREATE INDEX page for syntax.

Related Solutions

Sql-server – move rows between partitions by updating the partition key

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

Sql-server – Partitioning in SQL Server 2008

For what you want to do, I would recommend the following (which is pretty much what you were thinking).

1> Create history tables for the historic data you have - keep the schemas as similar as possible. Split up by some logical grouping (such as year/month) based on how they are going to be queried (say you need to report with in month/year as well as all). Do not worry about the table size of the splits unless they are getting into the TB size range (your dbms should handle it) just make sure that they are appropriately indexed for the queries that need to be run. You should consider putting these onto a different disk to the active data if performance is an issue.

2> Create a routine to move data from the active table to the relevant historic table. Run this periodically. As a practice rebuild the indexes on the table that has had the data removed from it, and maybe update the table statistics. Easiest way to do this is to write a sql script.

3> Consider the reporting you want to do. If you want to only have to deal with 1 table when writing queries, create a view that joins the archived tables together. Create indexes on all the tables to suite the view. This way if you want all the data, select from the view. If you want data from a specific year/month, query that table. The view will look something like:

create view view_all_data as select "Jan12" as month,a.* from data_Jan12 a union select "Feb12" as month,b.* from data_Feb12 b ....

I am assuming here that the system is not a highly used transactional system and that you have windows of low usage to run the analysis queries. If you need to maintain high levels of performance, you may like to do the above in a separate database (separate hardware) and port across the new data that you get from backups.

Related Question