SQL SERVER 表分区

浏览数：22 / 时间：2015年06月12日

一 .SQL SERVER 表分区介绍：

SQL Server  引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能……

二 .SQL SERVER 数据库表分区由三个步骤来完成:

1.创建分区函数

2.创建分区架构

3.对表进行分区

基于缓存更新机制，我使用时间来进行分区，这里大家根据业务的要求使用合适的字段来作为分区

创建数据库分区文件数量，这里存储一年的数据分成十二个分区，需要现在D盘建立好Data 的文件夹 里面包含Primary 文件夹和 FG1 FG2 FG3 FG4............

 

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N‘AirAvCache‘)  
DROP DATABASE [AirAvCache]  
GO  
CREATE DATABASE [AirAvCache]  
ON PRIMARY 
(NAME=‘Data Partition DB Primary FG‘,  
FILENAME=  
‘D:\Data\Primary\AirAvCache Primary FG.mdf‘,  
SIZE=5,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
FILEGROUP [AirAvCache FG1]  
(NAME = ‘AirAvCache FG1‘,  
FILENAME =  
‘D:\Data\FG1\AirAvCache FG1.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
FILEGROUP [AirAvCache FG2]  
(NAME = ‘AirAvCache FG2‘,  
FILENAME =  
‘D:\Data\FG2\AirAvCache FG2.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
FILEGROUP [AirAvCache FG3]  
(NAME = ‘AirAvCache FG3‘,  
FILENAME =  
‘D:\Data\FG3\AirAvCache FG3.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
FILEGROUP [AirAvCache FG4]  
(NAME = ‘AirAvCache FG4‘,  
FILENAME =  
‘D:\Data\FG4\AirAvCache FG4.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
FILEGROUP [AirAvCache FG5]  
(NAME = ‘AirAvCache FG5‘,  
FILENAME =  
‘D:\Data\FG5\AirAvCache FG5.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG6]  
(NAME = ‘AirAvCache FG6‘,  
FILENAME =  
‘D:\Data\FG6\AirAvCache FG6.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
 
FILEGROUP [AirAvCache FG7]  
(NAME = ‘AirAvCache FG7‘,  
FILENAME =  
‘D:\Data\FG7\AirAvCache FG7.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG8]  
(NAME = ‘AirAvCache FG8‘,  
FILENAME =  
‘D:\Data\FG8\AirAvCache FG8.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG9]  
(NAME = ‘AirAvCache FG9‘,  
FILENAME =  
‘D:\Data\FG9\AirAvCache FG9.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG10]  
(NAME = ‘AirAvCache FG10‘,  
FILENAME =  
‘D:\Data\FG10\AirAvCache FG10.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG11]  
(NAME = ‘AirAvCache FG11‘,  
FILENAME =  
‘D:\Data\FG11\AirAvCache FG11.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 ),  
 
FILEGROUP [AirAvCache FG12]  
(NAME = ‘AirAvCache FG12‘,  
FILENAME =  
‘D:\Data\FG12\AirAvCache FG12.ndf‘,  
SIZE = 5MB,  
MAXSIZE=500,  
FILEGROWTH=1 )

 

 

 

创建好后如图：

打开FG1 文件夹 看到多了AirAvCacheFG1.ndf 文件

创建分区函数

USE AirAvCache  
GO   
 
-- 创建函数  
 
CREATE PARTITION FUNCTION [AirAvCache Partition  Range](DATETIME)  
AS RANGE LEFT FOR VALUES (‘2010-09-01‘,‘2010-10-01‘,‘2010-11-01‘,
‘2010-12-01‘,‘2011-01-01‘,‘2011-02-01‘,‘2011-03-01‘,‘2011-04-01‘,
‘2011-05-01‘,‘2011-06-01‘,‘2010-07-01‘);

 

创建分区架构

CREATE PARTITION SCHEME [AirAvCache Partition Scheme]  
AS PARTITION [AirAvCache Partition  Range]  
TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],  
[AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);

 

创建一个使用AirAvCache Partitiion Scheme 架构的表

CREATE TABLE [dbo].[AvCache](  
    [CityPair] [varchar](6)  NOT NULL,  
    [FlightNo] [varchar](10)  NULL,  
    [FlightDate] [datetime] NOT NULL,  
    [CacheTime] [datetime] NOT NULL   DEFAULT (getdate()),  
    [AVNote] [varchar](300)  NULL 
)  ON [AirAvCache Partition Scheme] (FlightDate);   
--注意这里使用[AirAvCache Partition Scheme]架构，根据FlightDate 分区

 

查询分区情况

  1. -- 查看使用情况   
SELECT *, $PARTITION.[AirAvCache Partition  Range](FlightDate)  
FROM dbo.AVCache

     

可以看到9 月和 10 月已经分开了。

 

SQL SERVER 表分区,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： class style src 文件 使用 数据 Go com class style src 文件 使用 数据 Go com

相关文章

随机文章

您可能还喜欢

您可能还喜欢