Partitioning Error

  • USE Master;

    GO

    --- Step 1 : Create New Test Database with two different filegroups.

    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionDB')

    DROP DATABASE PartitionDB;

    GO

    CREATE DATABASE PartitionDB ON PRIMARY

    (NAME='PartitionDB_Part1',FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part1.mdf',SIZE=3, MAXSIZE=100,FILEGROWTH=1 );

    GO

    ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part2

    GO

    ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part3

    GO

    ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part4

    GO

    -------------------------------------------------------------------------------

    alter database PartitionDB

    add file (NAME = 'PartitionDB_Part2',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part2.ndf',

    SIZE =3,MAXSIZE=100,FILEGROWTH=1 ) to filegroup PartitionDB_Part2;

    alter database PartitionDB

    add file (NAME = 'PartitionDB_Part3',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part3.ndf',

    SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part3;

    GO

    alter database PartitionDB

    add file (NAME = 'PartitionDB_Part4',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part4.ndf',

    SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part4;

    GO

    USE PartitionDB;

    GO

    --- Step 2 : Create Partition Range Function

    CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)

    AS RANGE LEFT FOR VALUES (500,1000,2500);

    GO

    --- Step 3 : Attach Partition Scheme to FileGroups

    CREATE PARTITION SCHEME PartitionDB_PartitionScheme

    AS PARTITION PartitionDB_PartitionRange TO ([PRIMARY],[PartitionDB_Part2],[PartitionDB_Part3],[PartitionDB_Part4]);

    GO

    --- Step 4 : Create Table with Partition Key and Partition Scheme

    CREATE TABLE TestTable

    (

    ID INT identity

    ,Date DATETIME

    ) ON PartitionDB_PartitionScheme (ID);

    GO

    --- Step 5 : (Optional/Recommended) Create Index on Partitioned Table

    CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON TestTable(ID)

    ON PartitionDB_PartitionScheme (ID);

    GO

    declare @count int

    set @count =1

    While (@count < = 5000)

    begin

    INSERT INTO TestTable (Date) VALUES (GETDATE());

    set @count = @count + 1

    end

    GO

    --- Step 7 : Verify Rows Inserted in Partitions

    SELECT *

    FROM sys.partitions

    WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

    GO

    CREATE TABLE TestTable_Archive

    (

    ID INT identity

    ,Date DATETIME

    )

    GO

    declare @count int

    set @count =1

    While (@count < = 40)

    begin

    INSERT INTO TestTable_Archive (Date) VALUES (GETDATE());

    set @count = @count + 1

    end

    GO

    CREATE UNIQUE CLUSTERED INDEX IX_TestTable_Archive ON TestTable_Archive(ID)

    GO

    Query

    -- make the switch

    ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1;

    GO

    Error

    Msg 4982, Level 16, State 1, Line 1

    ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values

    that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.

    HOw can i identify that what partition1 range is ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • You specified the partition ranges yourself.

    CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)

    AS RANGE LEFT FOR VALUES (500,1000,2500);

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/20/2012)


    You specified the partition ranges yourself.

    CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)

    AS RANGE LEFT FOR VALUES (500,1000,2500);

    So here partition 1 means "Less than 500" ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (12/20/2012)


    GilaMonster (12/20/2012)


    You specified the partition ranges yourself.

    CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)

    AS RANGE LEFT FOR VALUES (500,1000,2500);

    So here partition 1 means "Less than 500" ?

    That's what your partition function says.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/21/2012)


    That's what your partition function says.

    what steps i followed .

    1 I creaed a table TestTable_Archive (NOn partitioned table )

    2) then i moved data from testtable by ALTER TABLE TestTable SWITCH PARTITION 1 TO TestTable_Archive ;

    3) then i populated the table TestTable_Archive

    declare @count int

    set @count =31

    While (@count < = 40)

    begin

    INSERT INTO TestTable_Archive (id,Date) VALUES (@count,GETDATE());

    set @count = @count + 1

    end

    GO

    4) then i tried ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1;

    but failed with error

    Msg 4982, Level 16, State 1, Line 1

    ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values

    that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.

    Can you please explain why ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (12/21/2012)


    Can you please explain why ?

    What does the error message say you are missing?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/21/2012)


    What does the error message say you are missing?

    what i can get from error is testtable_archive data is "falling out of range" from partition 1 but actually this table contains data range 31 to 40 (basicaly less then 500) ? then y its failing or i am overlooking something here ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Look at the error message. Read what it says. It says nothing at all about the data being out of range.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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