title: Create a Partition Function in SQL
body: <p>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. </p>
  
  <p>Here is my partition function:</p>
  
  <pre><code>     CREATE PARTITION FUNCTION StaticDateMonthPartition (int)
       AS RANGE left
       FOR VALUES     (   
                      20120301,
                      20120401,
                      20120501,
                      20120601,
                      20120701,
                      20120801,
                      20120901,
                      20121001,
                      20121101,
                      20121201,
                      20130101,
                      20130201
                      )
  </code></pre>
  
  <p>trying to apply to this table:</p>
  
  <pre><code>    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])
  </code></pre>
  
  <p>But when I try to execute the table script I get this error:</p>
  
  <pre><code>    Invalid partition scheme 'CORStaticDateMonthPartition' specified
  </code></pre>
  
  <p>Please Help.</p>
  
  <hr>
  
  <p>Reposting my code with steps</p>
  
  <p><a href="http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/" rel="nofollow">Pinal's</a> tutoral is great! Here's a quick summary</p>
  
  <ol>
  <li><p>Add file groups for each of your partitions</p>
  
  <pre><code>Alter Database [database]   Add FileGroup partition_201207
  </code></pre></li>
  <li><p>Create Partition Function</p>
  
  <pre><code>CREATE PARTITION FUNCTION Partition_Range_CORStaticMonth(int)
  AS RANGE left
  FOR VALUES (20120301)
  </code></pre></li>
  <li><p>Create Partition Scheme</p>
  
  <pre><code>CREATE PARTITION SCHEME Partition_Scheme_CORStaticMonth
  AS PARTITION Partition_Range_CORStaticMonth
  TO (FFF_Fact_vvv_201203)
  </code></pre></li>
  <li><p>Add Files to the File Groups</p>
  
  <pre><code>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]
  </code></pre></li>
  <li><p>Build Table with Partition Scheme applied</p>
  
  <pre><code>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])
  </code></pre></li>
  </ol>