What is the best way to remove this table partition?

Question

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.

Answer 1

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.