创建分区表，提高查询效率

CREATE DATABASE Sales ON PRIMARY
(
    NAME = N'Sales',
    FILENAME = N'd:\DATA\Sales.mdf',
    SIZE = 3MB,
    MAXSIZE = 100MB,
    FILEGROWTH =10%
    ),
    FILEGROUP FG1
    (
    NAME = N'File1',
    FILENAME = N'd:\DATA\File1.ndf',
    SIZE = 1MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
    ),
    FILEGROUP FG2
    ( 
    NAME = N'File2',
    FILENAME = N'd:\DATA\File2.ndf',
    SIZE = 1MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
    ),
    FILEGROUP FG3
    (
    NAME = N'File3',
    FILENAME = N'd:\DATA\File3.ndf',
    SIZE = 1MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
    )
 LOG ON 
 (
   NAME = N'Sales_Log',
   FILENAME = N'd:\DATA\Sales_Log.ndf',
   SIZE = 1MB,
   MAXSIZE = 100MB,
   FILEGROWTH = 10%
   )
    GO 
 
 ---------------------------------------------how 
 USE Sales
 GO 
 CREATE PARTITION FUNCTION pf_OrderDate(DATETIME)
 AS RANGE RIGHT
 FOR VALUES('2003/01/01', '2004/01/01') 
 GO
 
 ------------------------------ where 
 USE Sales 
 GO
 CREATE PARTITION SCHEME ps_OrderDate
 AS PARTITION pf_OrderDate
 TO (FG1,FG2, FG3)
 GO
 
 ------------------------------ create partion table -----
 USE Sales 
 GO
 CREATE TABLE dbo.Orders 
 (
    OrderID INT IDENTITY(10000,1),
    OrderDate DATETIME NOT NULL,
    CustomerID INT NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
 ) ON ps_OrderDate(OrderDate)
 
 GO
 CREATE TABLE dbo.OrdersHistory
 (
    OrderID INT IDENTITY(10000, 1),
    OrderDate DATETIME NOT NULL,
    CustomerID INT NOT NULL,
    CONSTRAINT PK_OrdersHistory PRIMARY KEY (OrderID, OrderDate)
    )ON ps_OrderDate(OrderDate)
    GO
    
 
 ----------------------------------------- Insert 2002 data --
 USE Sales 
 GO 
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/6/25', 1000)    
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/13', 1000)    
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/25', 1000)    
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/9/23', 1000) 
GO 

--------------------------------- Insert 2003 data ------------------
USE Sales    
GO 
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/6/25', 1000) 
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/13', 1000) 
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/25', 1000) 
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/9/23', 1000)    
GO

------------------------ 归档数据 ------------------------------------
USE Sales 
GO
ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1
GO 

--------------------------- 添加新分区 --------------------------------

USE Sales 
GO 
ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2 
ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE('2006/01/01')
GO 

----------------------------
SELECT * FROM sys.partition_functions 
SELECT * FROM sys.partition_range_values 
SELECT * FROM sys.partition_schemes