SQL Server 2005 partition table creation instance

--Create a partition function (default is left boundary)
CREATE PARTITION FUNCTION PARTFUNC1 (INT)
As RANGE
For VALUES (1000,2000,3000,4000,5000);

--Create a partition scheme
CREATE PARTITION SCHEME PARTSCHEME1
As PARTITION PARTFUNC1
All to ([PRIMARY]);

--Create a partitioned table
CREATE TABLE PARTTABLE1
(
col1 int,
col2 int,
col3 int
) on partscheme1 (col1);

--Inserting Test records
INSERT into parttable1 values (10,10,10)
INSERT into parttable1 values (999,10,10)
INSERT into parttable1 values (1000,10,10)
INSERT into parttable1 values (2000,10,10)
INSERT into parttable1 values (3000,10,10)
INSERT into parttable1 values (5000,10,10)
INSERT into parttable1 values (6000,10,10)
INSERT into parttable1 values (7000,10,10)
INSERT into parttable1 values (9000,10,10)
INSERT into parttable1 values (993,10,10)
INSERT into parttable1 values (6000,10,10)
INSERT into parttable1 values (5000,10,10)
INSERT into parttable1 values (7000,10,10)
INSERT into parttable1 values (6600,10,10)
INSERT into parttable1 values (8200,10,10)
INSERT into parttable1 values (8900,10,10)
INSERT into parttable1 values (17000,10,10)
INSERT into parttable1 values (61600,10,10)
INSERT into parttable1 values (81200,10,10)
INSERT into parttable1 values (18900,10,10)
INSERT into parttable1 values (10,10,10)
INSERT into parttable1 values (999,10,10)
INSERT into parttable1 values (1000,10,10)
INSERT into parttable1 values (2000,10,10)
INSERT into parttable1 values (3000,10,10)
INSERT into parttable1 values (5000,10,10)
INSERT into parttable1 values (6000,10,10)
INSERT into parttable1 values (7000,10,10)
INSERT into parttable1 values (9000,10,10)
INSERT into parttable1 values (1993,10,10)
INSERT into parttable1 values (16000,10,10)
INSERT into parttable1 values (15000,10,10)
INSERT into parttable1 values (17000,10,10)
INSERT into parttable1 values (16600,10,10)
INSERT into parttable1 values (18200,10,10)
INSERT into parttable1 values (15000,10,10)
INSERT into parttable1 values (17000,10,10)
INSERT into parttable1 values (16000,10,10)
INSERT into parttable1 values (12000,10,10)
INSERT into parttable1 values (11000,10,10)

--View the distribution of the data in the partition
Select O.name,p.partition_number,p.rows
From sys.objects o join sys.partitions p on o.object_id=p.object_id
where o.type= ' u ' and o.name= ' parttable1 ' and p.index_id=0

--See more details
Select $partition. PARTFUNC1 (col1) as partition,
Col1 as [data],col2,col3
From Parttable1
ORDER BY partition

--View the configuration of the partition
Select F.name,f.type_desc,f.fanout,p.boundary_id,p.value
From Sys.partition_functions F joins sys.partition_range_values p on f.function_id=p.function_id

--Merging partitions, merging 1000<x<=2000 and 2000<x<=3000
Alter PARTITION function PARTFUNC1 ()
Merge Range (2000)

--split partition, divide 1000<x<=3000 partition, divide by 2000
Alter PARTITION function PARTFUNC1 ()
Split Range (2000)

--To modify the partition scheme if the filegroup data specified by the partition scheme is less than the partition data in the partition function before splitting the partition
Alter PARTITION scheme PARTSCHEME1
Next used [filegroup_name]

--Archive partition, create an archive table
CREATE TABLE Parttable2
(
col1 int,
col2 int,
col3 int
) on partscheme1 (col1)

--Archiving of partition 1 data
ALTER TABLE Parttable1
Switch Partition 1
to Parttable2 partition 1

--Check that the data is archived
Select $partition. PARTFUNC1 (col1) as partition,* from Parttable1
ORDER BY partition
--where $partition. PARTFUNC1 (col1) =1

SELECT * FROM Parttable2

--Reload archived data
ALTER TABLE Parttable2
Switch Partition 1
to PARTTABLE1 partition 1

--What if the data from a non-partitioned table is transferred to a partitioned table?