How does one Remove a Partition from a Table?

Question

I have managed to add a Partition to a Table (Logs) but needed to create a Rollback script in case it needs to be removed. Unfortunately, this has now failed and Logs now has no primary key as a result of failing part-way through the rollback script and I have no way to add it back as I get the error...

Column 'SuperLogId' is partitioning column of the index 'PK__Logs__0E6B88F2'. Partition columns for a unique index must be a subset of the index key.

when trying to run this:

ALTER TABLE dbo.Logs
ADD PRIMARY KEY CLUSTERED (Id ASC)

So I tried following this guide (https://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html) and ended up having to write this to generate a script to merge all my dynamically-created partitions.

DECLARE @partitionsTable dbo.NVarCharCollectionTableType --User-defined table type to hold a collection of NVarChars.

INSERT INTO @partitionsTable
SELECT CONCAT('ALTER PARTITION FUNCTION Logs_SuperLogId_PartitionFunction() MERGE RANGE (', CONVERT(NVARCHAR, [Value]), ')')
FROM SYS.PARTITION_SCHEMES
INNER JOIN SYS.PARTITION_FUNCTIONS ON PARTITION_FUNCTIONS.FUNCTION_ID = PARTITION_SCHEMES.FUNCTION_ID
INNER JOIN SYS.PARTITION_RANGE_VALUES ON PARTITION_RANGE_VALUES.FUNCTION_ID = PARTITION_FUNCTIONS.FUNCTION_ID
WHERE PARTITION_SCHEMES.Name = 'Logs_SuperLogId_PartitionScheme'
AND PARTITION_FUNCTIONS.Name = 'Logs_SuperLogId_PartitionFunction'
ORDER BY [Value] ASC

DECLARE @statement NVARCHAR(MAX)

SELECT @statement =
    CASE
        WHEN @statement IS NULL
        THEN CAST([Text] AS NVARCHAR(MAX))
        ELSE CONCAT(@statement, '; ', [Text])
    END
    FROM @partitionsTable
    ORDER BY [Text] ASC

SELECT @statement

EXECUTE SP_EXECUTESQL @statement

ALTER PARTITION SCHEME Logs_SuperLogId_PartitionScheme NEXT USED [PRIMARY]

The guide suggested this would help somehow but it didn't! I still get the same error when trying to re-add the Primary Key and still get these errors for trying to drop the Partition Function and Partition Scheme!

DROP PARTITION SCHEME Logs_SuperLogId_PartitionScheme

The partition scheme "Logs_SuperLogId_PartitionScheme" is currently being used to partition one or more tables.

DROP PARTITION FUNCTION CatLogs_CatSessionLogId_PartitionFunction

Partition function 'Logs_SuperLogId_PartitionFunction' is being used by one or more partition schemes.

How is my Partition Scheme still being used? Why can't I just get rid of it and it be not used anymore? I just want to de-partition my Logs table and re-add its original clustered primary key (which I had to previously remove and replace with a non-clustered primary key to make SuperLogId have a clustered index on it so it could be partitioned upon).

Update:

I was able to use the following hack to get the Partition removed from my table but I still can't drop the Partition Scheme or Function.

--HACK: Dummy Index to disassociate the table from the partitioning scheme.
CREATE CLUSTERED INDEX IX_Logs_Id ON dbo.Logs(Id) ON [Primary]

--Now that the table has been disassociated with the partition, this dummy index can be dropped.
DROP INDEX IX_Logs_Id ON dbo.Logs

I have since ran this script to find out which tables are using any Partitions in my database and it returns nothing, as expected.

SELECT DISTINCT TABLES.NAME
FROM SYS.PARTITIONS
INNER JOIN SYS.TABLES ON PARTITIONS.OBJECT_ID = TABLES.OBJECT_ID
WHERE PARTITIONS.PARTITION_NUMBER <> 1

This allowed me to re-add the Primary key but I still get the The partition scheme "Logs_SuperLogId_PartitionScheme" is currently being used... error when trying to drop the Partition Scheme.

Based on the Microsoft documentation (https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-partition-scheme-transact-sql?view=sql-server-2017), the Partition Scheme should be droppable if there are no tables or indices references it. Therefore I subsequently also ran this script to check for an index using it...

SELECT DISTINCT indexes.NAME
FROM SYS.PARTITIONS
INNER JOIN SYS.indexes ON indexes.index_id = partitions.index_id
WHERE PARTITIONS.PARTITION_NUMBER <> 1

...And it returned nothing! So what on earth is using my Partition Scheme?!

Answer 1

I was able to remove the Partition from its table with the following code.

--HACK: Dummy Index to disassociate the table from the partitioning scheme.
CREATE CLUSTERED INDEX IX_Logs_Id ON dbo.Logs(Id) ON [Primary]

--Now that the table has been disassociated with the partition, this dummy index can be dropped.
DROP INDEX IX_Logs_Id ON dbo.Logs

Then, using the following script, found out that two indices were still holding onto the Partition Scheme.

SELECT SCHEMA_NAME(B.SCHEMA_ID) SCHEMANAME, B.NAME TABLENAME, C.INDEX_ID, C.NAME INDEXNAME, C.TYPE_DESC,
A.PARTITION_NUMBER, D.NAME DATASPACENAME, F.NAME SCHEMADATASPACENAME,
H.VALUE DATARANGEVALUE, A.ROWS,
J.IN_ROW_RESERVED_PAGE_COUNT, J.LOB_RESERVED_PAGE_COUNT,
J.IN_ROW_RESERVED_PAGE_COUNT+J.LOB_RESERVED_PAGE_COUNT TOTALPAGECOUNT,
I.LOCATION
FROM SYS.PARTITIONS A
JOIN SYS.TABLES B ON A.OBJECT_ID = B.OBJECT_ID
JOIN SYS.INDEXES C ON A.OBJECT_ID = C.OBJECT_ID AND A.INDEX_ID = C.INDEX_ID
JOIN SYS.DATA_SPACES D ON C.DATA_SPACE_ID = D.DATA_SPACE_ID
LEFT JOIN SYS.DESTINATION_DATA_SPACES E ON E.PARTITION_SCHEME_ID = D.DATA_SPACE_ID AND A.PARTITION_NUMBER = E.DESTINATION_ID
LEFT JOIN SYS.DATA_SPACES F ON E.DATA_SPACE_ID = F.DATA_SPACE_ID 
LEFT JOIN SYS.PARTITION_SCHEMES G ON D.NAME = G.NAME
LEFT JOIN SYS.PARTITION_RANGE_VALUES H ON G.FUNCTION_ID = H.FUNCTION_ID AND H.BOUNDARY_ID = A.PARTITION_NUMBER
LEFT JOIN (SELECT DISTINCT DATA_SPACE_ID, LEFT(PHYSICAL_NAME, 1) LOCATION FROM SYS.DATABASE_FILES) I ON I.DATA_SPACE_ID = ISNULL(F.DATA_SPACE_ID, D.DATA_SPACE_ID)
LEFT JOIN SYS.DM_DB_PARTITION_STATS J ON J.OBJECT_ID = A.OBJECT_ID AND J.INDEX_ID = A.INDEX_ID AND J.PARTITION_NUMBER = A.PARTITION_NUMBER
ORDER BY 1, 2, 3, A.PARTITION_NUMBER

All I had to do was drop the two indices referencing the Partition Scheme then that allowed me to drop the Partition Scheme, then Partition Function.

Answer 2

Taking the SSMS UI route (rather than figuring out all the DDL script), R-click the partitioned table in the Object Explorer, Design, R-click design area, Indexes, select each partitioned index, expand Data Space Specification, select Data Space Type dropdown and select "Filegroup." Your index will be off the partition and back on PRIMARY.

However, you're not done. Hit F4 to bring up table properties on the right, and do the same process. Remember to Save when you're done. Freedom!