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

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.

-- Delete the original data table   Drop Table Sale  

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

--create a new normal data tableCREATE TABLESale ([Id] [int] IDENTITY(1,1) not NULL,--Automatic growth    [Name] [varchar]( -) not NULL,      [Saletime] [datetime]  not NULL,      CONSTRAINT [Pk_sale] PRIMARY KEY CLUSTERED  --Create a primary key    (          [Id] ASC      )  )  --insert Some RecordsInsertSale ([Name],[Saletime])Values('Zhang San','2009-1-1')    InsertSale ([Name],[Saletime])Values('John Doe','2009-2-1')    InsertSale ([Name],[Saletime])Values('Harry','2009-3-1')    InsertSale ([Name],[Saletime])Values('Money Six','2010-4-1')    InsertSale ([Name],[Saletime])Values('Zhao Qi','2010-5-1')    InsertSale ([Name],[Saletime])Values('Zhang San','2011-6-1')    InsertSale ([Name],[Saletime])Values('John Doe','2011-7-1')    InsertSale ([Name],[Saletime])Values('Harry','2011-8-1')    InsertSale ([Name],[Saletime])Values('Money Six','2012-9-1')    InsertSale ([Name],[Saletime])Values('Zhao Qi','2012-10-1')    InsertSale ([Name],[Saletime])Values('Zhang San','2012-11-1')    InsertSale ([Name],[Saletime])Values('John Doe','2013-12-1')    InsertSale ([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:

--Remove primary KeyALTER TABLESaleDROP constraintPk_sale--Create a primary key, but not set as a clustered indexALTER TABLESaleADD CONSTRAINTPk_salePRIMARY KEY nonclustered  (      [ID] ASC  )  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:

-- Create a new clustered index that uses the partitioning scheme  in the clustered index CREATE CLUSTERED INDEX  on Sale ([saletime])  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.

-- count the total number    of records in all partitioned tables Select  as partition number,countas fromgroup 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