SQL Script Collation series one table partition

Purpose of Table partitioning:

1. To put historical data into another table can improve query efficiency of course, if you frequently query the combined result set of historical data and new data, it's a lot worse.

2. By putting a table into different files, and then storing different files in different disk arrays, you can increase the IO speed CPU much faster than the hard disk

3. Improve usability, one disk is broken, and the other file on the other disk is not very meaningful to me.

4. Convenient backup only need to do a partition backup, such as cloud server, the data volume is larger than 4 forms, put these four tables in a file so the daily backup basically does not spend any time, restore is more convenient, the data of the 4 tables lost meaning is not big, did not test

The definition of a partitioned table is broadly divided into three steps:

1. Defining partition Functions
2. Defining the partition Architecture
3. Defining partition tables inserting test data
4. "Importing a table's data into another table is a very fast one."

--Create a database these operations can be done in the graphical interface
--because they are tested on their own computers, they're all on the same hard drive. http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html

CREATE  DatabaseSales on Primary(Name=N'Sales', filename=N'G:\data\Primary\Sales.mdf', size=3MB, MaxSize=100MB, FileGrowth=Ten%),--Creating FilegroupsFilegroup FG1 (NAME=N'File1', FILENAME=N'G:\DATA\FG1\FILE1.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth= Ten% ),--Creating FilegroupsFILEGROUP FG2 (NAME=N'File2', FILENAME=N'G:\DATA\FG2\FILE2.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth= Ten%   ),--Creating FilegroupsFILEGROUP FG3 (NAME=N'File3', FILENAME=N'G:\DATA\FG3\FILE3.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth= Ten%   )   LOG  on(NAME=N'Sales_log', FILENAME=N'G:\data\Primary\Sales_Log.ldf', SIZE=1MB, MAXSIZE=100MB, FileGrowth= Ten%)GO

View Code

--Create a partition function

 Use Sales    GO CREATE FUNCTION pf_orderdate (datetime)   as right  --  forVALUES ('2003/01/01'2004/ 01/01'GO

View Code

--Creating a partition structure such as storing data between 1900-01-01-' 2003/01/01 ' into FG2
--The data between ' 2003/01/01 '-' 2004/01/01 ' is also stored in FG2
-The data between ' 2004/01/01 '-is also stored in FG3

-- establish a partition architecture such as storing data between 1900-01-01-' 2003/01/01 ' in FG2 -- the data between ' 2003/01/01 '-' 2004/01/01 ' is also stored in the FG2 -- the data between ' 2004/01/01 '-  is also stored in FG3 inside use   Salesgocreate   Partition  scheme ps_orderdateas  partition  pf_orderdateto( FG2,FG2,FG3)go

View Code

--Create a table

--Create a table UseSalesGoCreate TableOrders--Order Form(OrderIDint Identity(10000,1), OrderDatedatetime   not NULL, CustomerIDint  not NULL,   constraintPk_ordersPrimary Key(orderid,orderdate)) onPs_orderdate (OrderDate)--This sentence determines the difference between this tableGoCreate TableOrdershistory--The Order History table can put infrequently used data in this table--this will greatly improve the query for orders, and when necessary, all data is queried through union ALL(OrderIDint Identity(10000,1), OrderDatedatetime   not NULL, CustomerIDint  not NULL,   constraintPk_ordershistoryPrimary Key(orderid,orderdate)) onps_orderdate (OrderDate)Go

View Code

--inserting data into the table

--inserting data into a table UseSalesGO   INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2002/6/25', +)   INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2002/8/13', +)   INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2002/8/25', +)   INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2002/9/23', +)GO  + UseSalesGOINSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2003/6/25', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2003/8/13', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2003/8/25', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2003/9/23', +)   GO  +GOINSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2006/6/25', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2007/8/13', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2008/8/25', +)INSERT  intoDbo. Orders (OrderDate, CustomerID)VALUES('2009/9/23', +)   GO 11000

View Code

--View Results

SELECT *  fromOrdersSELECT *  fromdbo. Ordershistory--View the number of data rows for each partitionSelect$partition. Pf_orderdate (OrderDate) asPatition,COUNT(*) Countrow fromdbo. OrdersGroup  by$partition. Pf_orderdate (OrderDate)--Verify partition FunctionSELECT$partition. Pf_orderdate ('2002') as 'The partition in which'UNION  All SELECT$partition. Pf_orderdate ('2003') as 'The partition in which'UNION  All SELECT$partition. Pf_orderdate ('2004') as 'The partition in which'

View Code

--Dump the second part of the data into the history table

-- dump the second part of the data into the history table  Use Sales Go Alter Table 2  to 2 Go

View Code

--View Results

-- View Results SELECT *  from  SELECT* from dbo. Ordershistory

View Code

SQL Script Collation series one table partition