Advertisements
Remove Table Partition.
--Remove table partition completely
-- Drop PK or CI
DROP INDEX Cx_tbl_partition_test on [tbl_partition_test] /* drop the clustered if already exists if not create new CI on primary FG*/
ALTER TABLE [dbo].[tbl_partition_test] DROP CONSTRAINT [PK_C1810429_tbl_partition_test] WITH ( ONLINE = OFF )
/* Create clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]" or Dummy part is key to removing the partition scheme from the table ! */
CREATE CLUSTERED INDEX Cx_tbl_partition_test ON [tbl_partition_test] (ID,Date_Col) ON FG_Dummy
DROP PARTITION SCHEME PS_myPartitionScheme
DROP PARTITION FUNCTION PF_myDateRange
/* drop the dummy clustered index you created, as it was only created to free the table from the partitioning scheme */
DROP INDEX Cx_tbl_partition_test ON [tbl_partition_test]
--Create the original PK
ALTER TABLE [dbo].[tbl_partition_test] ADD CONSTRAINT [PK_C1810429_tbl_partition_test] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Date_Col] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PS_myPartitionScheme]([Date_Col])
GO
