Shulou(Shulou.com)05/31 Report--

In order to solve the problem of how to convert ordinary tables into partition tables in SQL, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find an easier way.

The code is as follows:

CREATE TABLE Sale ([Id] [int] identity (1jue 1) NOT NULL,-- automatic growth [Name] [varchar] (16) NOT NULL, [SaleTime] [datetime] NOT NULL, constraint [PK_Sale] primary key clustered-create 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')

Alter table Sale drop constraint PK_Sale-create a primary key, but not set to clustered index alter TABLE Sale add constraint PK_Sale primary key nonclustered ([ID] ASC) ON [PRIMARY]

-- create the partition function GOcreate partition function [pf_Sale] (SaleTime) as range left for values (Noble 2010-5-1T00 VOLO 00.000mm, Native 2012-9-1T00:00:00.000'GO

-- create partition structure GOcreate partition scheme [pt_Sale] as partition [pf_Sale] TO ([Sale1], [Sale3], [Sale2]) GO

-- create a GOcreate table [dbo]. [AvCache] ([Id] [int] identity (1) NOT NULL,-- automatically grow [Name] [varchar] (16) NOT NULL, [SaleTime] [datetime] NOT NULL,) on [pt_Sale] (SaleTime)-- Note that the [pt_Sale] architecture is used here, according to the SaleTime partition.

-- check usage SELECT *, $AvCache. [pt _ Sale] (SaleTime) FROM dbo. [AvCache]

This is the answer to the question about how to convert a normal table into a partition table in SQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.