3

I have created a partition function but I am not able to apply it to a table. I am not sure where I am going wrong.

Here is my partition function:

     CREATE PARTITION FUNCTION StaticDateMonthPartition (int)
     AS RANGE left
     FOR VALUES     (   
                    20120301,
                    20120401,
                    20120501,
                    20120601,
                    20120701,
                    20120801,
                    20120901,
                    20121001,
                    20121101,
                    20121201,
                    20130101,
                    20130201
                    )

trying to apply to this table:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partition_OLAP_Fact_vvv]') AND type in (N'U'))
    DROP TABLE [dbo].[partition_OLAP_Fact_vvv]
    GO

    CREATE TABLE [dbo].[partition_OLAP_Fact_vvv]
    (
        FFFFactvvvId            bigint,
        CORStaticDateId         int,
        CORVersionvvvId         bigint,
        vvvCount                tinyint,
        UPB                     decimal(18, 2)
    ) ON  CORStaticDateMonthPartition ([CORStaticDateId])

But when I try to execute the table script I get this error:

    Invalid partition scheme 'CORStaticDateMonthPartition' specified

Please Help.


Reposting my code with steps

Pinal's tutoral is great! Here's a quick summary

  1. Add file groups for each of your partitions

    Alter Database [database]   Add FileGroup partition_201207
    
  2. Create Partition Function

    CREATE PARTITION FUNCTION Partition_Range_CORStaticMonth(int)
    AS RANGE left
    FOR VALUES (20120301)
    
  3. Create Partition Scheme

    CREATE PARTITION SCHEME Partition_Scheme_CORStaticMonth
    AS PARTITION Partition_Range_CORStaticMonth
    TO (FFF_Fact_vvv_201203)
    
  4. Add Files to the File Groups

    ALTER DATABASE [database] 
    ADD FILE( 
            NAME = N'FFF_Fact_vvv_201203', 
            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FFF_Fact_vvv_201203.ndf' , 
            SIZE = 2048KB , 
            FILEGROWTH = 1024KB 
            ) 
    TO FILEGROUP [FFF_Fact_vvv_201203]
    
  5. Build Table with Partition Scheme applied

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partition_Table]') AND type in (N'U'))
    DROP TABLE [dbo].[partition_Table]
    GO
    
    CREATE TABLE [dbo].[partition_Table]
    (
        CORStaticDateId         int
    ) ON  Partition_Scheme_CORStaticMonth ([CORStaticDateId])
    
4
  • Why are you storing dates as ints rather than, hmm, say dates? Commented May 15, 2012 at 13:27
  • 1
    it's a fact table that feeds into our SSAS cube. The true date exists in the date dimension Commented May 15, 2012 at 15:04
  • perfect approach to store dates as integers on a DW Commented May 15, 2012 at 19:34
  • I was lucky enough to inherit that Commented May 17, 2012 at 3:31

2 Answers 2

4

you need a partition scheme to apply to a table.

The order is:

1) Create your Filegroups

2) Create your partition Function

3) Attach Partition Scheme to FileGroups (using the partition Function)

4) Create table on partition Scheme

Check this link for a tutorial

Sign up to request clarification or add additional context in comments.

1 Comment

worked Great, Steps are easy: I'll post my code with the answer
0

Is this just a naming issue, shouldn't:

) ON  CORStaticDateMonthPartition ([CORStaticDateId])

be

) ON  StaticDateMonthPartition ([CORStaticDateId])

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.