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.

Community
  • 1
Magier
  • 4,787
  • 8
  • 43
  • 87

1 Answers1

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.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Dan Guzman
  • 28,168
  • 2
  • 44
  • 68
  • 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 Oct 17 '16 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 Oct 17 '16 at 11:01