|
http://www.cnblogs.com/libingql/p/4087598.html
往年的数据几乎不做操作或只做查询操作，这种情况可以使用分区表。
分区可以对表的操作通过并行的方式进行，可以提升访问性能。
--这里需要注意分区表不能再创建聚集索引，
--因为聚集索引可以将记录在物理上顺序存储，
--而分区表是将数据存储在不同的表中，这两个概念是冲突的，
--所以在创建分区表时不能再创建聚集索引。
--如果：你要分2个区，你要一共有3个文件，多出来那个是为了准备给下一个分区用的。
一. 创建分区表步骤
创建分区表的步骤分为5步：
（1）创建数据库文件组
（2）创建数据库文件
（3）创建分区函数
（4）创建分区方案
（5）创建分区表
1、创建 数据库文件组
2、创建 数据库文件
--查看数据库文件组
select * from sys.filegroups
3、创建 分区函数
--Function_DateTime 分区函数名称
create partition function Function_DateTime(DATETIME)
AS RANGE RIGHT
for values('2010-01-01','2011-01-01') --values 每个分区指定的边界值
--查看分区函数
select * from sys.partition_functions
4、创建 分区方案
--分区方案的作用是将分区函数生成的分区映射到文件组中去，
--分区方案是让SQL Server将已分区的数据放在哪个文件组中。
--创建分区方案 Scheme_DateTime
create partition scheme Scheme_DateTime
as partition Function_DateTime --使用分区方案的分区函数的名称
TO (SECTION2010,SECTION2011,SECTION2012)--将分区映射到文件组
--如果：你要分2个区，你要一共有3个文件，多出来那个是为了准备给下一个分区用的。
--查看已创建的分区方案
SELECT * FROM sys.partition_schemes
5、创建分区表
CREATE TABLE [Order]
(
OrderID INT IDENTITY(1,1) NOT NULL,
UserID INT NOT NULL,
TotalAmount DECIMAL(18,2) NULL,
OrderDate DATETIME NOT NULL
) ON Scheme_DateTime ( OrderDate ) --分区方案Scheme_DateTime 分区列OrderDate
--这里需要注意分区表不能再创建聚集索引，
--因为聚集索引可以将记录在物理上顺序存储，
--而分区表是将数据存储在不同的表中，这两个概念是冲突的，
--所以在创建分区表时不能再创建聚集索引。
二、操作分区表
--Insert数据
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,10.00 ,'2009-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,20.50 ,'2009-12-31');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (2 ,40.00 ,'2010-01-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (3 ,40.00 ,'2010-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (4 ,50.00 ,'2011-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (6 ,70.00 ,'2013-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (10 ,90.00 ,'2014-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (9 ,100.00 ,'2015-10-20');
--查询数据所在物理分区表
--$PARTITION.分区函数名(表达式)
--调用分区函数并返回数据所在物理分区的编号
SELECT $PARTITION.Function_DateTime('2010-01-01')
--查看物理分区表中存放的记录
SELECT * FROM [Order] WHERE $PARTITION.Function_DateTime(OrderDate) = 1
SELECT * FROM [Order] WHERE $PARTITION.Function_DateTime(OrderDate) = 2
SELECT * FROM [Order] WHERE $PARTITION.Function_DateTime(OrderDate) = 3
SELECT $PARTITION.Function_DateTime(OrderDate) AS 分区编号, COUNT(1) AS 记录数
FROM [Order]
GROUP BY $PARTITION.Function_DateTime(OrderDate)
--修改分区表数据
UPDATE dbo.[Order] SET OrderDate='2015-01-01' WHERE OrderID = 3
--将普通表转换为分区表
删除表聚集索引，创建非聚集索引
CREATE TABLE Product
(
ProductID INT IDENTITY(1,1) NOT NULL,
ProductName VARCHAR(100) NOT NULL,
UnitPrice DECIMAL(18,2) NULL,
CreateDate DATETIME NOT NULL,
CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
)
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to SQL' ,10 ,'2012-01-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to XML' ,10 ,'2012-12-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Object' ,10 ,'2013-02-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to ADO.NET' ,10 ,'2014-01-02');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Entity' ,10 ,'2015-01-01');
--在SQL Server中，主键字段上默认创建聚集索引，删除主键的聚集索引。
ALTER TABLE 表名 DROP CONSTRAINT 索引名称
--重新创建主键非聚集索引.ProductID 字段
ALTER TABLE 表名 ADD CONSTRAINT 索引名称 PRIMARY KEY NONCLUSTERED (ProductID ASC)
--创建使用分区方案的聚集索引：
CREATE CLUSTERED INDEX IX_CreateDate ON 表名 ( CreateDate )
ON Scheme_DateTime ( CreateDate )
--删除（合并）一个分区表
--添加分区
|
转播
