SQL Server Table Partitioning detailed

Partition please think twice:

1, although the partition can bring many benefits, but also increased the implementation of object management costs and complexity. Therefore, consider carefully before partitioning to determine if the object should be partitioned.

2, after determining the partition for the object, the next step is to determine the partition key and the number of partitions. To determine partition data, you should first evaluate whether there are logical groupings and patterns in your data.

3. Determine if multiple file groupings should be used. To help optimize performance and maintenance, you should use filegroups to separate data. Filegroups are logical combinations of database data files that can be managed and distributed to improve the efficiency of concurrent access to database files.

To simplify operations, SQL Server 2008 provides related operations for table partitioning.

sequence of operations: 1, define filegroup 2, specify which secondary database files belong to this filegroup 3, put the table in a filegroup

Database Sub-filegroups (Specify disks):

The data is actually attached to the table to exist, we put the table into a filegroup, and the filegroup is a logical concept whose entity is a secondary database file (NDR), so it is equal to the data we specified into the specified secondary database file, Then, if you put these secondary database files in a different disk partition, you can finally achieve a targeted optimization of the corresponding data implementation performance.

When you create a filegroup, you define a different filegroup name, and you can make an orderly partition map filegroup for the next table partition, such as (select a database, right-click the property map).

A horizontal partition table has multiple partitions, one for each filegroup, resulting in many filegroups, and therefore performance gains, including I/O performance gains, because all partitions can be stationed on a different disk. Another benefit is that you can back up one partition separately from the backup filegroup. In addition, the SQL Server database engine can intelligently determine what data is stored on which partition and, if more than one partition is accessed, can also implement parallel data retrieval with multiple processors. This design also leverages the benefits of partitioned tables.

1. Improve scalability and manageability: Create partitions in SQL Server 2005 to improve scalability and manageability of large tables and tables with various access patterns.

2. Improve performance

3, only the partition of data to different disks, there will be a greater increase.

4, because when running queries involving joins between tables, multiple heads can read the data at the same time.

Partitioning a SQL Server data table is a three-step process:

1) Create a partition function

2) Establish a partition scheme

3) Partitioning the table

The steps are as follows:

1. Create PARTITION functions means creating a partition function.

2, Partfunsale 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 (' 20100101 ',' 20110101 ',' 20120101',   ' 20130101 ') to partition by these values.

• CREATE PARTITION FUNCTION yearcustomerfollowfunction (fdate) as RANGE right for VALUES (' 20100101 ', ' 20110101 ' , ' 20120101 ' , ' 20130101 ' )    
• The purpose of the 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 to put the partitioned data in
• CREATE PARTITION SCHEME Yearcustomerfollow as PARTITION yearcustomerfollowfunction to (sale2009,sale2010,sale2011, sale2012,sale2013)
• --create partition table on Yearcustomerfollow partition scheme name
• CREATE TABLE t_customerfollow_history ([Id] [int] IDENTITY () not NULL, [Name] [varchar] (+) not NULL, [FDa Te][datetime] not NULL) on Yearcustomerfollow ([fdate])
• --Delete a partition
• ALTER PARTITION FUNCTION yearcustomerfollowfunction () MERGE RANGE (' 20100101 ')
• --Add a partition
• ALTER PARTITION scheme yearcustomerfollow NEXT used [Sale2010]--partitioning scheme
• ALTER PARTITION FUNCTION yearcustomerfollowfunction () SPLIT RANGE (' 20100101 ')--cutoff value

SQL2005 Partition Table

The partitioning table facilitates the management of tables and indexes of large amounts of data, and introduces the concept of a partition key in a partition that performs aggregation of data based on an interval value, a specific value list, or a hash function.

Using partitioned tables has the following benefits:

1. Improve data usability: Availability is increased from the independence of each partition. The optimizer knows this partitioning mechanism and will remove unreferenced partitions from the query plan accordingly.
2. Reduce the burden on administrators.
3. To improve some query performance, partitioning works on two types of operations in terms of performance of read-only queries.

L Partition elimination: Some partitions are not considered when processing queries.

L Parallel Operation: Parallel full table scan and parallel index interval scan.

1. Reduce resource competition.

Script:

--First manually create a file grouping, physical files, script way to create behind the introduction

----Create a partition function

--go

--create partition function Minedaterange (datetime)

--as

--range right (left) for values (

--' 2010-01-01 ',

--' 2011-01-01 ',

--' 2012-01-01 ')

--go

--

----Create a partition scheme

--go

--create Partition Scheme Mine_orders

--as

--partition Minedaterange

--to (test2010, test2011, test2012,test2013)

--go

--Create a partitioned table

--go

--create table dbo. Orderstest

--(

--OrderID int NOT NULL

--, CustomerID varchar (TEN) NOT NULL

--, EmployeeID int NOT NULL

--, OrderDate datetime NOT NULL

--)

--on mine_orders (OrderDate)

--go

--Create a clustered partition index

--create clustered index ixc_orderstest on dbo. Orderstest (OrderDate)

--go

--Inserting data

--insert into [Hyminesecuritymonitor]. [dbo]. [Orderstest]

--([OrderID]

--, [CustomerID]

--, [EmployeeID]

--, [OrderDate])

--VALUES

--(7

--, ' FFFF ', 7

--, ' 2015-10-10 12:20:23 ')

--Querying data

--select * from Orderstest

--View the data distribution of each partition

--select partition = $partition. Minedaterange (OrderDate), rows = count (*), minval = min (OrderDate), maxval = max (OrderDate)

--FROM dbo. Orderstest

--group by $partition. Minedaterange (OrderDate)

--order by partition

--------------------------------------------Modify the partition----------------------------------------------------

--Add File grouping

--alter DATABASE hyminesecuritymonitor ADD FILEGROUP [test2014]

--Adding physical files

--alter DATABASE Hyminesecuritymonitor

--add FILE

--(NAME = n ' test2014 ', FILENAME = N ' D:\DataBase\testDB\test2014.ndf ', SIZE = 5mb,maxsize = 100mb,filegrowth = 5MB)

--to FILEGROUP [test2014]

--Modify partition function to add a partition

--go

--alter partition function Minedaterange ()

--split range (' 2013-01-01 ')

--go

--Modify partition scheme add a file

--go

--alter Partition Scheme mine_orders next used [test2014]

--go

--Modify partition function to merge one partition

--go

--alter partition function Pf_orders_orderdaterange ()

--merge range (' 2013-01-01 ')

--go

--------------------------------------------------------partition Table Data Migration-------------------------------------------

--sql Server 2005 partition Table partition switch in three forms:

--

--1. Toggles a partition of a partitioned table into a normal data table: Partition to table; (normal table: dbo. orders_1998)

--CREATE TABLE dbo. orders_1998

--     (

--OrderID int NOT NULL

--, CustomerID varchar (TEN) NOT NULL

--, EmployeeID int NOT NULL

--, OrderDate datetime NOT NULL

---) on [test2012]

--

--ALTER TABLE dbo. Orderstest switch partition 3 to dbo. orders_1998

--1). The normal table must be established on the filegroup where the partition table switch partition resides.

--2). The table structure of the ordinary table is consistent with the partition table;

--3). Indexes on ordinary tables are consistent with the partition table.

--4). The normal table must be an empty table and cannot have any data.

--2. Switch the normal table data to a partition in the Partition table: Table to Partition; (normal table: dbo. orders_1998)

--5). The normal table must be accompanied by a check constraint that is consistent with the partition data range.

--ALTER TABLE dbo. orders_1998 Add constraint Ck_orders1998_orderdate

--Check (orderdate>= ' 1998-01-01 ' and orderdate< ' 1999-01-01 ')

--

--ALTER TABLE dbo. orders_1998 switch to dbo. Orderstest Partition 3

--3. Switch partition table partitions to another partition table: Partition to Partition. (partition table dbo.) Orders, dbo. ordersarchive)

--ALTER TABLE dbo. Orders switch partition 1 to dbo. Ordersarchive partition 1

--ALTER TABLE dbo. Orders switch partition 2 to dbo. Ordersarchive Partition 2

--ALTER TABLE dbo. Orders switch partition 3 to dbo. Ordersarchive Partition 3

In fact, partitioned table partition switching does not actually move the data, but SQL Server changes the table's metadata at the bottom of the system. therefore partition Table partition switching is efficient, fast, and flexible. With partitioned table partitioning, we can quickly load data into a partitioned table. Unmount the partition data to a normal table, and then truncate the normal table to enable quick deletion of partitioned table data. Quickly archive inactive data to history tables.

--See how many partitions *******safun is the partition function ordercreatedate is the partition field

SELECT * FROM Sys.partition_functions

--See which partition the date is in

Select $PARTITION. Safun (' 2014-05-01 ')

--View a partition data

SELECT * from Sa_order where $PARTITION. Safun (ordercreatedate) =2

--see which partition each data is in

Select $PARTITION. Safun (ordercreatedate) as partition number, COUNT (Ordercode) as records from Sa_order Group by $PARTITION. Safun ( Ordercreatedate)