Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to convert partitioned tables into normal tables by SQL Server". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how SQL Server converts partitioned tables into normal tables".

Earlier, we showed how to create a partitioned table directly and how to convert a normal table into a partitioned table. So what's the difference between the tables created in these two ways? Now, I have recently created two more tables:

The first table is named Sale, and after creation, a primary key is added to the table.

The second table is named Sale1, which uses a normal table and then converts the normal table to a partitioned table by adding a clustered index to the normal table.

You can get a partitioned table by all of the above methods, but there is still a difference between the two partitioned tables. What is the difference? Let's take a look at the indexes and primary keys of the two tables respectively, as shown in the following figure.

As you can see from the figure above, in the index of the partitioned table Sale created directly, there is only one index named PK_Sale, which is a unique, nonclustered index, that is, the index created automatically by SQL Server when the PK_Sale primary key is created. Besides the unique, nonclustered index named PK_Sale1 created by SQL Server automatically when the primary key is created, there is also a clustered index named CT_Sale1 in the index of Sale1 which is converted from ordinary table to partitioned table.

For the table Sale, you can modify the partition function to convert it into a normal table, see "SQL Server partition table (IV): delete (merge) a partition", in fact, all partition boundaries in the partition function are deleted, then all the data in this partition table can only be stored in the first partition table. In this example, you can use the following code to modify the partition function.

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20100101')

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20110101')

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20120101')

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20130101')

In fact, after doing this, the table Sale is still a partition table, as shown in the following figure, but with only one partition, which is no different from a general table.

For a partitioned table that converts a normal table to a normal table by creating a partitioned index, in addition to the above method, you can also convert a partitioned table to a normal table by deleting the partitioned index. But you must go through the following two steps:

1. Delete the partition index

2. Rebuild an index on the original index field.

First of all, let's delete the partitioned index. this step is very simple. You can delete the partitioned index directly on SQL Server Management Studio, or you can delete it using SQL statements. For example, in this case, you can use the following code to delete the created partitioned index.

Drop index Sale1.CT_Sale1

Not only that, but also, the original clustered unique index (the index that is the primary key in this case) cannot be changed to a clustered index, as shown in the following figure.

If you want to solve this problem completely, you must also recreate the index on the field where the partitioned index was originally created, and only after recreating the index can SQL Server convert the partitioned table into a normal table. In this example, you can use the following code to recreate the index.

CREATE CLUSTERED INDEX CT_Sale1 ON Sale1 ([SaleTime])

ON [PRIMARY]

Go

After re-indexing, the partitioned table becomes a regular table, and now if we look at the properties of the Sale1 table, we can see that the original partitioned table has become a normal table, as shown in the following figure.

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

CREATE CLUSTERED INDEX CT_Sale1 ON Sale1 ([SaleTime])

WITH (DROP_EXISTING = ON)

ON [PRIMARY]

In theory, the operation in SQL Server Management Studio is the same as using the SQL statement, but I delete the clustered index in SQL Server Management Studio and then recreate an index with the same name on the field, and regenerate and organize the index, but the partitioned table still does not become a normal table, which makes me puzzled. However, as long as we can use SQL statements to achieve our goal, let's use it.

At this point, I believe you have a deeper understanding of "how SQL Server converts partitioned tables into ordinary tables". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.