Partitioned Graph table - Partition Switch failed

Question

I am trying and partitioning to an existing SQL Server Graph table (node). The table is very large and is taking long time for deletes etc, so hoping to use partitioning to improve the performance.

However, when I add partition and try to SWITCH the data. It fails with the below error:

Msg 7733, Level 16, State 4, Line 1 'ALTER TABLE SWITCH' statement failed. The table tempdatabase.dbo.graphtable is partitioned while index 'GRAPH_UNIQUE_INDEX_xxxxxxxxx' is not partitioned.

As the index GRAPH_UNIQUE_INDEX_xxxxxxxxx is automatically created, not able to add the partition key it.

CREATE PARTITION FUNCTION f_partfunction (INT) AS RANGE LEFT 
FOR VALUES (1,100,200,300)
GO

-- Create the partition scheme
CREATE PARTITION SCHEME s_partscheme
AS PARTITION f_partfunction
ALL TO ([PRIMARY]);
GO


CREATE TABLE [dbo].[graphtable](
    PartitionKey INT,
    ID INT,
    EName varchar(100))
 AS NODE  ON s_partscheme (PartitionKey)
go

CREATE TABLE [dbo].[graphtable_test](
    PartitionKey INT,
    ID INT,
    EName varchar(100))
 go

 --Failing Code
 ALTER TABLE [dbo].[graphtable] SWITCH PARTITION 3 TO [dbo].[graphtable_test]

I don't think you can create an aligned partitioned graph table (required for SWITCH) because the partitioning column (PartitionKey) is not part of the unique index on $node. Partitioning improves manageability but not necessarily performance. Indexes (which I don't see in your question) and query tuning are the first steps to improve performance. — Dan Guzman, Oct 09 '18 at 02:04

score 1 · Answer 1 · edited Oct 09 '18 at 08:09

1

You will have to drop the current index, and then recreate with the index pointing to the partition schema you have.

    CREATE INDEX GRAPH_UNIQUE_INDEX_xxxxxxxxx' ON graphtable
    (
    "Your column"
    )
    INCLUDE (  [Primary Key column]) WITH (DATA_COMPRESSION = PAGE) ON
    [s_partscheme] (PartitionKey)
    GO

In your case l'm guessing its PartitionKey, used as Partition column.

edited Oct 09 '18 at 08:09

Rahim Dastar

1,259
1
9
15

answered Oct 09 '18 at 07:31

TKAY

66
2

The graph_unique_index_xxxxxxx is autocreated with node table. It cannot be dropped or altered. – David Oct 09 '18 at 17:36
Msg 13925, Level 16, State 1, Line 1 Cannot drop or disable index or constraint 'GRAPH_UNIQUE_INDEX_xxxxxxxxxxx' because the last unique index or constraint on '$node_id' cannot be dropped or disabled. – David Oct 09 '18 at 17:44
you dont need to drop the Primary key i.e Clustured index – TKAY Oct 10 '18 at 07:17

Partitioned Graph table - Partition Switch failed

1 Answers1