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
Add file groups for each of your partitions
Alter Database [database] Add FileGroup partition_201207Create Partition Function
CREATE PARTITION FUNCTION Partition_Range_CORStaticMonth(int) AS RANGE left FOR VALUES (20120301)Create Partition Scheme
CREATE PARTITION SCHEME Partition_Scheme_CORStaticMonth AS PARTITION Partition_Range_CORStaticMonth TO (FFF_Fact_vvv_201203)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]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])
ints rather than, hmm, saydates?