Partition a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Partition a table

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-26 : 09:46:24

Hello,
I want to partition a table but am having problems with the partition creation

ALTER DATABASE Temp_DJJ ADD FILEGROUP MyTestFG;
GO
ALTER DATABASE Temp_DJJ ADD FILE (
    name = Test_ReadOnly1, 
    filename = 'C:\SQLData\MyTestFG.ndf', 
    size = 10mb, 
    maxsize = 200mb, 
    filegrowth = 5mb) TO FILEGROUP MyTestFG;
GO
-- Try for two partitions on prior to 6/1/12 and one after that.
CREATE PARTITION FUNCTION TestPF (DATETIME) AS RANGE LEFT FOR VALUES ('6/1/2012');
GO
CREATE PARTITION SCHEME TestIt AS PARTITION TestPF TO ([PRIMARY], MyTestFG);
GO

On the CREATE PARTITION SCHEME I get the error:
Msg 208, Level 16, State 58, Line 1
Invalid object name 'MyTestFG'.

Not to mention I am unclear how to associate the table.

So where did I go wrong?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 13:07:26

You have to create the partition function and scheme in the database - that may (or may not be the problem). Can you try this?:

ALTER DATABASE Temp_DJJ ADD FILEGROUP MyTestFG;
GO
ALTER DATABASE Temp_DJJ ADD FILE (
    name = Test_ReadOnly1, 
    filename = 'C:\Temp\MyTestFG.ndf', 
    size = 10mb, 
    maxsize = 200mb, 
    filegrowth = 5mb) TO FILEGROUP MyTestFG;
GO
USE Temp_DJJ
GO
-- Try for two partitions on prior to 6/1/12 and one after that.
CREATE PARTITION FUNCTION TestPF (DATETIME) AS RANGE LEFT FOR VALUES ('6/1/2012');
GO
CREATE PARTITION SCHEME TestIt AS PARTITION TestPF TO ([PRIMARY], MyTestFG);
GO

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-27 : 07:02:30

I knew it would be something simple. That worked.

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 07:27:08

Great!!

Now I can safely fess up! That was a wild guess based on the first sentence in this MSDN page which reads "Creates a function in the current database that maps..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources