2

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.

Improve this question

1 Answer 1

Reset to default
9

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.

Improve this answer
2
  • Could you perform the following procedure? 1.Rebuild Clustered index with online ON/Drop_Existing ON to a new or existing filegroup 2.rebuild nonclustered indexes with online on/Drop_Existing ON to the filegroup 3. remove the former partition files from the database. 4. Detach, copy and attach DB on new Standard server. ?
    – Magier
    Commented Oct 17, 2016 at 10:02
  • 1
    @Magier, yes you can use that procedure. Removing files and filegroups is optional. If you perform the operation during a maintenance window, ONLINE=OFF (the default option) should be used, which might run faster. You could also used backup/restore instead of detach/attach to move the database to the SE server. Query sys.dm_db_persisted_sku_features to make sure no other EE-only features are used.
    – Dan Guzman
    Commented Oct 17, 2016 at 11:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.