SQL Server partition

1. Create a partition

Partitioning steps: 1. Create a partition function 2. Create a partition Schema 3. Create a partitioned index (clustered)

--1. create partition function
DECLARE @dt datetime
SET @dt = ' 20030901 '
Create PARTITION function pf_hist Ory (datetime)
as RANGE right
for VALUES (
    @dt,
    DATEADD (Month, 1, @dt ), DATEADD (month, 2, @dt), DATEADD (month, 3, @dt),
    DATEADD (month, 4, @dt), DATEADD (month, 5, @dt), DATEADD (month, 6, @dt),
    DATEADD (month, 7, @dt), DATEADD (month, 8, @dt), DATEADD (month, 9, @dt),
    DATEADD (month, ten, @dt), DATEADD (month, one, @dt), DATEADD (month, @dt))
GO
--2. Creating a Partition Schema
CREATE PARTITION SCHEME ps_history
as PARTITION pf_history
to ([PRIMARY], [PRIMARY],
    [ PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY],
    [ PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY])
GO

--3. Create a partitioned index (clustered)
-----1) Delete an existing clustered index
-----2) Create a partitioned clustered index
CREATE CLUSTERED INDEX Ct_sale on Sale ([Saletime])
On partition scheme name ([Saletime])

Query statements

--Query

--the partition where the data is queried
Select $PARTITION. Pf_t_service_log (' 2000-06-30 17:13:57.883 ') as partition number
--Count the total number of records in all partition tables
Select $PARTITION. The Pf_t_service_log (tm) as partition number, COUNT (ID) as record number from the T_service_log group by $PARTITION. Pf_t_service_log (tm)
--Query the record under the specified partition number
SELECT * from T_service_log where $PARTITION. Pf_t_service_log (tm) =2

Profile: http://blog.csdn.net/smallfools/article/details/4934119

* * Convert a partitioned table to a normal table: http://blog.csdn.net/smallfools/article/details/5004100

Dynamically generated partition script: http://www.cnblogs.com/gaizai/archive/2011/01/14/1935579.html

Http://www.cnblogs.com/knowledgesea/p/3696912.html

SQL Server partition