Biwork partition table reading and practice notes

/*************************************************************** biwork Partition Table reading and Practice Note: the example uses the SQL
    
      
    
The Demo Database for Server 2000, which you can download from this link http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654  When checking the deletion of Partition function and Partition scheme, be aware that Partition scheme references Partition Function, all of which need to be deleted first Partition Scheme.
    
Similarly, a table referencing partition Scheme should be deleted first. Reference relationship: TABLE-> PARTITION SCHEME-> PARTITION FUNCTION ********************************************************* /IF object_id (' dbo. Orders ') is not NULL DROP TABLE Orders go IF EXISTS (SELECT * from Sys.partition_schemes WHERE na me = ' ps_orders ') DROP PARTITION SCHEME ps_orders go to IF EXISTS (SELECT * from Sys.partition_func
    
     
    
tions WHERE name = ' Pf_orders_orderdaterange ') DROP PARTITION FUNCTION pf_orders_orderdaterange Go
/***************************************************************    
1. If you do not need to physically group the database, such as a partitioned table or enjoy the same filegroup, you should be able to create a partition function from the creation of partition functions * * * Determine the type of the partitioning key column (DATETIME) and the boundary value of the partition:
    
            (' 1997-01-01 ', ' 1998-01-01 ', ' 1999-01-01 ') * * n boundary value determine n+1 partition * * Right-all values for the first partition are less than Val < 1997-01-1 The value range for the second partition is 1997-01-01 <= VAL < 1998-01-01 *****************************************************
    
   /CREATE PARTITION FUNCTION pf_orders_orderdaterange (DATETIME) as RANGE right for VALUES ( ' 1997-01-01 ', ' 1998-01-01 ', ' 1999-01-01 ') go EXEC dbo.sp_show_partition_ Range @partition_function = ' Pf_orders_orderdaterange '/***************************************************                                    Displays partitioning of partition functions, PARTITION function,partition,minval,value,maxval pf_orders_orderdaterange 1 NULL <= Val < 1997-01-01 00:00:00.000 Pf_orders_orderdaterange 2 1997- 01-01 00:00:00.000 <= Val < 1998-01-01 00:00:00.000 Pf_orders_orderdaterange 3 1998-01-01 00:00:00.000 <= val <
    
1999-01-01 00:00:00.000 pf_orders_orderdaterange 4 1999-01-01 00:00:00.000 <= val < NULL ****************************************************************/
    
     
    
/*************************************** 2. After you create a partition function, you can create a partition scheme * * * * * * * because there is a boundary value in the previous partition function, 4 partitions, and there are no other database filegroups, so when the partitioning scheme is applied to the specific partition functions, all partitions are pointing to the primary file
    
Group ***************************************************************/CREATE PARTITION SCHEME ps_orders as PARTITION Pf_orders_orderdaterange to ([primary],[primary],[primary],[primary]) go/********** 3. The partition scheme is applied when the partition table is created and the specific partition key column on partition function (partition key column) is provided ****************************************************************/CR Eate TABLE dbo. Orders (OrderID inT NOT NULL, CustomerID VARCHAR (TEN) not NULL, EmployeeID INT is not NULL, OrderDate DATETIME not NULL) on ps_orders (OrderDate) Go/************************************** 4. After you create a partitioned table, you need to create a clustered partition index * * * The features that are often queried according to the order Form orders query when OrderDate scope conditions are used, * * We'd better set up a clustered index on the orders.orderdate column (cl
    
Ustered index).
    
To facilitate partition switching (partition Swtich) In most cases, it is recommended that you establish a partitioned index on a partitioned table. /CREATE CLUSTERED INDEX ixc_orders_orderdate on Dbo. Orders (OrderDate) Go/******************************************************************* 5. Create a primary key for a partitioned table if the primary key does not contain a partitioning key column, the following error message appears: MSG 1908, level, State 1, Line 2 Column ' Orderdat
    
E ' is partitioning column of the index ' pk_orders '.
    
Partition columns for a unique index must is a subset of the index key. MSG 1750, level, state 0, line 2 could not create constraint.
    
     
    
Previous errors.
    
Reason: The primary key is actually a unique index, but the partitioned table must be part of a unique index when it establishes a unique index (a partitioned index). Because SQL Server ensures that indexes are unique across partitions, it is guaranteed to be unique throughout the table. /ALTER TABLE dbo. Orders ADD CONSTRAINT pk_orders PRIMARY KEY (OrderID, customerid,orderdate) go/***************** To view the index on a partitioned table Orders: Ixc_orders_orderdate|cluste Red located on ps_orders| OrderDate pk_orders|nonclustered,unique,primary Key located on ps_orders| OrderID, CustomerID, OrderDate ************************************************************************/EXEC Sp_helpindex ' dbo. Orders '/********************************************************************** 6. Import test data from SQL Server ************************************************************/INSERT into dbo. Orders SELECT OrderID, CustomerID, EmployeeID, OrderDate from Northwind.dbo. Orders/************************************************************************ 7. To view the partition data of partitioned tables (data rows, maximum minimum OrderDate values) *************************************************************************/SE Lect PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. Orders GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION go/************************************************    In the current test data, there is no more than 1999 of data, so in the above query results do not see the first partition information: Partition,rows,minval,maxval 1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000 2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:0 0.000 3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000 ********************************************************************** /--Inserts a test data insert INTO dbo. Orders VALUES (11111, ' TEST ', 1, ' 2000-10-10 10:10:10:100 ')--requery SELECT PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. Orders GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/******************************************************** The query results show the information for the Partitions Partition,rows,minval,maxval 1 152 1996-07-04 00:00:00.000 1996-12- 31 00:00:00.000 2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000 3 270 1998-01-01 00:00:00.0 00 1998-05-06 00:00:00.000 4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100 ***************** *************************/GO/**************************************************************** Toggles a partition of a partitioned table into a regular datasheet *************************** ************* Partition to table ***********
    
*****************************/
    
     
    
/************************************************************************* 1. First set up the ordinary data table orders_1998, which holds the order date of 1998 years of all data 2. Partitioning to a normal table is best met with the following prerequisites: A. The normal table must be set on the filegroup on which the partition table switch partition is located on [PRIMARY] B. Table structure of normal table and partition Table C. The index on the normal table is consistent with the partition table (poly
    
Set index, nonclustered index) d. Normal tables must be empty tables, no data *************************************************************************/ IF object_id (' orders_1998 ') is not a NULL DROP TABLE orders_1998 go CREATE table dbo.  orders_1998 (OrderID INT not NULL, CustomerID VARCHAR (a) not NULL, EmployeeID
    
 INT NOT NULL, OrderDate DATETIME NOT null) on [PRIMARY] Go    
    
--Add a clustered index and partition table consistent CREATE CLUSTERED index ixc_orders1998_orderdate on dbo. orders_1998 (OrderDate) Go--Add primary key, and partition table consistent ALTER table dbo. orders_1998 ADD CONSTRAINT pk_orders_1998 PRIMARY KEY (orderid,customerid,orderdate) Go/******** * * Start toggle partition Table orders Third partition data (1998 data) to ordinary table orders _1998 * * Keywords-SWITCH PARTITION [number] to [History Table] ***************************************************** /ALTER TABLE dbo. Orders SWITCH PARTITION 3 to dbo.
    
orders_1998/*************************************************************************** Query Source partition table results The partition number for the data has not been 1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000 2 408 1997-01-01 00:00:00.00 0 1997-12-31 00:00:00.000 4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100 ****************** ************************/SELECT PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. Orders GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/********************************************* Query Archive Table Results 3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000 * * * /SELECT PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. orders_1998 GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/********************************************* Switch Normal table data to the partition table********************* ************* Table to Partition ************************/in a partition /************************************************************************* above we have switched the data from the third partition of the partition table to the normal table orders _1998, now we switch back: **************************************************************************/ALTER TABL E dbo. orders_1998 SWITCH PARTITION 3 to dbo.  Orders/************************************************************************* error message: MSG 4911, Level 16, State 2, Line 1 cannot specify a partitioned table without partition number in ALTER table SWITCH statement.
    
The table ' SSISDemoDB.dbo.Orders ' is partitioned. Reason: Should actually be the dbo. The data in the orders_1998 table is switch to dbo.
    
The Partition partition of the Orders table. and cannot be said to be the dbo. orders_1998 the partition's data switch to dbo. **************************************************************************/ALTER table db in Orders entire table o.orders_1998 SWITCH to dbo.
  Orders PARTITION 3  
/************************************************************************* error message: MSG 4982, Level, State 1, Line 1 ALTER TABLE SWITCH statement failed. Check constraints of source table ' dbo. orders_1998 ' Allow values that are not allowed by range defined by partition 3 on target table ' dbo.
    
     
    
Orders '. Reason: Table dbo.
    
The data of Orders is defined by the partitioning column of the partition function, and the data in each partition is actually checked by the data constraint, and the data that conforms to the partition boundary range (range) is entered into each partition. But in the History Table/Archive table dbo.
    
The data in the orders_1998 actually has no boundary constraints, such as the fact that you can manually insert a year's data, which is definitely not going to work when you switch.
    
     
    
So in the switch, the first is a binding check, although there is no data that does not conform to the specification, but there are potential threats. So before you switch, dbo is the first.
    
orders_1998 Add a CHECK constraint and switch again, success! /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. Orders PARTITION 3/******************************Query the source partition table results, the partition's data has been from dbo. orders_1998 back 1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000 2 408 1997-01-01 00:00:00.0 00 1997-12-31 00:00:00.000 3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000 4 1 2000- 10-10 10:10:10.100 2000-10-10 10:10:10.100 *********************************************************************** /SELECT PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. Orders GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/********************************************* Query archive table results, no data, has been successfully switch to Orders table Partition 3 ******************************* /SELECT PARTITION = $PARTITION.Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. orders_1998 GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/******************************************************** Therefore, in the archive table data to the partition table migration process (table to PARTITION), compared (PARTITION to TABLE) One more condition: normal table must add and partition data range one
    
Constraint conditions. *****************************************************************************/
    
     
    
/************************** Toggle partition table data to partition Table ******************************** * * * PARTITION to PARTITION **********************************//******************************* --The new archived partition table is structurally consistent with the source partition table, including partition functions and partitioning schemes, but it needs to be recreated and cannot simply use dbo directly. 
    
Partition functions and partitioning schemes on the Orders table because they have a binding relationship. *******************/IF object_id (' ordersarchive ') is not NULL DROP TABLE or Dersarchive go to IF EXISTS (SELECT * from sys.partition_schemes WHERE name = ' ps_ordersarchive ') D ROP PARTITION SCHEME ps_ordersarchive go to IF EXISTS (SELECT * from sys.partition_functions WHERE name
    
     
    
= ' Pf_ordersarchive_orderdaterange ') DROP PARTITION FUNCTION pf_ordersarchive_orderdaterange Go CREATE PARTITION FUNCTION Pf_ordersarchive_orderdaterange (DATETIME) as RANGE right for VALUES (' 1997-01-01 ', ' 1998-01-01 ', ' 1999-01-01 ') go CREATE PARTITION SCHEME Ps_ordersar Chive as--partitioning scheme and partition functions bind PARTITION Pf_ordersarchive_orderdaterange to ([Primary],[primary],[primar Y],[primary]) go CREATE TABLE dbo. Ordersarchive (OrderID INT not NULL, CustomerID VARCHAR ((a) Not NULL, EmployeeID INT NOT NULL, OrderDate DATETIME NOT NULL)--table and partition scheme Bound on ps_ordersarchive (OrderDate) go CREATE CLUSTERED INDEX ixc_ordersarchive_orderdate on dbo. Ordersarchive (OrderDate) ALTER TABLE dbo. Ordersarchive ADD CONSTRAINT pk_ordersarchive PRIMARY KEY (OrderID, customerid,orderdate) go/*** Start switching partitions ************************** /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/*************************************************************************** query Source partition table results, There will only be partitions of data 4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100 ***************************************************************************/SELECT PARTITION = $PARTITION.
    
       Pf_orders_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. Orders GROUP by $PARTITION. Pf_orders_orderdaterange (OrderDate) Order by PARTITION/*********************************************
    
Query archive table results have been successfully transferred 1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000 2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000 3 270 1998-01-01 00:00:00.000 1998-05-06 00 : 00:00.000 ***************************************************************************/SELECT PARTITION = $PART Ition.
    
       Pf_ordersarchive_orderdaterange (OrderDate), ROWS = COUNT (*), minval = MIN (OrderDate), Maxval = MAX (OrderDate) from dbo. OrderSarchive GROUP by $PARTITION. Pf_ordersarchive_orderdaterange (OrderDate) Order by PARTITION/************************************** Summary: Partitioning table partitioning does not actually move data, but SQL Server changes the table's metadata at the bottom of the system.
    
Partitioning table partitioning is therefore efficient, fast, and flexible. With partition table partitioning, we can quickly load data into partitioned tables. Unload the partitioned data to a normal table, and then truncate the normal table to quickly delete the partitioned table data and quickly archive inactive data to the history table. ****************************************************************************/

Note: The original blog address has not been remembered, this log is based on the results of other people's analysis plus their own hands-on, thinking, add some code and comments. In the annotation and conclusion verification in accordance with their own understanding to make a relatively detailed description of the new Contact table zoning concept of friends believe that there will be a great help. I also learned and mastered some of the basic operation steps and processing methods of table partitioning in this way.