Creating a data partitioned table, error on creating parttion function

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  July 24, 2018 at 2:58 pm

  #414435

  Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

  Msg 7707, Level 16, State 1, Line 110
  The associated partition function 'myDateRangePF1' generates more partitions than there are file groups mentioned in the scheme 'myDateRangePS1'.

  Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

  Below is the code flow I'm executing

  Thanks

  
  USE ABHS_BI;
  GO

  -- Adds six new filegroups to the ABHS_BI database
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2015fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2016fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2017fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2018fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2019fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2020fg;

  -- Adds one file for each filegroup. for 6 filegroups
  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2015dat1,
    FILENAME = 'G:\\sw2015dat1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
  )
  TO FILEGROUP sw2015fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2016dat2,
    FILENAME = 'G:\\sw2016dat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2016fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2017dat3,
    FILENAME = 'G:\\sw2017dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2017fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2018dat4,
    FILENAME = 'G:\\sw2018dat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2018fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2019dat5,
    FILENAME = 'G:\\sw2019dat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2019fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2020dat6,
    FILENAME = 'G:\\sw2020dat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2020fg;
  GO

  -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

  --DROP PARTITION FUNCTION myDateRangePF1 ;

  CREATE PARTITION FUNCTION myDateRangePF1 (int)
    AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
  GO

  -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
  CREATE PARTITION SCHEME myDateRangePS1 --S
    AS PARTITION myDateRangePF1 --F
    TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
  GO

  -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
  CREATE TABLE [dbo].[BI_Actvt](
  [actvt_ym] [int] null,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Rev](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Usg](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO
  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 24, 2018 at 4:41 pm

  #1998902

  quinn.jay - Tuesday, July 24, 2018 2:58 PM

  Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

  Msg 7707, Level 16, State 1, Line 110
  The associated partition function 'myDateRangePF1' generates more partitions than there are file groups mentioned in the scheme 'myDateRangePS1'.

  Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

  Below is the code flow I'm executing

  Thanks

  
  USE ABHS_BI;
  GO

  -- Adds six new filegroups to the ABHS_BI database
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2015fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2016fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2017fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2018fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2019fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2020fg;

  -- Adds one file for each filegroup. for 6 filegroups
  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2015dat1,
    FILENAME = 'G:\\sw2015dat1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
  )
  TO FILEGROUP sw2015fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2016dat2,
    FILENAME = 'G:\\sw2016dat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2016fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2017dat3,
    FILENAME = 'G:\\sw2017dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2017fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2018dat4,
    FILENAME = 'G:\\sw2018dat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2018fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2019dat5,
    FILENAME = 'G:\\sw2019dat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2019fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2020dat6,
    FILENAME = 'G:\\sw2020dat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2020fg;
  GO

  -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

  --DROP PARTITION FUNCTION myDateRangePF1 ;

  CREATE PARTITION FUNCTION myDateRangePF1 (int)
    AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
  GO

  -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
  CREATE PARTITION SCHEME myDateRangePS1 --S
    AS PARTITION myDateRangePF1 --F
    TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
  GO

  -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
  CREATE TABLE [dbo].[BI_Actvt](
  [actvt_ym] [int] null,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Rev](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Usg](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO
  

  Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
  Table Partitioning Basics

  Sue

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  July 25, 2018 at 3:29 pm

  #1999006

  Sue_H - Tuesday, July 24, 2018 4:41 PM

  quinn.jay - Tuesday, July 24, 2018 2:58 PM

  Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

  Msg 7707, Level 16, State 1, Line 110
  The associated partition function 'myDateRangePF1' generates more partitions than there are file groups mentioned in the scheme 'myDateRangePS1'.

  Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

  Below is the code flow I'm executing

  Thanks

  
  USE ABHS_BI;
  GO

  -- Adds six new filegroups to the ABHS_BI database
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2015fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2016fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2017fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2018fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2019fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2020fg;

  -- Adds one file for each filegroup. for 6 filegroups
  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2015dat1,
    FILENAME = 'G:\\sw2015dat1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
  )
  TO FILEGROUP sw2015fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2016dat2,
    FILENAME = 'G:\\sw2016dat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2016fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2017dat3,
    FILENAME = 'G:\\sw2017dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2017fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2018dat4,
    FILENAME = 'G:\\sw2018dat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2018fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2019dat5,
    FILENAME = 'G:\\sw2019dat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2019fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2020dat6,
    FILENAME = 'G:\\sw2020dat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2020fg;
  GO

  -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

  --DROP PARTITION FUNCTION myDateRangePF1 ;

  CREATE PARTITION FUNCTION myDateRangePF1 (int)
    AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
  GO

  -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
  CREATE PARTITION SCHEME myDateRangePS1 --S
    AS PARTITION myDateRangePF1 --F
    TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
  GO

  -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
  CREATE TABLE [dbo].[BI_Actvt](
  [actvt_ym] [int] null,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Rev](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Usg](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO
  

  Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
  Table Partitioning Basics

  Sue

  Thanks, this helped a lot. I now have other issues, but its separate from this so will make a new post.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 26, 2018 at 6:12 pm

  #1999183

  quinn.jay - Wednesday, July 25, 2018 3:29 PM

  Sue_H - Tuesday, July 24, 2018 4:41 PM

  quinn.jay - Tuesday, July 24, 2018 2:58 PM

  Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

  Msg 7707, Level 16, State 1, Line 110
  The associated partition function 'myDateRangePF1' generates more partitions than there are file groups mentioned in the scheme 'myDateRangePS1'.

  Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

  Below is the code flow I'm executing

  Thanks

  
  USE ABHS_BI;
  GO

  -- Adds six new filegroups to the ABHS_BI database
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2015fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2016fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2017fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2018fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2019fg;
  GO
  ALTER DATABASE ABHS_BI
  ADD FILEGROUP sw2020fg;

  -- Adds one file for each filegroup. for 6 filegroups
  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2015dat1,
    FILENAME = 'G:\\sw2015dat1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
  )
  TO FILEGROUP sw2015fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2016dat2,
    FILENAME = 'G:\\sw2016dat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2016fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2017dat3,
    FILENAME = 'G:\\sw2017dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2017fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2018dat4,
    FILENAME = 'G:\\sw2018dat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2018fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2019dat5,
    FILENAME = 'G:\\sw2019dat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2019fg;
  GO

  ALTER DATABASE ABHS_BI
  ADD FILE
  (
    NAME = sw2020dat6,
    FILENAME = 'G:\\sw2020dat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 250MB,
    FILEGROWTH = 10MB
  )
  TO FILEGROUP sw2020fg;
  GO

  -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

  --DROP PARTITION FUNCTION myDateRangePF1 ;

  CREATE PARTITION FUNCTION myDateRangePF1 (int)
    AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
  GO

  -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
  CREATE PARTITION SCHEME myDateRangePS1 --S
    AS PARTITION myDateRangePF1 --F
    TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
  GO

  -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
  CREATE TABLE [dbo].[BI_Actvt](
  [actvt_ym] [int] null,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Rev](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO

  CREATE TABLE [dbo].[BI_Usg](
  [actvt_ym] [int] NULL,
  [actvt_dt] [date] NULL,
  ...
  ) ON myDateRangePS1 (actvt_ym)
  GO
  

  Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
  Table Partitioning Basics

  Sue

  Thanks, this helped a lot. I now have other issues, but its separate from this so will make a new post.

  Glad it helped - thanks for posting back!
  You'll get through all of these. It's one of those...different issues working through it all and then it comes together and makes sense.

  Sue