partition table in SQL Server 2005 (vi): Converting a partitioned table to a normal table

In the previous section, we discussed how to create a partitioned table directly and how to convert a normal table into a partitioned table. So what's the difference between these two ways of creating a table? Now, I've created two more tables in a new way:

The first table is named Sale, which uses the partition table in SQL Server 2005 (a): What is a partitioned table? Why use partitioned tables? How do I create a partitioned table? is created, and a primary key is added to the table after it has been created.

The second table name is Sale1, which uses the partition table in SQL Server 2005 (iii): Create a method in the normal Table conversion Component table, that is, create a normal table first, and then convert the normal table to a partitioned table by adding a clustered index to the normal table.

The above method can get a partitioned table, but the two partitioned tables are still a bit different, where is the difference? Let's look at the indexes and primary keys for both tables, as shown in.

As can be seen, the directly created partition Table sale index, there is only one index named Pk_sale, this index is a unique, nonclustered index, that is, when the Pk_sale primary key created by SQL Server automatically created index. A clustered index named Ct_sale1 exists in the index of the Sale1 of the normal table Conversion component Area table, in addition to the unique, nonclustered index named Pk_sale1, which is automatically created by SQL Server when the primary key is created.

For table sale, you can modify the partition function to convert it to a normal table, the specific way to modify the table in the SQL Server 2005 partition tables (iv): Delete (merge) a partition, in fact, is to remove all partition boundaries in the partition function, then, All data in this partitioned table can only exist in the first partition table. In this example, you can use the following code to modify the partition function.

[C-sharp]View Plaincopy

1. ALTER PARTITION FUNCTION Partfunsale ()
2. MERGE RANGE (' 20100101 ')
3. ALTER PARTITION FUNCTION Partfunsale ()
4. MERGE RANGE (' 20110101 ')
5. ALTER PARTITION FUNCTION Partfunsale ()
6. MERGE RANGE (' 20120101 ')
7. ALTER PARTITION FUNCTION Partfunsale ()
8. MERGE RANGE (' 20130101 ')

In fact, after this operation, the table sale is still a partitioned table, as shown, only a partition of the partition, which is no different from the universal table.

For partitioned tables that convert a normal table to a partition table by creating a partitioned index, the partition table can be converted to a normal table by deleting the partitioned index, in addition to the methods above. However, the following two steps are required:

1. Delete the partition index

2. Rebuild an index on the original index field.

First say delete partition index, this step is simple, you can delete the partition index directly in SQL Server Management Studio, or you can use the SQL statement to delete, as in this example, you can use the following code to delete the partition index that has already been created.

[C-sharp]View Plaincopy

1. Drop INDEX Sale1.ct_sale1

At first, I thought that as soon as the partition index was deleted, the partition table would automatically be converted to a normal table, but after the index was dropped, the table's properties were checked, and the result was a partitioned table, as shown in.

Not only that, but it is not possible to change the original clustered unique index (the index in this case, the primary key) to a clustered index, as shown in.

If you want to solve this problem completely, you must also re-create the index on the field where the partition index was originally created, and only after re-creating the index can SQL Server convert the partitioned table to a normal table. In this example, you can re-create the index using the following code.

[C-sharp]View Plaincopy

1. CREATE CLUSTERED INDEX ct_sale1 on Sale1 ([Saletime])
2. On [PRIMARY]
3. Go

After rebuilding the index, the partition table becomes the normal table, and now look at the properties of the Sale1 table, we can see that the original partition table has become a normal table, as shown in.

Of course, the above two steps can also be synthesized in one step, that is, while rebuilding the index, the original index is deleted. As shown in the following code:

[C-sharp] View plaincopy

1. CREATE CLUSTERED INDEX ct_sale1 on Sale1 ([Saletime])
2. With (drop_existing = ON)
3. On [PRIMARY]

The operation in SQL Server Management Studio is supposed to be the same as using SQL statements, but I deleted the clustered index in SQL Server Management Studio and re-created an index with the same name on that field. and regenerate and organize the index, but the partition table doesn't turn into a regular table, which makes me baffled. However, as long as you can use the SQL statement to achieve the purpose, then we use it.

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

RELATED links:

partition table in SQL Server 2005 (a): What is a partitioned table? Why use partitioned tables? How do I create a partitioned table?

partition table in SQL Server 2005 (ii): How to add, query, and modify data in a partitioned table

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

partition table in SQL Server 2005 (iv): Delete (merge) a partition

partition table in SQL Server 2005 (v): adding a partition

partition table in SQL Server 2005 (vi): Converting a partitioned table to a normal table

partition table in SQL Server 2005 (vi): Converting a partitioned table to a normal table