Below, in the SQL SERVER 2005 table partition, how to partition the existing table with data, in fact, before the http://www.cnblogs.com/jackyrong/archive/2006/11/13/ 559354.html said, just exchange the order, the following is still illustrated by examples:
Still set up 4 folders in the DATA2 directory in C disk to make 4 filegroups and then build the database
Use master
IF EXISTS (SELECT name from sys.databases WHERE name = N ' Data Partition DB3 ')
DROP DATABASE [Data Partition DB3]
Go
CREATE DATABASE [Data Partition DB3]
On PRIMARY
(name= ' Data Partition DB Primary FG3 ',
Filename=
' C:\Data2\Primary\Data Partition DB Primary fg3.mdf ',
Size=5,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG1]
(NAME = ' Data Partition DB3 FG1 ',
FILENAME =
' C:\Data2\FG1\Data Partition DB3 fg1.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG2]
(NAME = ' Data Partition DB3 FG2 ',
FILENAME =
' C:\Data2\FG2\Data Partition DB3 fg2.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG3]
(NAME = ' Data Partition DB3 FG3 ',
FILENAME =
' C:\Data2\FG3\Data Partition DB3 fg3.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG4]
(NAME = ' Data Partition DB3 FG4 ',
FILENAME =
' C:\Data2\FG4\Data Partition DB3 fg4.ndf ',
SIZE = 5MB,
MAXSIZE=500,
filegrowth=1)
Then create a data table:
Use [Data Partition DB3]
Go
CREATE TABLE MyTable
(ID INT not NULL,
Date DATETIME,
Cost $) on [primary]
and establish an index
Use [Data Partition DB3]
Go
CREATE UNIQUE CLUSTERED INDEX MYTABLE_IXC
On MyTable (ID) on [PRIMARY]
Next, add the data to the list.
Use [Data Partition DB3]
Go
DECLARE @count int
Set @count =-25
While @count <=100
Begin
Insert INTO MyTable select @count, GETDATE (), 100.00
Set @count = @count +1
End
Set @count =101
While @count <=200
Begin
Insert INTO MyTable select @count, GETDATE (), 200.00
Set @count = @count +1
End
Set @count =201
While @count <=300
Begin
Insert INTO MyTable select @count, GETDATE (), 300.00
Set @count = @count +1
End
Set @count =301
While @count <=400
Begin
Insert INTO MyTable select @count, GETDATE (), 400.00
Set @count = @count +1
End
Set @count =401
While @count <=800
Begin
Insert INTO MyTable select @count, GETDATE (), 500.00
Set @count = @count +1
End
At this point, you can see that the data is in one table select * from sys.partitions where object_name (object_id) = ' MyTable '
We then set up the table partitioning function use [Data Partition DB3]
Go
CREATE PARTITION FUNCTION [Data PARTITION Range] (int)
As RANGE left for VALUES (100,200,300)
This shows that the principle of partitioning is four partitions, from negative to 100,101-200,201-300, greater than 300
Of course, if you use right for values, it's from negative to 99,100 to 199,200-299, and greater than 300
Finally, the table partitioning function is applied to the file group.
Use [Data Partition DB3]
Go
CREATE PARTITION Scheme [Data PARTITION scheme]
As PARTITION [Data PARTITION Range]
to ([Data Partition DB3 FG1] [data Partition DB3 FG2], [data Partition DB3 fg3],[data Partition DB3]);
Move the table that was originally built into this table partition
Drop index MYTABLE_IXC on MyTable with (move to [Data Partition Scheme] (ID))
Finally take a look at select * from Sys.partitions where object_name (object_id) = ' MyTable '
As you can see, the original table data was correctly split into four filegroups to implement the table partition
Http://www.cnblogs.com/jackyrong/archive/2006/11/16/562514.html