partition table in SQL Server 2005 (iii): Converting a normal table to a component area table

partition table in SQL Server 2005 (iii): Converting a normal table to a component area tableCategory: SQL Server2009-12-03 17:01 9709 People read Comments (6) favorite reports SQL Serverinserttablenullsql Database

In the design of the database, often do not take into account the problem of table partitioning, often in the burden of the data table load is getting heavier, will take into account the partitioning method, at this time, it is related to how to convert the ordinary table to the problem of the Component table.

So, how do you convert a normal table into a partitioned table? In the final analysis, simply create a clustered index on the table and use the partitioning scheme on the clustered index.

However, this is simple to say, it is a bit more complicated to do. In the example above, we first use the following SQL statement to delete the original sale table.

[C-sharp]View Plaincopy

1. --Delete the original data sheet
2. drop table Sale

Then use the following SQL statement to create a new normal table and insert some data in this table.

[C-sharp]View Plaincopy

2. --Create a new normal data table
4. CREATE TABLE Sale (
6. [Id] [int] IDENTITY (all) not NULL,-- autogrow
8. [Name] [varchar] (+) Not NULL,
10. [Saletime] [DateTime] Not NULL,
12. CONSTRAINT [Pk_sale] PRIMARY key CLUSTERED--Create PRIMARY key
14. (
16. [Id] ASC
18. )
20. )
22. --Insert Some records
24. Insert Sale ([name],[saletime]) VALUES (' Zhang San ',' 2009-1-1 ')
26. Insert Sale ([name],[saletime]) VALUES (' John Doe ',' 2009-2-1 ')
28. Insert Sale ([name],[saletime]) VALUES (' Harry ',' 2009-3-1 ')
30. Insert Sale ([name],[saletime]) VALUES (' Money Six ',' 2010-4-1 ')
32. Insert Sale ([name],[saletime]) VALUES (' Zhao Qi ',' 2010-5-1 ')
34. Insert Sale ([name],[saletime]) VALUES (' Zhang San ',' 2011-6-1 ')
36. Insert Sale ([name],[saletime]) VALUES (' John Doe ',' 2011-7-1 ')
38. Insert Sale ([name],[saletime]) VALUES (' Harry ',' 2011-8-1 ')
40. Insert Sale ([name],[saletime]) VALUES (' Money Six ',' 2012-9-1 ')
42. Insert Sale ([name],[saletime]) VALUES (' Zhao Qi ',' 2012-10-1 ')
44. Insert Sale ([name],[saletime]) VALUES (' Zhang San ',' 2012-11-1 ')
46. Insert Sale ([name],[saletime]) VALUES (' John Doe ',' 2013-12-1 ')
48. Insert Sale ([name],[saletime]) VALUES (' Harry ',' 2014-12-1 ')

The table created with the above code is a normal table, so let's look at the properties of the table, as shown in.

In the above code, we can see that this table has the characteristics of the general table--there is a primary key, and the primary key is also a clustered index. As mentioned earlier, a partitioned table is a partition condition for a field, so you cannot create a clustered index other than this field. Therefore, to convert a normal table to a constituent table, you must first delete the clustered index and then create a new clustered index that uses the partitioning scheme in the clustered index.

Unfortunately, in SQL Server, if a field is both a primary key and a clustered index, you cannot simply delete the clustered index. Therefore, we can only delete the entire primary key and recreate a primary key, but do not set it as a clustered index when creating the primary key, as shown in the following code:

[C-sharp]View Plaincopy

1. --Remove the primary key
2. ALTER TABLE Sale DROP constraint Pk_sale
3. --Create a primary key, but not set as a clustered index
4. ALTER TABLE Sale ADD CONSTRAINT pk_sale PRIMARY KEY nonclustered
5. (
6. [ID] ASC
7. ) on [PRIMARY]

After you re-nonclustered the primary key, you can create a new clustered index for the table and use the partitioning scheme in the clustered index, as shown in the following code:

[C-sharp]View Plaincopy

1. --Create a new clustered index that uses the partitioning scheme in the clustered index
2. CREATE CLUSTERED INDEX Ct_sale on Sale ([Saletime])
3. On Partschsale ([Saletime])

After creating a clustered index that uses a partitioning scheme for the table, the table becomes a partitioned table to see its properties, as shown in.

Once again, we can use the following code to see the number of records in each partition table.

[C-sharp]View Plaincopy

1. --Count the total number of records in all partition tables
2. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)

The result of running the above code is as follows: After converting the normal table to the component area table, the data is not lost and is automatically placed in the partition table it should be in.

Original is not easy, reproduced please indicate the source. Http://blog.csdn.net/smallfools/archive/2009/12/03/4934119.aspx

partition table in SQL Server 2005 (iii): Converting a normal table to a component area table