1

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.

1 Answer 1

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.