在好例子网,分享、交流、成长!
您当前所在位置:首页SQL 开发实例SQL基础 → SQL Server 2008 分区表实例

SQL Server 2008 分区表实例

SQL基础

下载此实例
  • 开发语言:SQL
  • 实例大小:8.30KB
  • 下载次数:57
  • 浏览次数:374
  • 发布时间:2016-01-22
  • 实例类别:SQL基础
  • 发 布 人:hnfjj
  • 文件格式:.txt
  • 所需积分:2
 相关标签: 实例 sql SQL Server Server 2

实例介绍

【实例简介】
【实例截图】

【核心代码】SQL Server 2008 分区表实例

--本文演示了 SQL Server 2008 分区表实例:
--1.  创建测试数据库 ;
--2.  创建分区函数;
--3.  创建分区架构;
--4.  创建分区表;
--5.  创建分区索引 ;
--6.  分区切换 ;
--7.  查询哪些表使用了分区表;
 
-- 创建测试数据库
USE master
GO 
IF OBJECT_ID(N'PartitionDataBase') IS NOT NULL
DROP DATABASE PartitionDataBase
GO
CREATE DATABASE PartitionDataBase
ON PRIMARY
(
NAME = N'File_A_H',
FILENAME ='D:\Data\PartitionDataBase_AH.mdf'
),
FILEGROUP FileGroup_I_N
(
NAME = N'File_I_N',
FILENAME ='D:\Data\PartitionDataBase_IN.mdf'
),
FILEGROUP FileGroup_M_Z
(
NAME = N'File_M_Z',
FILENAME ='D:\Data\PartitionDataBase_MZ.mdf'
)
GO
-- 创建分区函数
USE PartitionDataBase;
GO
CREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100))
AS
RANGE LEFT FOR VALUES ('H','M')
GO
-- 创建分区架构
CREATE PARTITION SCHEME StaffNamePScheme
AS
PARTITION StaffNameRangePFN
TO ([PRIMARY], FileGroup_I_N, FileGroup_M_Z)
GO
 
-- 创建分区表
CREATE TABLE [dbo].[Staff] 
(
[StaffName] [varchar](100) NOT NULL
)
ON StaffNamePScheme ([StaffName])
GO
 
-- 插入测试数据1
INSERT INTO [dbo].[Staff]
SELECT FirstName FROM AdventureWorks.Person.Contact
 
-- 查看结果
SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
-- 插入测试数据2
INSERT INTO [dbo].[Staff]
SELECT AddressLine1 FROM AdventureWorks.Person.Address
-- 查看结果
SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
--   从结果看出,已经重新分区过了
-- 重新改变布局
Use master
GO
ALTER DATABASE PartitionDataBase ADD FILEGROUP FileGroup_0_9
GO
ALTER DATABASE PartitionDataBase
ADD FILE
(
NAME = N'File_0_9',FileName = 'D:\Data\PartitionDataBase.mdf'
)
TO FILEGROUP FileGroup_0_9
GO
Use PartitionDataBase
GO
ALTER PARTITION SCHEME StaffNamePScheme
NEXT USED FileGroup_0_9;
GO
ALTER PARTITION FUNCTION StaffNameRangePFN()
SPLIT RANGE ('A');
GO
 
-- 查看结果
SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
 
 
-- 创建聚集分区索引
CREATE CLUSTERED index IXC_Staff_StaffName on  dbo.Staff ( StaffName )
go
 
---  分区切换
-- 6.1切换分区表的一个分区到普通数据表中: Partition to Table
--首先建立普通数据表 dbo.StaffName_Num ,该用来存放表StaffName 数字STAFF的数据
-- 创建表
if OBJECT_ID('dbo.StaffName_Num') is not null
drop table dbo.StaffName_Num
go
create table dbo.StaffName_Num
(
   StaffName  varchar ( 100 )   not null
)
on [FileGroup_0_9]
go
 
--注意这里建表不能为空或primary,因为我们建的分区表不在primary文件组
--如果这样会出现
--消息 4939,级别 16,状态 1,第 1 行
--ALTER TABLE SWITCH 语句失败。索引'PartitionDataDB.dbo.StaffName_Num.IXC_StaffName_Num_StaffName_Num' 在文件组'PRIMARY' 中,而 索引'PartitionDataDB.dbo.Staff.IXC_Staff_StaffName' 的分区 1 在文件组'FileGroup_0_9' 中。

create clustered index IXC_StaffName_Num_StaffName_Num on dbo.StaffName_Num ( StaffName )
go

--这里的索引也要建立一样的,
--不然也会出现这样的提示
--消息 4913,级别 16,状态 1,第 1 行
--ALTER TABLE SWITCH 语句失败。表 'PartitionDataDB.dbo.Staff' 具有聚集索引 'IXC_Staff_StaffName',而表 'PartitionDataDB.dbo.StaffName_Num' 没有聚集索引。
 
--   开始切换,将数字开头的StaffName切换到刚才建立的表
alter table dbo.Staff switch partition 1 to dbo.StaffName_Num
 
-- 查看StaffName_Num表结果
select top 1 * from dbo.StaffName_Num
 
 
-- 查看StaffName表结果
select * from dbo.Staff where StaffName like '100' '%'
 
--满足条件:
--1. 普通表必须建立在分区表切换分区所在的文件组上。
--2. 普通表的表结构跟分区表的一致;
--3. 普通表上的索引要跟分区表一致。
--4. 普通表必须是空表,不能有任何数据。
--5. 切换普通表数据到分区,除了满足上面的 4 个条件外,
--   还要加上一条:普通表必须加上和分区数据范围一致的 check 约束条件。
 
-- 查看结果
--6.1 切换普通表数据到分区表的一个分区中: Table to Partition
--上面我们已经把分区表 切换 了,现在我们再切换回来:
alter table dbo.StaffName_Num switch to dbo.Staff partition 1
 
--但是,此时有错误发生:
--消息 4982,级别 16,状态 1,第 1 行
--ALTER TABLE SWITCH 语句失败。源表 'PartitionDataDB.dbo.StaffName_Num' 的
--检查约束所允许的值对于目标表 'PartitionDataDB.dbo.Staff' 中分区 1
--定义的范围是不允许的。

--这就奇怪了,
--能把数据从分区切换进来却切换不出去。
--出错信息中提示我们是普通表的 check constraint 跟
--分区表不一致。
--于是在普通表上建立 check constraint :
 
alter table dbo.StaffName_Num add constraint CK_Orders1998_StaffName
check ( left(StaffName,1) in (1,2,3,4,5,6,7,8,9) )
 
--切换普通表数据到分区,除了满足上面的 4 个条件外,
--还要加上一条:普通表必须加上和分区数据范围一致的 check 约束条件。
--6.3 切换分区表的分区到另一分区表: Partition to Partition
--这里已无再建分区表了,,
--实际上,分区表分区切换并没有真正去移动数据,
--而是 SQL Server 在系统底层改变了表的元数据。
--因此分区表分区切换是高效、快速、灵活的。
--利用分区表的分区切换功能,
--我们可以快速加载数据到分区表。
--卸载分区数据到普通表,
--然后 truncate 普通表,
--以实现快速删除分区表数据。
--快速归档不活跃数据到历史表。
 
7.查看哪些表使用了分区表
SELECT OBJECT_NAME (p. object_id ) AS ObjectName,
 i.name                   AS IndexName,
 p.index_id               AS IndexID,
 ds.name                  AS PartitionScheme,  
 p.partition_number       AS PartitionNumber,
 fg.name                  AS FileGroupName,
 prv_left.value           AS LowerBoundaryValue,
 prv_right.value          AS UpperBoundaryValue,
 CASE pf.boundary_value_on_right
 WHEN 1 THEN ' RIGHT '
            ELSE ' LEFT ' END     AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i. object_id = p. object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
UNION ALL
-- non-partitioned table/indexes
SELECT
      OBJECT_NAME (p. object_id )    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                         AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName, 
      NULL                         AS LowerBoundaryValue,
      NULL                         AS UpperBoundaryValue,
      NULL                         AS Boundary,
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i. object_id = p. object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;


------------------------------------------------------------------------------

--详细的分区表与索引可以阅读:
--http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
 
--参考:
--http://www.cnblogs.com/goodspeed/archive/2005/11/07/270759.html


实例下载地址

SQL Server 2008 分区表实例

不能下载?内容有错? 点击这里报错 + 投诉 + 提问

好例子网口号:伸出你的我的手 — 分享

网友评论

发表评论

(您的评论需要经过审核才能显示)

查看所有0条评论>>

小贴士

感谢您为本站写下的评论,您的评论对其它用户来说具有重要的参考价值,所以请认真填写。

  • 类似“顶”、“沙发”之类没有营养的文字,对勤劳贡献的楼主来说是令人沮丧的反馈信息。
  • 相信您也不想看到一排文字/表情墙,所以请不要反馈意义不大的重复字符,也请尽量不要纯表情的回复。
  • 提问之前请再仔细看一遍楼主的说明,或许是您遗漏了。
  • 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦,又没人会搭理,于人于己都无利。

关于好例子网

本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享,对搜索内容的合法性不具有预见性、识别性、控制性,仅供学习研究,请务必在下载后24小时内给予删除,不得用于其他任何用途,否则后果自负。基于互联网的特殊性,平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查;无论平台是否已进行审查,用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场,基于网友分享,根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定,若资源存在侵权或相关问题请联系本站客服人员,点此联系我们。关于更多版权及免责申明参见 版权及免责申明

;
报警