SQL Server 2005 表分区操作详解

（一）：什么是分区表？为什么要用分区表？如何创建分区表？如果你的数据库中某一个表中的数据满足以下几个条件，那么你就要考虑创建分区表了。

1、数据库中某个表中的数据很多。

很多是什么概念？一万条？两万条？还是十万条、一百万条？这个，我觉得是仁者见仁、智者见智的问题。

当然数据表中的数据多到查询时明显感觉到数据很慢了，那么，你就可以考虑使用分区表了。

如果非要我说一个数值的话，我认为是100万条。

2、但是，数据多了并不是创建分区表的惟一条件，哪怕你有一千万条记录，但是这一千万条记录都是常用的记录，那么最好也不要使用分区表，说不定会得不偿失。

只有你的数据是分段的数据，那么才要考虑到是否需要使用分区表。

3、什么叫数据是分段的？这个说法虽然很不专业，但很好理解。

比如说，你的数据是以年为分隔的，对于今年的数据而言，你常进行的操作是添加、修改、删除和查询，而对于往年的数据而言，你几乎不需要操作，或者你的操作往往只限于查询，那么恭喜你，你可以使用分区表。

换名话说，你对数据的操作往往只涉及到一部分数据而不是所有数据的话，那么你就可以考虑什么分区表了。

那么，什么是分区表呢？简单一点说，分区表就是将一个大表分成若干个小表。

假设，你有一个销售记录表，记录着每个每个商场的销售情况，那么你就可以把这个销售记录表按时间分成几个小表，例如说5个小表吧。

2009年以前的记录使用一个表，2010年的记录使用一个表，2011年的记录使用一个表，2012年的记录使用一个表，2012年以后的记录使用一个表。

那么，你想查询哪个年份的记录，就可以去相对应的表里查询，由于每个表中的记录数少了，查询起来时间自然也会减少。

但将一个大表分成几个小表的处理方式，会给程序员增加编程上的难度。

以添加记录为例，以上5个表是独立的5个表，在不同时间添加记录的时候，程序员要使用不同的SQL 语句，例如在2011年添加记录时，程序员要将记录添加到2011年那个表里；在2012年添加记录时，程序员要将记录添加到2012年的那个表里。

实验1 SQL Server 2005基本表操作一、实验目的1．掌握使用Management Studio创建和修改表结构。

2．掌握使用Management Studio进行索引操作。

3．掌握使用使用SQL语句创建和修改表结构。

4．掌握使用SQL语句进行索引操作。

5．掌握使用Management Studio添加、修改和删除数据。

二、实验要求1．分别使用Management Studio图形界面和SQL语句完成创建、修改和删除表结构等操作。

2．分别使用Management Studio图形界面和SQL语句完成索引的创建、修改和删除等操作。

3．使用Management Studio图形界面完成数据的添加、修改和删除操作。

4．请参考如下表结构完成以上操作。

现有学生管理数据库，数据库名为ST，包含学生信息、课程信息、教师信息、选课信息以及教学任务信息。

数据库ST包含下列5个表。

（1）StudentInfo: 学生信息表。

（2）CourseInfo: 课程信息表。

（3）TeacherInfo: 教师信息表。

（4）SC: 选课信息表。

（5）TeachTasksInfo: 教学任务信息表。

各表的结构和表中数据示例分别如表3-1至表3-10所示。

表3-1 StudentInfo表结构表名：StudentInfo 说明：学生基本信息表属性列数据类型长度空值列约束说明Sno char 12 Not Null PK 学生学号Sname nvarchar 20 Null 学生姓名Sex char 2 Null 学生性别Birthday date Null 出生日期Depart nvarchar 30 Null 学生系别Major nvarchar 50 Null 学生专业LengSch tinyint Null 学生学制表3-2 StudentInfo表中数据示例Sno Sname Sex Birthday Depart Major LengSch 201001903051 辛月娟女1990-4-27 经济管理系工商管理(本科) 4201001903057 谭文娟女1990-11-12 计算机系计算机科学与技术（本科） 4 201001903029 明梅女1991-3-15 计算机系计算机科学与技术（本科） 4 201011002031 徐雁男1989-07-02 计算机系信息管理与信息系统(1＋3) 4 201011002032 秦赵璇女1989-08-21 计算机系信息管理与信息系统(1＋3) 4 201011801017 王鹏飞男1991-1-5 服装系服装设计与工程(专科) 3 201011801018 哈菲菲女1991-9-9 服装系服装设计与工程(1＋3) 4表3-3 CourseInfo表结构表名：CourseInfo 说明：课程信息表属性列数据类型长度空值列约束说明Cno char 6 Not Null PK 课程编号Cname nvarchar 50 Null 课程名称Cpno char 6 Null 先行课Credit Decimal 2 Null 学分表3-4 CourseInfo表中数据示例Cno Cname Cpno Credit 140173 数据库系统概论140176 3140174 C语言程序设计 2140175 操作系统140176 3140176 数据结构140174 3.5140178 计算机英语 2140179 软件工程140173 2140180 计算机组成原理 4表3-5 TeacherInfo表结构表名：TeacherInfo 说明：教师信息表属性列数据类型长度空值列约束说明TeacherI D char5 Not Null PK职工号Tname nvarchar 20 Null 姓名Sex char 2 Null 性别Depart nvarchar 30 Null 所在院系ProfTitle nvarchar 10 Null 职称Degree char 6 Null 学位表3-6 TeacherInfo表中数据示例TeacherID Tname Sex Depart ProfTitle Degree 04001 白红霞女计算机系教授博士09001 安宁女计算机系讲师本科09004 董敏女化学系教授博士09006 冯李宁男计算机系副教授本科06067 范美丽女数学系讲师硕士09011 华玉山男计算机系助教硕士09016 王洪亮男计算机系副教授本科表3-7 TeachTasksInfo表结构表名：TeachTasksInfo 说明：教学任务表属性列数据类型长度空值列约束说明TeachTask ID char26 Not Null PK教学任务编号Cno char 6 Null 课程编号TeacherID char 5 Null 教师编号AcadYear char 9 Null 开设学年Term char 1 Null 开设学期ExamMeth od varchar10 Null考试方式Depart varchar 30 Null 开设部门TimeTable nvarchar 50 Null 上课时间表3-8 TeachTasksInfo表中数据示例TeachTaskID Cno TeacherID AcadYear Term ExamMethod Depart TimeTable(2009-2010-1)-1 40173-04001 140173 04001 2009-2010 1 考试计算机系周二第3,4节{第1-20周}(2009-2010-2)-1 40176-09006 140176 09006 2009-2010 2 考查计算机系周一第1,2节{第1-20周}(2009-2010-2)-1 40178-06067 140178 06067 2009-2010 2 考试数学系周三第1,2节{第1-20周}(2009-2010-1)-1 40180-09016 140180 09016 2009-2010 1 考查计算机系周三第5,6节{第1-20周};周五第3,4节{第1-20周}注：教学任务编号TeachTaskID的组成规则为（学年-学期）-课程号-教师号。

sql2005分区表功能的知识要点（一）：rangeleft与rangeright（一）.为范围分区创建分区函数范围分区必须使用边界条件进行定义。

而且，即使通过CHECK 约束对表进行了限制，也不能消除该范围任一边界的值。

为了允许定期将数据移入该表，需要创建最后一个空分区。

在范围分区中，首先定义边界点：如果存在五个分区，则定义四个边界点值，并指定每个值是第一个分区的上边界(LEFT) 还是第二个分区的下边界(RIGHT)。

根据LEFT 或RIGHT 指定，始终有一个空分区，因为该分区没有明确定义的边界点。

具体来讲，如果分区函数的第一个值（或边界条件）是'20001001'，则边界分区中的值将是：对于LEFT第一个分区是所有小于或等于'20001001' 的数据第二个分区是所有大于'20001001' 的数据对于RIGHT第一个分区是所有小于'20001001' 的数据第二个分区是所有大于或等于'20001001' 数据由于范围分区可能在datetime 数据中进行定义，因此必须了解其含义。

使用datetime 具有某种含义：即总是同时指定日期和时间。

未定义时间值的日期表示时间部分为“0”的12:00A.M。

如果将LEFT 与此类数据结合使用，则日期为10 月1 日12:00 A.M. 的数据将位于第一个分区，而10 月份的其他数据将位于第二个分区。

从逻辑上讲，最好将开始值与RIGHT 结合使用，而将结束值与LEFT 结合使用。

下面的三个子句将创建逻辑上相同的分区结构：RANGE LEFT FOR V ALUES ('20000930 23:59:59.997','20001231 23:59:59.997','20010331 23:59:59.997','20010630 23:59:59.997')或RANGE RIGHT FOR V ALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')或RANGE RIGHT FOR V ALUES ('20001001', '20010101','20010401', '20010701')注意：此处使用datetime 数据类型确实增加了一定的复杂性，但您需要确保设置正确的边界情况。

SQL SERVER 2005利用分区对大数据表处理操作手册超大型数据库的大小常常达到数百GB，有时甚至要用TB来计算。

而单表的数据量往往会达到上亿的记录，并且记录数会随着时间而增长。

这不但影响着数据库的运行效率，也增大数据库的维护难度。

除了表的数据量外，对表不同的访问模式也可能会影响性能和可用性。

这些问题都可以通过对大表进行合理分区得到很大的改善。

当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分来提高系统的运行效率。

如果系统有多个CPU或是多个磁盘子系统，可以通过并行操作获得更好的性能。

所以对大表进行分区是处理海量数据的一种十分高效的方法。

本文通过一个具体实例，介绍如何创建和修改分区表，以及如何查看分区表。

SQL Server 2005是微软在推出SQL Server 2000后时隔五年推出的一个数据库平台，它的数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。

此外SQL Server 2005结合了分析、报表、集成和通知功能。

这使企业可以构建和部署经济有效的BI解决方案，帮助团队通过记分卡、Dashboard、Web Services 和移动设备将数据应用推向业务的各个领域。

无论是开发人员、数据库管理员、信息工作者还是决策者，SQL Server 2005都可以提供出创新的解决方案，并可从数据中获得更多的益处。

它所带来的新特性，如T-SQL的增强、数据分区、服务代理和与.NetFramework的集成等，在易管理性、可用性、可伸缩性和安全性等方面都有很大的增强。

表分区的具体实现方法：表分区分为水平分区和垂直分区。

水平分区将表分为多个表。

每个表包含的列数相同，但是行更少。

例如，可以将一个包含十亿行的表水平分区成12个表，每个小表表示特定年份内一个月的数据。

任何需要特定月份数据的查询只需引用相应月份的表。

而垂直分区则是将原始表分成多个只包含较少列的表。

SqlServer2005对现有数据进行分区具体步骤RegMail是用来存放注册邮件的表,现以创建时间(CreateTime)字段来给表进行分区,具体步骤如下:--为分区创建存储文件ALTER DATABASE Test ADD FILEGROUP RegMailFile查看数据库的文件组能看到如下图:--为文件组设置存储文件ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2007', FILENAME ='E:\Data\RegMailFile2007.NDF') TO FILEGROUP RegMailFile;ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2008', FILENAME ='E:\Data\RegMailFile2008.NDF') TO FILEGROUP RegMailFile;ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2009', FILENAME ='E:\Data\RegMailFile2009.NDF') TO FILEGROUP RegMailFile;查看数据库的存储文件能看到如下图:--创建分区函数F EXISTS (SELECT*FROM sys.partition_functions WHERE name = N'test_partition')DROP PARTITION FUNCTION[test_partition]CREATE PARTITION FUNCTION pf_RegMail(datetime)ASRANGE RIGHT FOR VALUES ( ' 20070101 00:00:00 ' ,'20080101 00:00:00')创建完了在分区函数中可以看到刚创建好的pf_RegMail如果创建后想对分区函数进行修改可以用如下访求 :--修改分区函数(拆分)alter PARTITION FUNCTION pf_RegMail()split RANGE ('20090101 00:00:00');--修改分区函数(合并)ALTER PARTITION FUNCTION pf_RegMail()MERGE RANGE ('20080101 00:00:00');--创建分区方案/*看分区方案是否存在，若存在先drop掉*/IF EXISTS (SELECT*FROM sys.partition_schemes WHERE name = N'test_scheme') DROP PARTITION SCHEME test_schemeCREATE PARTITION SCHEME ps_RegMailAS PARTITION pf_RegMail TO (RegMail2007,RegMail2008,RegMail2009)CREATE PARTITION SCHEME MonthDateRangeSchemeASPARTITION MonthDateRangeALL TO ([PRIMARY])如果想去分区方案进行修改--修改分区方案ALTER PARTITION SCHEME ps_RegMailNEXT USED RegMail2010;--创建分区表CREATE TABLE [dbo].[PARTITIONERegMail]([id] [int] IDENTITY(1,1) NOT NULL,[CreateTime] [datetime] NOT NULLCONSTRAINT [PK_PARTITIONERegMail] PRIMARY KEY NONCLUSTERED([id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [ps_RegMail]([CreeateTime])--此为关键步骤,将现有数据存入上面所建的文件中ALTER TABLE [dbo].[RegMail]WITH NOCHECK ADDCONSTRAINT [PK_RegMail] PRIMARY KEY CLUSTERED([CreateTime]) ON [ps_RegMail]([CreateTime])--如果原来的表里有主键哪就要执行下面语句:alter table RegEmail drop constraint PK_RegEmail--将表的主键删除--4. --对已经存在的表进行分区设置ALTER TABLE dbo.d_depot_inoutDROP CONSTRAINT PK_d_depot_inoutWITH(MOVE TO ps_d_depot_inout(statdate))ALTER TABLE d_depot_inoutADDPRIMARY KEY NONCLUSTERED(depot,statdate,itemno)ON ps_d_depot_inout(statdate)GO--查寻数据所在文件组--最后看看结果。

实验1-SQL-Server 2005的基本操作实验背景SQL-Server是一种基于Windows操作系统的关系型数据库管理系统。

在实际开发工作中，我们需要对数据库进行增删改查等操作，因此学习SQL-Server的基本操作是非常必要的。

本实验将介绍SQL-Server 2005的基本操作，包括创建数据库、创建表、插入数据、查询数据等常用操作。

实验目的1.理解SQL-Server的基本概念和原理2.掌握SQL-Server 2005的基本操作3.能够利用SQL-Server 2005完成常用的数据操作实验步骤步骤一：创建数据库在SQL-Server 2005中，我们可以通过以下步骤来创建数据库：1.打开SQL-Server Management Studio软件2.在Object Explorer中，右击“Databases”文件夹，选择“New Database”3.在弹出的“New Database”对话框中，输入数据库的名称，选择数据库的文件路径和文件名等相关信息4.点击“OK”按钮，等待数据库创建完成步骤二：创建表在创建完数据库之后，我们需要在数据库中创建表。

创建表的步骤如下：1.在Object Explorer中，选择刚创建的数据库，右键选择“New Query”2.在新建的查询窗口中，输入以下SQL语句：CREATE TABLE [表名] ([列1名称] [列1类型] [列1属性],[列2名称] [列2类型] [列2属性],...)注：表名、列名、类型和属性可以根据实际情况进行修改。

3.执行SQL语句，等待表创建完成步骤三：插入数据创建表之后，我们需要向表中插入数据。

插入数据的步骤如下：1.在Object Explorer中，找到刚刚创建的表，右键选择“Edit Top 200Rows”或“Select Top 1000 Rows”2.在弹出的窗口中，输入需要插入的数据内容3.点击“保存”按钮，等待数据插入完成步骤四：查询数据插入数据之后，我们可以使用以下SQL语句来查询数据：SELECT [列1名称], [列2名称], ...FROM [表名]WHERE [条件]注：列名和表名可以根据实际情况进行修改，条件为可选项。

SQL Server 2005 中的分区表和索引SQL Server 2005发布日期: 3/24/2005 | 更新日期: 3/24/2005Kimberly L. Tripp 的创始人适用于：SQL Server 2005摘要：SQL Server 2005 中基于表的分区功能为简化分区表的创建和维护过程提供了灵活性和更好的性能。

追溯从逻辑分区表和手动分区表的功能到最新分区功能的发展历程，探索为什么、何时以及如何使用SQL Server 2005 设计、实现和维护分区表。

（本文包含一些指向英文站点的链接。

）关于本文本文所描绘的功能和计划是下一版本SQL Server 的开发方向。

它们并非本产品的说明书，如有更改，恕不另行通知。

对于最终产品是否具有这些功能不做任何明示或暗示的保证。

对于某些功能，本文假设读者熟悉SQL Server 2000 功能和服务。

有关背景信息，请访问SQL Server 网站或SQL Server 2000 资源工具包。

这并不是产品说明书。

下载相关的代码示例SQL2005PartitioningScripts.exe。

本页内容为什么要进行分区？分区的发展历史定义和术语创建分区表的步骤融会贯通：案例研究总结为什么要进行分区？什么是分区？为什么要使用分区？简单的回答是：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

通常，创建表是为了存储某种实体（例如客户或销售）的信息，并且每个表只具有描述该实体的属性。

一个表对应一个实体是最容易设计和理解的，因此不需要优化这种表的性能、可伸缩性和可管理性，尤其是在表变大的情况下。

大型表是由什么构成的呢？超大型数据库(VLDB) 的大小以数百GB 计算，甚至以TB 计算，但这个术语不一定能够反映数据库中各个表的大小。

大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。

这些要求也适用于表；如果其他用户的活动或维护操作限制了数据的可用性，则可以认为表非常大。

sqlserver中partition用法在SQL Server中，分区（Partition）是一种将表的数据分布在多个物理位置的技术，以便更有效地管理数据和访问速度。

通过分区，可以将表拆分为较小的逻辑部分，以便更方便地执行查询和管理操作。

以下是SQL Server中分区的一些常见用法：1、创建分区表：在创建分区表时，需要定义分区的数量和每个分区包含的列。

以下是一个创建分区表的示例：sqlCREATE TABLE PartitionedTable(Column1 INT,Column2 VARCHAR(50),...)WITH (DATA_COMPRESSION = PAGE)ON PartitionScheme (PartitionColumn) =(PARTITION_Scheme1 (01, 02, 03),PARTITION_Scheme2 (04, 05, 06),...);在上面的示例中，PartitionedTable 是要创建的分区表的名称，Column1 和Column2 是表中的列。

WITH (DATA_COMPRESSION = PAGE) 指定了使用页压缩来压缩数据。

ON PartitionScheme 指定了分区方案，其中PartitionColumn 是用于分区的列，而PARTITION_Scheme1 和PARTITION_Scheme2 是定义分区的方案和范围。

2、查询分区表：查询分区表时，可以使用分区键的值来确定要查询的分区。

以下是一个查询分区表的示例：sqlSELECT *FROM PartitionedTableWHERE PartitionColumn = '01'; --根据分区键的值筛选数据在上面的示例中，PartitionedTable 是已分区的表，PartitionColumn 是用于分区的列。

通过在WHERE 子句中使用适当的分区键值，可以仅查询特定的分区。

SQL Server 2000里的分区--（SQL Server 2005里面的分区技术，为大部分朋友所熟知，但对于SQL Server 2000里面的表分区，很多朋友可能有些迷糊，本方将为大家描述一下SQL Server 2000及SQL Server 20005的分区技术。

其实SQL Server 2000里面本没有真正的分区，但为了弥补这一缺陷，人们利用视图和触发器的组合，创造出一种分区方案，对于这样种分区方案，姑且称之为“伪分区”。

）---------------------------------------------------------------------准备1SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country='Germany'SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country='Mexico'GO--准备2CREATE VIEW v_Customers_Ger_MexASSELECT * FROM CustomersGerUNIONSELECT * FROM CustomersMexGO--创建INSTEAD OF触发器CREATE TRIGGER tr_Customers_Update ON v_Customers_Ger_MexINSTEAD OF UPDATEASDECLARE @Country nvarchar(15)SET @Country=(SELECT Country FROM inserted)IF @Country='Germany'BEGINUPDATE CustomersGer SET CustomersGer.Phone=Inserted.Phone FROM CustomersGerJOIN Inserted ON CustomersGer.CustomerID=Inserted.CustomerIDENDELSE IF @Country='Mexico'BEGINUPDATE CustomersMex SET CustomersMex.Phone=Inserted.Phone FROM CustomersMexJOIN Inserted ON CustomersMex.CustomerID=Inserted.CustomerIDENDGO--测试UPDATE v_Customers_Ger_Mex SET Phone='030-007xfxx' WHERE CustomerID='ALFKI'SELECT CustomerID,Phone FROM v_Customers_Ger_Mex WHERE CustomerID='ALFKI'SELECT CustomerID,Phone FROM CustomersGer WHERE CustomerID='ALFKI'SELECT CustomerID,Phone FROM CustomersMex WHERE CustomerID='ALFKI'GO--------------------------------------------------------------------- SQL Server 2005里的分区---------------------------------------------------------------------创建实验用数据库USE masterCREATE DATABASE Sales ON PRIMARY (NAME = 'Sales_Data',FILENAME='C:\Databases\Sales_dat.mdf', SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=10%),FILEGROUP FG1(NAME = 'File1',FILENAME = 'C:\Databases\File1_dat.ndf', SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 10%),FILEGROUP FG2(NAME = 'File2',FILENAME = 'C:\Databases\File2_dat.ndf', SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 10%),FILEGROUP FG3(NAME = 'File3',FILENAME = 'C:\Databases\File3_dat.ndf', SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 10%)LOG ON(NAME = 'Sales_Log',FILENAME = 'C:\Databases\Sales_Log.ldf', SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 10%)--创建分区函数，假定当前为2002年USE SalesCREATE PARTITION FUNCTION pf_OrderDate (datetime)AS RANGE RIGHT FOR VALUES('2003/01/01', '2004/01/01')GO--USE Sales--ALTER PARTITION FUNCTION pf_OrderDate ()--SPLIT RANGE ('2005/01/01')--GO--创建分区方案USE SalesGOCREATE PARTITION SCHEME ps_OrderDateAS PARTITION pf_OrderDate TO(FG1, FG2, FG3)GO--创建实验用数据表，并将其绑定到分区方案上USE SalesGOCREATE 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)GOCREATE 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-- SELECT * FROM sys.partition_schemes-- SELECT * FROM sys.partitions--向数据表中写入2002年的范例数据USE SalesGOINSERT 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)GOSELECT * FROM OrdersSELECT * FROM dbo.OrdersHistoryGO--2003年年初利用分区交换执行快速的数据归档USE SalesGOALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1 GOSELECT * FROM OrdersSELECT * FROM dbo.OrdersHistoryGO--可以通过以下代码确认数据归档是否成功：--SELECT * FROM dbo.Orders--SELECT * FROM dbo.OrdersHistory--向数据表中写入2003年的范例数据USE SalesGOINSERT 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)GOSELECT * FROM OrdersSELECT * FROM dbo.OrdersHistoryGO--2004年年初利用分区交换执行快速的数据归档USE SalesGOALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersHistory PARTITION 2 GOSELECT * FROM OrdersSELECT * FROM dbo.OrdersHistoryGO--对2002年数据分区和2003年数据分区进行合并USE SalesGOALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('2003/01/01')GO--SELECT * FROM sys.partition_schemes--SELECT * FROM sys.partitions--使用分区分裂功能准备2005年的数据分区USE SalesGOALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2005/01/01') GO--可使用以下代码检查各分区中的数据行数：SELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition,COUNT(*) AS [COUNT] FROM dbo.OrdersGROUP BY $PARTITION.pf_OrderDate(OrderDate)ORDER BY Partition ;GOSELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition,COUNT(*) AS [COUNT] FROM dbo.OrdersHistoryGROUP BY $PARTITION.pf_OrderDate(OrderDate)ORDER BY Partition ;GO-----------------------USE SalesGOINSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2004/6/25', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2004/8/13', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2004/8/25', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2004/9/23', 1000) GOINSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/6/25', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/8/13', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/8/25', 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/9/23', 1000) GO。

Sql2005数据表管理一：表的说明：1.表的概念表是关系模型中中表示实体的方式，是数据库存储数据的主要对象。

SQL Server数据库的表由行和列组成，行有时也称为记录，列有时也称为字段或域，如图下图所示。

在表中，行的顺序可以是任意的，一般按照数据插入的先后顺序存储。

在使用过程中，可以使用排序语句或按照索引对表中的行进行排序。

列的顺序也可以是任意的，对于每一个表，最多可以允许用户定义1024列。

在同一个表中，列名必须是唯一的，即不能有名称相同的两个或两个以上的列同时存在于一个表中，并且在定义时为每一个列指定一种数据类型。

但是，在同一个数据库的不同表中，可以使用相同的列名。

在SQL Server 2005系统中把表分成了4种类型，即普通表、临时表、已分区表和系统表。

普通表就是通常提到的数据库中存储数据的数据表，是最重要、最基本的表其他几种类型的表都是有特殊用途的表，往往是在特殊应用环境下，为了提高系统的使用效率而派生出来的。

临时表，顾名思义是临时创建的、不能永久生存的表。

临时表被创建之后，可以一直存储到SQL Server实例断开连接为止。

临时表又可以分为本地临时表和全局临时表，本地临时表只对创建者是可见的，全局临时表在创建之后对所有的用户和连接都是可见的。

已分区表是将数据水平划分成多个单元的表，这些单元可以分散到数据库中多个文件组里面，实现对单元中数据的并行访问。

如果表中的数据量非常庞大，并且这些数据经常被以不同的使用方式来访问，那么建立已分区表是一个有效的选择。

系统表储存了有关SQL Server服务器的配置、数据库配置、用户和表对象的描述等系统信息。

一般来说，只能由DBA 来使用系统表。

4. 语法特点1.不区分大小写2.没有”==”，’=’可以赋值也可以是相等的比较3.不区分字符和字符串。

常量可以用单引号引起来4.逻辑与(and),逻辑或(or),逻辑非(not)5.赋值运算符只有一个=6.没有自增自减表达式7.注释用—或者/* */来表示二：创建表：1.使用SSMS资源管理器创建表1)点击“开始/所有程序/sql server 2005/SQL ServerManagement Studio”，启动SSMS2)右键相应数据库下的表，新建表3)输入数据个字段名，数据类型4)保存，输入表名，完成2.主要列属性1)允许空（NULL或NOT NULL）该属性定义在输入数据时指定列是否可以为空。

SQL Server 2005------分区表和分区函数在谈论分区表这个话题之前，先和大家分享一个案例：2008年秋天的某天，我的团队接到成都市XX局一个SQL调优的ESS单子。

客户反映查询统计一次各地市局上报的数据汇总，需要6到15秒才能获得真正想要的数据，当我和销售人员赶到客户数据中心现场后，发现里面布置了很多柜式服务器，每台服务器都是8核16G内存。

和相关技术负责人沟通以及演示业务系统之后，可以肯定不是服务器性能的问题，我详细分析了他们的数据库，统计慢的几张表往往一周的上报数据便会增加1百多万行，导致他们这个系统刚上线没多久，某些表产生的数据已经在2000万行以上，最终我提出了优化方案，业务逻辑层采用存储过程代替普通的SQL语句，并启用相关开发平台的缓存技术；数据库系统中采用增强索引和规划分区表进行优化，最终问题解决。

事实上数据库性能优化是每个优秀的数据库工程师必须具备的素质之一，而这一节讨论的分区表便是性能调优的一种技术。

在企业级应用系统中，一个表存储2千万行的数据很常见，不可预期的数据也会在逐渐增长，所以数千万级别的表DBA会常常碰到，而TB级别的数据最终也在所难免，因此了解和掌握性能调优的18般兵器非常重要。

我计划用三篇博文介绍分区表这个主题，分别为：1，分区表理论解析2，实战分区表3，分区表前传大凡在应用系统和数据库系统中行走江湖多年的朋友，都会面临数据统计、分析以及归档的问题，企业信息化进程加速了各种数据的极具增长，商务智能(BI)的出现和实施着实给信息工作者和决策者带来了绝妙的体验，但从 OLTP 向 OLAP 系统加载数据是很头疼的事，常常需要数分钟或数小时,解决这一问题的技术之一便是分区表，一旦实施了分区表，这样的操作往往只需几秒钟，太让人兴奋了。

而大型表或索引经过分区后更容易进行管理，因为这样可以快速高效地管理和访问数据子集，同时维护数据集合的完整性。

分区表的数据分布于一个数据库中的多个文件组单元中，数据是按水平方式分区的（数据分区的多种方式会在分区表前传中阐述），因此一个表的某些行映射到某个分区，而另外一些行映射到另外某个分区，以此类推。

SQL2005分区表分区表有利于管理海量数据的表和索引,在分区中引入了一个分区键的概念,分区键用于根据某个区间值,特定值列表或散列函数执行数据的聚集.使用分区表有如下好处:1.提高数据的高用性:可用性的提高源自每个分区的独立性.优化器知道这种分区机制,会相应的从查询计划中除去未引用的分区.2.减轻管理员负担.3.改善某些查询性能,在只读查询的性能方面,分区对两类操作起作用.●分区消除:处理查询时,不考虑某些分区.●并行操作:并行全表扫描和并行索引区间扫描.4.减少资源竞争.脚本:--首先手工创建文件分组、物理文件；脚本方式创建后边介绍---- 创建分区函数--go--create partition function MineDateRange(datetime)--as--range right(left) for values (--'2010-01-01',--'2011-01-01',--'2012-01-01')--go------ 创建分区方案--go--create partition scheme Mine_Orders--as--partition MineDateRange--to (test2010, test2011, test2012,test2013)--go-- 创建分区表--go--create table dbo.OrdersTest--(-- OrderID int not null-- ,CustomerID varchar(10) not null-- ,EmployeeID int not null-- ,OrderDate datetime not null--)--on Mine_Orders(OrderDate)--go-- 创建聚集分区索引--create clustered index IXC_OrdersTest on dbo.OrdersTest(OrderDate) --go--插入数据--INSERT INTO [HyMineSecurityMonitor].[dbo].[OrdersTest]-- ([OrderID]-- ,[CustomerID]-- ,[EmployeeID]-- ,[OrderDate])-- VALUES-- (7-- ,'ffff',7-- ,'2015-10-10 12:20:23')--查询数据--select * from OrdersTest--查看每个分区的数据分布情况--SELECT partition = $partition .MineDateRange(OrderDate), rows = count(*), minval = min(OrderDate), maxval = max(OrderDate)--FROM dbo.OrdersTest--GROUP BY $partition .MineDateRange(OrderDate)--ORDER BY partition--------------------------------------------修改分区------------------------------------------------------添加文件分组--ALTER DATABASE HyMineSecurityMonitor ADD FILEGROUP [test2014]--添加物理文件--ALTER DATABASE HyMineSecurityMonitor--ADD FILE--(NAME = N'test2014',FILENAME =N'D:\DataBase\testDB\test2014.ndf',SIZE = 5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)--TO FILEGROUP [test2014]--修改分区函数新增一个分区--go--alter partition function MineDateRange()--split range('2013-01-01')--go--修改分区方案新增一个文件--go--alter partition scheme Mine_Orders next used [test2014]--go--修改分区函数合并一个分区--go--alter partition function PF_Orders_OrderDateRange()--merge range('2013-01-01')--go--------------------------------------------------------分区表数据迁移---------------------------------------------SQL Server 2005 分区表分区切换的三种形式：----1. 切换分区表的一个分区到普通数据表中：Partition to Table；（普通表：dbo.Orders_1998）-- create table dbo.Orders_1998-- (-- OrderID int not null-- ,CustomerID varchar(10) not null-- ,EmployeeID int not null-- ,OrderDate datetime not null-- ) on [test2012]---- alter table dbo.OrdersTest switch partition 3 to dbo.Orders_1998--1). 普通表必须建立在分区表切换分区所在的文件组上。

SQLServer数据库分区分表（⽔平分表）详细步骤⽬录1、需求说明2、实现思路2.1分区原理2.2 ⽔平分区优点2.3 实现思路3、实现步骤3.1代码创建分区表3.1.1 创建数据库3.1.2 添加⽂件组3.1.3 添加⽂件3.1.4 定义分区函数3.1.5 定义分区架构3.1.6 定义分区表3.2 界⾯向导表分区3.2.1 创建数据库3.2.2 创建⽂件组3.2.3 添加⽂件3.2.4 定义分区表3.2.5 添加分区函数和分区架构3.3 动态添加分割点4、测试数据4.1 添加测试数据4.1.1 新建测试表4.1.2 编写T-SQL添加测试数据5、补充说明5.1 分区分表理解5.2 ⽔平分区分表疑惑5.3 其它说明1、需求说明将数据库Demo中的表按照⽇期字段进⾏⽔平分区分表。

要求数据⽂件按⼀年⼀个⽂件存储，且分区的分割点会根据时间的增长⾃动添加（例如现在是2017年1⽉1⽇，将其作为⼀个分割点，即将2017年1⽉1⽇之前的数据存储到数据⽂件A中，将2017年1⽉1⽇的之后的数据存储到数据⽂件B中；当时间到2018年1⽉1⽇时，⾃动将2018年1⽉1⽇添加为⼀个新的分区分割点，并将2017年1⽉1⽇⾄2018年1⽉1⽇的数据存储在数据⽂件B中，将2018年1⽉1⽇之后的数据存储在⼀个新的数据⽂件C中，以此类推）。

2、实现思路2.1分区原理要实现这⼀功能，⾸先要了解数据库对⽔平分区表进⾏分区存储的原理。

所谓⽔平分区分表，就是把逻辑上的⼀个表，在物理上按照你指定的规则分放到不同的⽂件⾥，把⼀个⼤的数据⽂件拆分为多个⼩⽂件，还可以把这些⼩⽂件放在不同的磁盘下。

这样把⼀个⼤的⽂件拆分成多个⼩⽂件，便于我们对数据的管理。

2.2 ⽔平分区优点l 便于存档l 便于管理：备份恢复时可以单⼀的备份或者恢复某⼀个分区l 提⾼可⽤性：⼀个分区故障，不影响其他分区的正常使⽤l 提⾼性能：提升查询数据的速度2.3 实现思路①创建数据库②在创建的数据库中添加⽂件组③在⽂件组中添加新的⽂件④定义分区函数⑤定义分区架构⑥定义分区表⑦定义代理作业，⾃动添加分区分割点⑧测试数据注意：² 分区表依赖于分区架构，⽽分区架构⼜依赖与分区函数，所以在穿件分区函数、分区架构、分区表是要按照对应的顺序创建。