SQL Server Partitioning Guide

partitioningsql server

I am trying to create partitions on a table of one of my test databases called WingTipToys2012.

Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and FgThree, as follows: 

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgTwo;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgThree;

Then, I created one additional file for each file group, as follows:

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file1', 
  filename='C:\...\file1.ndf', size=5MB) TO FILEGROUP FgOne;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file2', 
  filename='C:\...\file2.ndf', size=5MB) TO FILEGROUP FgTwo;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file3', 
  filename='C:\...\file3.ndf', size=5MB) TO FILEGROUP FgThree;

Then I created the partition function, as follows: 

CREATE PARTITION FUNCTION WTPartFunction (int)
AS RANGE LEFT FOR VALUES(30, 60);

The problem comes when trying to create the partition scheme. To do so, I execute the following command: 

CREATE PARTITION SCHEME WTPartScheme
AS PARTITION WTPartFunction TO (FgOne, FgTwo, FgThree);

But after executing the instructions above, I get the following error:

Msg 208, Level 16, State 58, Line 1

Invalid object name 'FgOne'.

It looks like the file group FgOne is not recognized, but I know it's there. Funny enough, SQL server also knows it is there. If I try to create another file group with the same name:

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;

I get the following error:

Msg 5035, Level 16, State 1, Line 1
Filegroup 'FgOne' already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.

I am not sure what I am doing wrong. Can anyone help?

Best Answer

I think you are running this in the context of master, when you are creating partitions you need to be in the context of the database, before you create the function and scheme do USE[database_name].

If you check master you will see your partition function is there, not in the database you think it is.

Related Question