none
partition scheme

    Question

  • i created partition function like this-

    create partition function  pforderdate(datetime) as range right for values('2002-01-01','2003-01-01','2004-01-01','2005-01-01')

    then i created partition scheme -

    create partition scheme psorderdate as partition pforderdate to(fg1, fg2, fg3, fg4, fg5)

    BUT,when i execute the partition scheme query an error is shown to me-

    Msg 208, Level 16, State 58, Line 1
    Invalid object name 'fg1'.

    why this error is coming and please tell me how to resolve it?

    Sunday, June 23, 2013 7:32 AM

Answers

  • You need to add/create a file group

    USE dbname;
    SET NOCOUNT ON;
    GO
    ALTER DATABASE dbname ADD FILEGROUP FG1;
    ALTER DATABASE dbname ADD FILEGROUP FG2;
    ALTER DATABASE dbname ADD FILEGROUP FG3;
    ALTER DATABASE dbname ADD FILEGROUP FG4;

    GO
    ALTER DATABASE dbname  ADD FILE (Name = F1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F1.ndf', SIZE = 16MB) TO FILEGROUP FG1;
    ALTER DATABASE dbname  ADD FILE (Name = F2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F2.ndf', SIZE = 16MB) TO FILEGROUP FG2;
    ALTER DATABASE dbname  ADD FILE (Name = F3, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F3.ndf', SIZE = 16MB) TO FILEGROUP FG3;
    ALTER DATABASE dbname  ADD FILE (Name = F4, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F4.ndf', SIZE = 16MB) TO FILEGROUP FG4;

    GO
    -- Partition function
    CREATE PARTITION FUNCTION PF (integer)
    AS RANGE RIGHT
    FOR VALUES (100000, 200000, 300000, 400000);
    GO
    -- Partition scheme
    CREATE PARTITION SCHEME PS
    AS PARTITION PF
    TO (FG1, FG2, FG3, FG4);
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 23, 2013 8:32 AM
    Answerer
  • hi

      Before you creating partition function and partition Scheme, must have Separate file group . In your partition scheme fg1,fg2,fg3 and fg4 is the file group name.

      1.Create file group and database  file(primary or secondary file mdf,.ndf)

      2.create partition function- boundary for partition.

      3.create partition scheme- map partition function to file group.

      4.create table or index and map to the partition.

     

    i hope this will help you.

     

    Sunday, June 23, 2013 10:03 AM

All replies

  • You need to add/create a file group

    USE dbname;
    SET NOCOUNT ON;
    GO
    ALTER DATABASE dbname ADD FILEGROUP FG1;
    ALTER DATABASE dbname ADD FILEGROUP FG2;
    ALTER DATABASE dbname ADD FILEGROUP FG3;
    ALTER DATABASE dbname ADD FILEGROUP FG4;

    GO
    ALTER DATABASE dbname  ADD FILE (Name = F1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F1.ndf', SIZE = 16MB) TO FILEGROUP FG1;
    ALTER DATABASE dbname  ADD FILE (Name = F2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F2.ndf', SIZE = 16MB) TO FILEGROUP FG2;
    ALTER DATABASE dbname  ADD FILE (Name = F3, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F3.ndf', SIZE = 16MB) TO FILEGROUP FG3;
    ALTER DATABASE dbname  ADD FILE (Name = F4, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\F4.ndf', SIZE = 16MB) TO FILEGROUP FG4;

    GO
    -- Partition function
    CREATE PARTITION FUNCTION PF (integer)
    AS RANGE RIGHT
    FOR VALUES (100000, 200000, 300000, 400000);
    GO
    -- Partition scheme
    CREATE PARTITION SCHEME PS
    AS PARTITION PF
    TO (FG1, FG2, FG3, FG4);
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 23, 2013 8:32 AM
    Answerer
  • hi

      Before you creating partition function and partition Scheme, must have Separate file group . In your partition scheme fg1,fg2,fg3 and fg4 is the file group name.

      1.Create file group and database  file(primary or secondary file mdf,.ndf)

      2.create partition function- boundary for partition.

      3.create partition scheme- map partition function to file group.

      4.create table or index and map to the partition.

     

    i hope this will help you.

     

    Sunday, June 23, 2013 10:03 AM
  • thank you.......

    Tuesday, June 25, 2013 3:49 AM
  • if our ans is helpful please mark as answer. it will helpful for others also.
    Tuesday, June 25, 2013 4:13 AM