Post reply

Partition Switch Problem

  • October 2, 2018 at 5:31 am

    #379679

    Hi everyone!

    I have a problem with Partitioning.
    I am currently evaluating this tiopic on an existing test database, but I struggle with the "data switch out of partition".
    We have one big table with three columns (Uniqueidentifier, varbinary and datetime) and a Clustered Primary Key Constraint on the uniqueidentifier (all on the filegroup called DATA).
    The idea the developers are having is, to partition the data by some kind of "date (around a month in days)" on a integer column.
    So I did the following:
    - added a integer column (called PartInd with a default value of -900)
    - created the PartitionFunction as follows:
    CREATE PARTITION FUNCTION PartFunc (INT)
    AS RANGE LEFT FOR VALUES
    (-900,-31,-30,-29,-28,-27,-26,-25,-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13,-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1, 0, 1);
    GO
    - Added PartitionScheme
    CREATE PARTITION SCHEME PartScheme
    AS PARTITION PartFunc
    ALL TO (DATA);
    - formatted the existing table (dropping constraints, creating new clustered index on the partition column and new constraint for the PK - nonclustered in this case)

    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[meas].[Data]') AND name = N'PK_Data_Id')
    ALTER TABLE [meas].[Data] DROP CONSTRAINT [PK_Data_Id] WITH ( ONLINE = OFF )
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[meas].[Data]') AND name = N'IKC_Data_PartInd')
    CREATE CLUSTERED INDEX [IX_Data_PartInd] ON [meas].[Data]
    (
        [PartInd]
    ) WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) ON PartScheme(PartInd)
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[meas].[Data]') AND name = N'PK_Data_Id')
    ALTER TABLE [meas].[Data] ADD CONSTRAINT [PK_Data_Id] PRIMARY KEY NONCLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
    GO

    - there I was not sure if the nonclustered PK needs to be "on the partition scheme" (put it on the same filegroup)
    - afterwards I created the "temporary table" with the following structure:

    CREATE TABLE [meas].[DataDel](
        [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
        [Raw] [varbinary](max) NULL,
        [CreationDate] [datetime] NULL,
        [PartInd] [int] NOT NULL,
    CONSTRAINT [PK_DataDel_Id] PRIMARY KEY NONCLUSTERED
    (
        [Id] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
    )
    ALTER TABLE [meas].[DataDel] ADD CONSTRAINT [DF_DataDel_Id] DEFAULT (newsequentialid()) FOR [Id]
    GO
    ALTER TABLE [meas].[DataDel] ADD DEFAULT ((-900)) FOR [PartInd]
    GO
    ALTER TABLE [meas].[DataDel] WITH CHECK ADD CONSTRAINT [CK_DataDel_IsNotEmpty] CHECK (([Raw] IS NOT NULL AND datalength([Raw])>(0)))
    GO
    ALTER TABLE [meas].[DataDel] CHECK CONSTRAINT [CK_DataDel_IsNotEmpty]
    GO
    CREATE CLUSTERED INDEX [IX_DataDel_PartInd] ON [meas].[DataDel]
    (
        [PartInd] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO

    - the original one (before my modifications) looked like this

    CREATE TABLE [meas].[Data](
        [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
        [Raw] [varbinary](max) NULL,
        [CreationDate] [datetime] NULL,
    CONSTRAINT [PK_Data_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) ON [DATA]
    )
    ALTER TABLE [meas].[Data] ADD CONSTRAINT [DF_Data_Id] DEFAULT (newsequentialid()) FOR [Id]
    GO
    ALTER TABLE [meas].[Data] WITH CHECK ADD CONSTRAINT [CK_Data_IsNotEmpty] CHECK (([Raw] IS NOT NULL AND datalength([Raw])>(0)))
    GO
    ALTER TABLE [meas].[Data] CHECK CONSTRAINT [CK_Data_IsNotEmpty]
    GO

    - now I should be able to switch (unless I forgot something which I do not see), but it fails:
    ALTER TABLE [meas].[Data] SWITCH PARTITION 7 TO [meas].[DataDel];

    'ALTER TABLE SWITCH' statement failed. The table 'meas.Data' is partitioned while index 'PK_Data_Id' is not partitioned.

    - I also tried to create the temporary table (DataDel) on the PartitionScheme, and switch the data to the temporary table and an existing partition in it, but I got the same error.
    - Do I need to create the Partition on a Key which includes the PK? (Where would be the benefit?)

    And this is where I need your help, to solve the problem for switching out the data to a temporary table.
    Thanks in advance!

  • October 4, 2018 at 11:01 am

    #2008148

    Those twp tables are not identical.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply