Sql-server – Table Partition Switch Problem SQL Server

partitioningsql server

I have

Tables Schema:

CREATE TABLE [dbo].[t1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL,
CONSTRAINT [PK_valores_texto_test_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [dbo].[t2](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL),
CONSTRAINT [PK_valores_texto_switch_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The table t1 has data, meanwhile t2 is empty with the same schema as t1, then I want to partition both tables and switch the new data inserted into t2 to t1.

First, I create the partition function and partition schema.

-- Create the partition function
CREATE PARTITION FUNCTION PF_valores_texto_partition_HASH_BY_VALUE (BIGINT) 
AS RANGE LEFT 
FOR VALUES (2500000, 5000000, 7500000, 10000000, 12500000, 15000000)
GO

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

And finally, I partitioning the tables.

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON 
t1
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON 
t2
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

But when I try to make a switch bettwen tables with this command:

ALTER TABLE t1 SWITCH PARTITION 1 TO t2 PARTITION 1

It returns me this error:

'ALTER TABLE SWITCH' statement failed. The index 'partition_by_id_file' is partitioned while table 't1' is not partitioned.

Best Answer

You've created partitioned indexes but haven't partitioned the underlying table by creating a partitioned clustered index.

Note that the partitioning column must be part of the key for partitioned unique indexes. The implication here is that you need to add the id_file column to the primary key so that the partitioned table is aligned, which is a requirement in order to use SWITCH.

For partitioned non-unique non-clustered indexes, the partitioning column doesn't need to be part of the key. It will be implicitly included as the row locator in the index leaf nodes, just like a clustered index key.

Related Question