SQL Server 2005 changes a table that already has a large amount of data to a partitioned table

First, the partition table introduction:

The primary purpose of using partitioned tables is to improve the scalability and manageability of large tables and tables with various access patterns.
Partitioning can divide the data into smaller, more manageable parts that play a role in improving performance, and on the other hand, for systems with multiple CPUs, partitioning can be done in parallel to the operation of the table, which is very helpful for improving performance.

Second, create the steps:

There is a table L_testresult a total of 30 million records, the main purpose of this article is to partition the table by time, in order to improve the efficiency of the table operation.

1. Create a new filegroup, preferably a new filegroup:

ALTER DATABASE listest ADD FILEGROUP [LISFQ]

2. Create a partition function:

CREATE PARTITION FUNCTION monthdaterange (datetime)

As RANGE left for VALUES

(

' 20071231 23:59:59.997 ',

' 20080630 23:59:59.997 ',

' 20081231 23:59:59.997 '

)

3. Create a partition file based on the timephased point of the partition function, preferably with a database file on a different disk, to increase the read and write speed of the disk:

ALTER DATABASE Listest

ADD FILE

(NAME = n ' lis200712 ', FILENAME = N ' f:/data/lis200712.ndf ', SIZE = 5mb,maxsize = Unlimited,filegrowth = 5MB)

To FILEGROUP [LISFQ]

ALTER DATABASE Listest

ADD FILE

(NAME = n ' lis200806 ', FILENAME = N ' f:/data/lis200806.ndf ', SIZE = 5mb,maxsize = Unlimited,filegrowth = 5MB)

To FILEGROUP [LISFQ]

ALTER DATABASE Listest

ADD FILE

(NAME = n ' lis200812 ', FILENAME = N ' f:/data/lis200812.ndf ', SIZE = 5mb,maxsize = Unlimited,filegrowth = 5MB)

To FILEGROUP [LISFQ]

4. Create the partition schema and apply the partition function to the schema:

CREATE PARTITION SCHEME Monthdaterangescheme

As

PARTITION Monthdaterange

All to ([LISFQ])

The partition functions you have created and the partition schema are as follows:

5, the existing data table is applied to the partition scheme, you can delete the partition field of the index of the way to do, as in this example, I use Measuretime to partition the table:

--Delete the index on the partition field

Drop Index L_testresult. Idx_l_testresult_measuretime

--Create an index by partition scheme

CREATE INDEX Idx_l_testresult_measuretime

On L_testresult (Measuretime)

On Monthdaterangescheme (Measuretime)

6. For a clustered index in a partitioned table, it is best to remove the rebuild:

--Rebuilding the main building of clustered index

ALTER TABLE L_testresult

DROP CONSTRAINT Pk_l_testresult

ALTER TABLE L_testresult

ADD CONSTRAINT Pk_l_testresult

PRIMARY KEY CLUSTERED (sampleno,testid,sampletype,editstatus)

Third, query partition information:

1. Query the table is not partition success:

SELECT * from SYS. partitions where object_id = object_id (' L_testresult '), the results are as follows:

2, query for a period of time the data in which partition:

SELECT *, $PARTITION. Monthdaterange (measuretime) from L_testresult where measuretime>= ' 2009-01-01 ', results such as:

SQL Server 2005 changes a table that already has a large amount of data to a partitioned table