Database Partition Table

Database partition table (i) What happens when partitioning is required to prepare the data that needs to be partitioned

What database needs to be partitioned? First look at our case: June 2010 Our six IT development team received a XX national chain of food and beverage system, after a week of agile development, XX food system officially on-line, due to the software's powerful, simple operation, functional flexibility and other characteristics, soon spread across the country. XX restaurant food is also quite popular with customers, and some stores daily income of up to 1W yuan, so much income every day, so how much to produce a daily order? < XmlNamespace prefix = "o" ns = "Urn:schemas-microsoft-com:office:office"/>

This is a large amount of data, in the beginning of the first few months, we can also be normal according to all the orders generated, according to the product category and sales situation, produce a product sales ranking report. But as the volume of data is getting bigger and larger, there is now a long waiting time for each product sales ranking to be aggregated. And our server performance is not a problem, then we only improve the structure of the database to improve the efficiency of data retrieval. There are two ways to improve the structure of a database, one is to use stored procedures instead of ordinary SQL statements, the other is using the database system to enhance the index and planning partition table for optimization, here we use the second solution to solve the problem.

We use data partitioning table method for data optimization, so as long as the amount of data is large enough to make the database partition table to improve query efficiency? More data is not the only way to create a partitioned table, even if you have 10 million records, but these 10 million records are commonly used records, it is best not to use the partition table, it might not be worth the candle. Only if your data is segmented, consider whether you need to use a partitioned table. Here our data is queried by time period, so we can partition here.

These are the prerequisites for our data partitioning table, and if you have such a requirement, then wait and use the data partitioning table. Here we provide a simple database, convenient for later instance of the practice, if you need, you can click here to download. If you have a database that has just been backed up, what if the restore is unsuccessful? Here's the solution.

Database partition table (ii) What is a partitioned table?

Table partitions are divided into horizontal and vertical partitions. Horizontal partitioning divides a table into multiple tables. Each table contains the same number of columns, but fewer rows. For example, a table with 1 billion rows can be partitioned horizontally into 12 tables, each representing one months or months of data in a particular year. Any query that requires specific month data only needs to refer to the table for the corresponding month. Vertical partitioning, in turn, divides the original table into multiple tables that contain only fewer columns. Horizontal partitioning is the most commonly used partitioning method, followed by a horizontal partition to introduce a specific implementation.

To put it simply, a partitioned table is one that divides a large table into a number of small tables. Here, we have a sales record table that records the order of each restaurant, so you can divide the sales record into a few small tables, which we divide into 15 small tables. Records from 2011 Use a table, 2011 Records use a table every two months (2011//01/01-2011/03/01, a table for each two month, a total of six tables), 2012, 2013 () years of records ibid, 2013 Records use a table, A table is used for records after 2012. Then, you want to query which year's record, you can go to the corresponding table query, because the number of records in each table is less, the query time will naturally be reduced.

But dividing a large table into a few small tables can make programming more difficult for programmers. To add records as an example, the above 15 tables are separate 15 tables, when adding records at different times, the programmer to use different SQL statements, such as when adding records in 2011, the programmer to add records to the 2011 table, in 2012 to add records, The programmer wants to add the record to the 2012 table. In this way, the programmer's workload increases and the likelihood of errors increases.

The use of partitioned tables can be a good solution to the above problems. A partitioned table can physically divide a large table into smaller tables, but logically, it is a large table. A partitioned table can divide a sales record table into 15 physically small tables, but for programmers, he is still faced with a large table, whether it is adding records in 2010 or adding records in 2012, for the programmer is no need to consider, he just insert records into the Sales record table-- The big table in this logic is on the line. SQL Server automatically places it in the physical table that it should stay in.

Similarly, for the query, programmers only need to set up the query criteria, Ok,sql server will automatically go to the corresponding table query, do not care too much.

Partition Table preparation work is done, the next step is to create the partition table, and so on, look at the next blog post you know.

Database partition table (iii) How do I create a partition table 1?

The following five steps are required to create a partitioned table.

1) Create file groups

2) Create a file

3) Create a partition function

4) Create a partition scheme

5) Create a partitioned table

(1) To create a filegroup, there are two scenarios, one is manually added, and the other is to add it through a SQL script. The following two scenarios illustrate:

Scenario One: Create filegroups, although we can omit this step, because we can directly use the primary file (i.e. the system master file). However, to facilitate management, we still create several filegroups, so that different small tables (different time periods, or different data tables) can be placed in different filegroups, which is easy to understand and improve the speed of operation.

Open SQL Server Management Studio, locate the database where the partition table resides, right-click to select Properties, select Filegroups, click the Add button below, and add the X filegroups as shown:

Scenario two: Execute via Query parser SQL script

ALTER DATABASE cxfunsche ADD FILEGROUP CXFG2010

ALTER DATABASE cxfunsche ADD FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILEGROUP CXFG2012

ALTER DATABASE cxfunsche ADD FILEGROUP CXFG2013

(2) Create a database file

Scenario One: After you create a filegroup, you also create several database files. Why create a database file, this is very understandable, because the partition of the small table must be placed on the hard disk, and put on the hard disk where? Of course it's in the papers. Besides, there are no files in the filegroup, what's the use of filegroups? Or in that interface, select the "File" option, and then add a few files. Note the following points when adding a file:

1. Don't forget to put different files in the filegroup. Of course, a filegroup can also contain several different files.

2. If possible, put different files in different hard disk partitions, preferably on separate hard drives. Knowing the speed of IQ is often one of the important conditions that affect the speed of SQL Server operations. By placing different files on different hard disks, you can speed up the SQL Server run.

In the example of this article, the database master file and partition file is not in the same directory, each partition file can also be placed in a different directory, it is recommended to use during the practice.

Scenario two: Execute via Query parser SQL script

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail2010 ', FILENAME = N ' D:\program files\programming software\sql Se RVer 2005\mssql.1\mssql\data\selllogdetail2010.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2010

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201102 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201102.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2010

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201104 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201104.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201106 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201106.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201108 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201108.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201110 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201110.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201112 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201112.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2011

ALTER DATABASE cxfunsche ADD FILE (NAME = n ' SellLogDetail201202 ', FILENAME = N ' D:\program files\programming software\sql Server 2005\mssql.1\mssql\data\selllogdetail201202.ndf ', SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP CXFG2012

.

.

.

Because of the creation of too many data files, here will not be posted out, the rest of the few, left to everyone to exercise their own. Once you have created the partition file, you can find the data file you just created in the corresponding directory on the D drive.

Database partition table (iii) How do I create a partition table 2?

(3) Creating a partition function

Create a partition function that is created to tell SQL Server how to partition a partitioned table. This step must be done by SQL script. In the example above, we want to divide the sales records into 15 small tables by time. The time divided is:

1th Small table: Data prior to 2011-01-01 (not including 2010-01-01).

2nd Small table: Data from 2011-01-01 (including 2011-01-01) to 2010-01-01.

3rd Small table: Data from 2011-03-01 (including 2011-03-01) to 2011-05-01.

4th Small table: Data from 2011-05-01 (including 2011-05-01) to 2011-07-01.

The 5th Small table: ...

The 6th small table: ...

--Create a partition function

CREATE PARTITION FUNCTION partfun_cx (datetime)

As RANGE right for VALUES (' 20110101 ', ' 20110301 ', ' 20110501 ', ' 20110701 ', ' 20110901 ', ' 20111101 ', ' 20120101 ', ' 20120301 ', ' 20120501 ', ' 20120701 ', ' 20120901 ', ' 20121101 ', ' 20130101 ', ' 20130301 ', ' 20130501 ', ' 20130701 ')

1. Create PARTITION functions means creating a partition function.

2, PARTFUN_CX is the partition function name.

3, as range right is the way to set the range of the partition, that is, the way.

4. For VALUES (' 20110101 ', ' 20110301 ', ' 20110501 ', ' 20130101 ',......) To partition by these values, the value in values is the condition of a partition

(4) Create a partition scheme

The purpose of a partitioning scheme is to map partitions generated by partition functions to filegroups. The purpose of the partitioning function is to tell SQL Server how to partition the data, and the partitioning scheme is to tell SQL Server which filegroup the partitioned data is placed in.

--. Create a partition scheme

CREATE PARTITION SCHEME partsch_cx

As PARTITION partfun_cx

to (

CX2010,

CX2011, CX2011, CX2011, CX2011, cx2011,cx2011,

CX2012, CX2012, CX2012, CX2012, cx2012,cx2012,

CX2013, CX2013, CX2013, CX2013)

1. Create PARTITION scheme means creating a partition scheme.

2, PARTSCH_CX is the partition scheme name.

3, as PARTITION PARTFUN_CX describes the partitioning scheme used by the data partitioning conditions (that is, the partition function used) is PARTFUN_CX.

4, to the back of the content refers to the PARTFUN_CX partition function is divided into the data corresponding to the filegroup stored.

So far, the partitioning function and partitioning scheme have been created. The created partition function and partition scheme can be seen in the storage of the database.

(5) Creating a partitioned table

Create a partitioned table similar to the creation of a universal table, as follows:

CREATE TABLE T_partition3 (

ptId int IDENTITY (a) Not NULL primary key nonclustered,

Ptname varchar (+) not NULL,

Selltime[datetime] Not NULL

) on PARTSCH_CX (Selltime)

If you follow the code above, the error code hint appears as follows:

Msg 1908, Level 16, State 1, line 1th

Column ' Selltime ' is the partitioning column for index ' pk__t_partition3__671f4f74 '. The partitioning column for a unique index must be a subset of the index keys.

Msg 1750, Level 16, State 0, line 1th

Unable to create constraint. See the preceding error message.

It is not possible to create a clustered index other than the partition field in the partition table, because the clustered index is stored in a physical order, and the partitioned table stores the data separately in a different table, which is a conflict between the two concepts. If we create a clustered index of other fields, we store them in a physical order according to the other fields, and our partitioned tables are stored physically in the order of the partitioned fields.

Database partition table (iv) converting a normal table to an ingredient area table

2011-02-15 16:27:43|  Category: Database Learning | Tags: | font size Big small subscription

Our database has been in use for some time, but we didn't create a partition table at that time, but what we need to do now is to convert the normal table to the constituent table, but it doesn't affect the data in our database, so what should we do? Simply create a clustered index on the table and use the partitioning scheme in that clustered index.

It's easy to say, but it's not that easy to implement, because you have a primary key, a foreign key, and other constraints in your database, so we need to address these issues first when we convert the normal table to the constituent table.

We know a partition table when a field is a partition condition, in addition to this field other than the field is not able to create a clustered index, so when we convert the normal table to the constituent table, we must delete the clustered index, and then re-create a new clustered index in the clustered index to use the partition scheme.

But we need to modify the OrderID in the T_selllog table to be both the primary key and the clustered index, and the foreign key for the other table. Therefore, we can only delete the Foreign Key Association, then delete the primary key, then re-create the OrderID primary key, but set to a nonclustered index, and then set our Selltime field as a clustered index, and finally add our foreign key constraints, the normal table to convert the work of the Component table is finished, the code is as follows:

--View FOREIGN KEY constraints

Use Cx_partiton_scheme

EXEC sp_helpconstraint T_selllog

--delete foreign KEY constraint

ALTER TABLE T_selllog DROP constraint Fk_t_selllog_t_user

--Remove the primary key

ALTER TABLE t_selllog DROP constraint Pk_t_selllog

--Create a primary key, but not set as a clustered index

ALTER TABLE t_selllog ADD CONSTRAINT pk_t_selllog PRIMARY KEY nonclustered (

OrderId ASC

)

On [PRIMARY]

--Create a new clustered index that uses the partitioning scheme in the clustered index

CREATE CLUSTERED INDEX ct_selllog on T_selllog (selltime)

On PARTSCH_CX ([Selltime])

--Add the deleted foreign key constraints (specific to their own implementation according to the actual situation)

After the conversion succeeds, we can look at the number of records in each partition table by the following code:

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

Select $PARTITION. PARTFUN_CX ([selltime]) as partition number, COUNT (orderId) as record count from T_selllog group BY$PARTITION.PARTFUN_CX ([ Selltime])

We can also use the following code to see which partition the data in the database library is in:

--See which partition the data in the database table is in

Select $PARTITION. PARTFUN_CX (' 2010-10-1 ')--the partition in which the data for the month and day are queried

Select $PARTITION. PARTFUN_CX (' 2011-01-1 ')--the partition in which data is queried if you want to compare how much efficiency has been increased after we use the partitioning scheme and the previous program We can use the following statement to see the execution time of the script is OK, I tested the data is 0.017 seconds faster, on the one hand because of our test data volume is small, on the other hand my machine configuration is pretty good.

--View the execution time of the SQL script

Select GetDate ()

SELECT * FROM T_selllog

Select GetDate ()

Database partition table (RPM)