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

This article mainly explains how SQL Server converts a normal table into a partition table. 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 "SQL Server how to convert an ordinary table into a partition table".

When designing the database, the problem of table partitioning is often not taken into account, and the partitioning method is often taken into account when the load-bearing burden of the data table is getting heavier and heavier. At this time, it involves the problem of how to convert the ordinary table into the partitioned table.

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

However, this time it is easy to say and a little more complicated to do. Continuing with the above example, let's first delete the original Sale table using the following SQL statement.

-- Delete the original data table

Drop table Sale

Then use the following SQL statement to create a new regular table and insert some data into the table.

-- create a new normal data table

CREATE TABLE Sale (

[Id] [int] IDENTITY (1pm 1) NOT NULL,-- automatic growth

[Name] [varchar] (16) NOT NULL

[SaleTime] [datetime] NOT NULL

CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED-- create a primary key

(

[Id] ASC

)

)

-- insert some records

Insert Sale ([Name], [SaleTime]) values ('Zhang San', '2009-1-1')

Insert Sale ([Name], [SaleTime]) values ('Li Si', '2009-2-1')

Insert Sale ([Name], [SaleTime]) values ('Wang Wu', '2009-3-1')

Insert Sale ([Name], [SaleTime]) values ('Qian Liu', '2010-4-1')

Insert Sale ([Name], [SaleTime]) values ('Zhao Qi', '2010-5-1')

Insert Sale ([Name], [SaleTime]) values ('Zhang San', '2011-6-1')

Insert Sale ([Name], [SaleTime]) values ('Li Si', '2011-7-1')

Insert Sale ([Name], [SaleTime]) values ('Wang Wu', '2011-8-1')

Insert Sale ([Name], [SaleTime]) values ('Qian Liu', '2012-9-1')

Insert Sale ([Name], [SaleTime]) values ('Zhao Qi', '2012-10-1')

Insert Sale ([Name], [SaleTime]) values ('Zhang San', '2012-11-1')

Insert Sale ([Name], [SaleTime]) values ('Li Si', '2013-12-1')

Insert Sale ([Name], [SaleTime]) values ('Wang Wu', '2014-12-1')

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

In the above code, we can see that this table has the characteristics of a normal table-- it has a primary key, and the primary key is also a clustered index. As mentioned earlier, a partitioned table is partitioned on a certain field, so a clustered index cannot be created for fields other than this field. Therefore, if you want to convert a regular table to a partitioned table, you must first delete the clustered index and then create a new clustered index in which the partitioning scheme is used.

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

-- delete the primary key

ALTER TABLE Sale DROP constraint PK_Sale

Create a primary key, but do not set it as a clustered index

ALTER TABLE Sale ADD CONSTRAINT PK_Sale PRIMARY KEY NONCLUSTERED

(

[ID] ASC

) ON [PRIMARY]

After re-revisiting the nonclustered 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:

Create a new clustered index and use the partitioning scheme in the clustered index

CREATE CLUSTERED INDEX CT_Sale ON Sale ([SaleTime])

ON partschSale ([SaleTime])

After you create a clustered index for the table that uses the partitioning scheme, the table becomes a partitioned table and view its properties, as shown in the following figure.

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

-Statistics of the total number of records in all partition tables

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records from Sale group by $PARTITION.partfunSale (SaleTime)

The running result of the above code is shown below, indicating that after converting the ordinary table into a partitioned table, the data is not lost, but is automatically placed in the partitioned table in which it should be.

At this point, I believe you have a deeper understanding of "how SQL Server converts a normal table into a partition table". 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.