How SQL Server 2008 creates a partitioned table and compresses database space

1. What is a partition Table

A partitioned table is logically a table and physically multiple tables. From the user's point of view, the partition table and the normal table are the same. The primary purpose of using partitioned tables is to improve the scalability and manageability of large tables and tables with multiple access patterns. A partitioned table is a set of data that is partitioned into regions that are stored in different filegroups, using partitions to quickly and efficiently manage and access subsets of data.

Suitable for partitioning the table situation

? A table in the database a lot of data, in the query data will obviously feel very slow, this time need to consider the partition table;

? Data is segmented, such as year-delimited data, for the current year's data are often used to increase and revise the operation, and for previous years of data almost do not operate or only do query operations, this situation can use the partition table. The operation of the data if only a subset of data is involved, but not all of the data can consider the partition table, if the data of a table is often used and regardless of the year and other factors often to change the operation is best not to partition.

2. Why table Partitioning? A partitioned table can physically divide a large table into several small tables, but logically it is a large table.

? For systems with multiple CPUs, partitioning can perform parallel operations on the table, which can improve access performance.

3. How to Partition Table

The steps to create a partitioned table are 5 steps:

(1) Creating a database filegroup

(2) Create a database file

(3) Creating a partition function

(4) Create a partition scheme

(5) Creating a partitioned table

3.1. Create a database file group

         Note: database: zmqgl_test dbo.entry_head, dbo.entry_list for example

            Create filegroups: alter database zmqgl_test add  filegroup group1

Syntax:alter database < data name > add filegroup < filegroup name >

3.2. Create a database file

Create the file and add it to the filegroup:

Alter database zmqgl_test add file

(n Ame=n ' ById1 ' ,filename =n ' E:\DB\ZMQGL_test\ById1.ndf ' ,size=5< Span data-wiz-span= "Data-wiz-span" >mb, Filegrowth=5 mb)

t O filegroup group1   Syntax: ALTER DATABASE < DB name > Add file < Data identity > to filegroup < filegroup name >

--< Data Identification > (name: File name, Fliename: physical path file name, size: File Initial size kb/mb/gb/tb,filegrowth: File AutoIncrement kb/mb/gb/tb/%,maxsize: Maximum size The file can increase to kb/mb/gb/tb/ Unlimited) 

   

3.3. Create a partition function, Create a partition scheme, Create a partitioned table

Right-click the table you want to partition--->> storage--->> create a partition--->> Show the wizard view--->> Next--->> Next:

                                                 

Click "Next", and finally click Finish to generate the partition function and partition scheme script, as follows:Use [Zmqgl_test] GO BEGIN TRANSACTIONCREATE PARTITION FUNCTION [Fq_hs] (DateTime2 (3)) as RANGE left for VALUES (n ' 2015-12-31t23:59:59.999 ', n ' 2016-12-31t23:59 : 59.999 ') create PARTITION SCHEME [FQ_FA] as PARTITION [FQ_HS] to ([PRIMARY], [Group1], [Group2]) Create CLUSTERED INDEX [Cl USTEREDINDEX_ON_FQ_FA_636040955802353043] on [dbo]. [Entry_head] ([D_date] ) with (sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = off, ONLINE = off) on [Fq_fa] ([d_date])DROP INDEX [clusteredindex_on_fq_fa_636040955802353043] on [dbo]. [Entry_head] With (ONLINE = OFF) COMMIT TRANSACTION Direct F5 operation can be done.

4. Added partition table "New partition boundary"1, first add file groups and files, for example: GROUP3, and establish a good correspondence relationship
2. New Boundary value

ALTER PARTITION SCHEME fq_fa NEXT used [GROUP3]

‘

    syntax ":

ALTER PARTITION Scheme < partitioning scheme > NEXT used < filegroups >

ALTER PARTITION Function < partition functions > SPLIT RANGE (< boundary value >)

5. Delete (merge) partitions

 Delete partition with boundary value ' 2017-12-31 23:59:59.999 '

ALTER PARTITION FUNCTION fq_hs () MERGE RANGE (' 2017-12-31 23:59:59.999 ')

The zoning plan that's really into with it has also changed.

How SQL Server 2008 creates a partitioned table and compresses database space