ALTER TABLE… Przełączanie ze zwykłej tabeli na tabelę podzieloną na partycje kończy się niepowodzeniem

Poniższy kod wykonuje następujące czynności:

1. Tworzy bazę danych partycja play w C: \ TEMP
2. Tworzy dwie identyczne partycjonowane tabele play_table i archive_play_table
3. Przełącza partycję 1 tabeli odtwarzania do partycji 1 tabeli odtwarzania
4. Tworzy nową niepartycjonowaną tabelę temp_table o tej samej strukturze co play_table w tej samej grupie plików co partycja 2 play_table
5. Przełącza partycję play_table_partition 2 na temp_table

6. Próbuje przełączyć temp_table z powrotem na partycję play_table 2 i kończy się niepowodzeniem

   Msg 4982, poziom 16, stan 1, wiersz 64 ALTER TABLE SWITCH instrukcja nie powiodła się. Sprawdź ograniczenia tabeli źródłowej „play_partition.dbo.temp_table” dopuszczaj wartości, które nie są dozwolone przez zakres zdefiniowany przez partycję 2 w tabeli docelowej „play_partition.dbo.play_table”.

Dlaczego to się nie udaje?

Używam SQL Server 2014 (wersja Enterprise Edition Trial).

Pozdrowienia,

Colin Daley

http://www.colindaley.com/translator

/* Playing with partitioned tables */

USE master;
GO

DROP DATABASE play_partition;
GO

CREATE DATABASE play_partition
    ON PRIMARY(
        NAME = play_partition
        , FILENAME = 'C:\TEMP\play_partition.mdf')
    ,FILEGROUP play_fg1(
        NAME = play_fg1
        ,FILENAME = 'C:\TEMP\play_fg1f1.ndf')
    ,FILEGROUP play_fg2(
        NAME = play_fg2f1
        ,FILENAME = 'C:\TEMP\play_fg2f1.ndf');
GO

USE play_partition;


CREATE PARTITION FUNCTION play_range(INT)
    AS RANGE LEFT FOR VALUES(3);

-- Partition scheme
CREATE PARTITION SCHEME play_scheme 
    AS PARTITION play_range TO (play_fg1, play_fg2);

-- Partitioned tables
CREATE TABLE dbo.play_table(
    c1 INT NOT NULL CONSTRAINT PK_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

CREATE TABLE dbo.archive_play_table(
c1 INT NOT NULL CONSTRAINT PK_archive_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

-- partition 1 = {1, 2, 3}, partiion 2 = {4, 5, 6}
INSERT INTO dbo.play_table(c1) VALUES (1), (2),  (3), (4), (5), (6);

-- move partition 1 from play_table to archive play_table
ALTER TABLE dbo.play_table
    SWITCH PARTITION 1 to dbo.archive_play_table PARTITION 1;

-- create empty table with same structure as dbo.play_table
SELECT * INTO dbo.temp_table FROM dbo.play_table WHERE 1 = 0;

-- move temp_table to filegroup play_fg2
ALTER TABLE dbo.temp_table
    ADD CONSTRAINT PK_temp_table_c1 PRIMARY KEY CLUSTERED(c1) ON play_fg2;

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

-- move data back to partitioned play_table from unpartitioned temp_table
-- FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';


SELECT 'archive_play_table' as table_name, t1.c1
    FROM dbo.archive_play_table AS t1
    UNION ALL
    SELECT 'temp_table' AS table_name, t1.c1
        FROM dbo.temp_table as t1
    ORDER BY 1, 2;

Podczas pracy z przełączaniem partycji SQL Server będzie musiał sprawdzić, czy granice tabeli źródłowej / partycji mieszczą się w granicach tabeli docelowej / partycji. Innymi słowy, próbujesz przełączyć dane z dbo.temp_tablena dbo.play_tablepartycję 2. Pomyśl o tym w ten sposób, dane dla c1in dbo.temp_tablesą ograniczone tylko przez typ danych ( int), więc możesz mieć wartości od -14 147 483 648 do 2 147 483 647 . Ale odwrotnie, miejsce docelowe ( dbo.play_tablepartycja 2) ma zakres od 4 do 2 147 483 647.

Twoje dane nie naruszają tego, ale nie pozwalają na to metadane. Równie łatwo możesz wstawić wartość -10 do dbo.temp_table. Przełączanie partycji nie powiedzie się w ten sam sposób i ma większy sens, ponieważ -10 nie mieści się w dbo.play_tablegranicach drugiej partycji.

Jeśli chcesz, aby ten kod działał, musisz wyraźnie powiedzieć SQL Serverowi, że dbo.temp_tablenigdy nie będzie zawierał żadnych danych, które nie zmieściłyby się na dbo.play_tabledrugiej partycji. Możesz to zrobić z ograniczeniem sprawdzania:

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

/******************************************************************************
    added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
    end of added code
******************************************************************************/

-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

Powyższy przykładowy dodatek do kodu sprawia, że ​​jest to działające rozwiązanie. Teraz SQL Server wie, że dane dbo.temp_tablemogą zmieścić się na partycji 2 z dbo.play_tablepowodu dodanego ograniczenia sprawdzania dbo.temp_table.