优秀高端网站建设企业/个人发布信息的免费平台

对分区表，特别需要注意的是：

1、如果分区表，有一个聚集索引，当然肯定是包含了分区列，可以建立不包含分区列的唯一索引。

2、如果分区表，有一个主键聚集索引，那么建立不包含分区列的唯一索引，会报错。

3、如果这个表一开始是个普通表，先建立的唯一索引，那么再改造为分区表时，当然了，也就没有唯一索引报错的问题了。

但不管是上面的哪种情况，这个不包含分区列的唯一索引，都是个全局索引，如果进行了分区操作，那么这个唯一索引就会失效，必须要重建。

1、分区表提供了内建的方法，水平划分表和索引中的数据。水平分区是指每一个分区都有相同数量的列，只是减少了行的数量。分区使超大型表和索引的管理变的简单，减少加载时间，改善查询时间，允许更小的维护窗口。

--1.创建数据库 create database wc on primary ( 	name = wc_data, 	filename = 'D:\wc_data.mdf' ) log on ( 	name = wc_log1, 	filename = 'd:\wc_log1.ldf'   ), ( 	name = wc_log2, 	filename = 'd:\wc_log2.ldf' )   --2.增加文件组 alter database wc add filegroup wc_fg1  alter database wc add filegroup wc_fg2  alter database wc add filegroup wc_fg3  alter database wc add filegroup wc_fg4  --3.把文件添加到文件组中 alter database wc add file ( 	name = wc_fg1_1, 	filename = 'd:\wc_fg1_1.ndf', 	size = 1MB ) to filegroup wc_fg1  alter database wc add file ( 	name = wc_fg2_1, 	filename = 'd:\wc_fg2_1.ndf', 	size = 1MB ) to filegroup wc_fg2  alter database wc add file ( 	name = wc_fg3_1, 	filename = 'd:\wc_fg3_1.ndf', 	size = 1MB ) to filegroup wc_fg3  alter database wc add file ( 	name = wc_fg4_1, 	filename = 'd:\wc_fg4_1.ndf', 	size = 1MB ) to filegroup wc_fg4   --4.创建分区函数 create partition function wcLeftRange(datetime) as range left for values('2006-01-01','2007-01-01','2008-01-01')  create partition function wcRightRange(datetime) as range right for values('2006-01-01','2007-01-01','2008-01-01')   --5.创建分区方案 create partition scheme wcLeftRangeScheme as partition wcLeftRange to (wc_fg1,wc_fg2,wc_fg3,wc_fg4)   --6.创建分区表 create table dbo.wcT 	(wcId bigint not null, 	 wcV varchar(100) not null , 	 wcDate datetime not null, 	 constraint pk_wcid_date  	 primary key(wcId,wcDate) 	) on wcLeftRangeScheme(wcDate)   insert into dbo.wcT(wcId,wcV,wcDate) values(1,'2','2006-01-01 00:00:00'),       (2,'1','2005-12-31 23:59:59'),       (3,'2','2006-12-31 23:59:59'),       (4,'3','2007-01-01 00:00:00'),       (5,'4','2008-01-01 00:00:00'),       (6,'4','2008-12-31 23:59:59')   --7.显示每条数据所属分区号,从1开始计算 select *,         --$partition函数,后面是分区函数名称,列名称        $partition.wcLeftRange(wcDate) as partition  from wcT   --8.1再次增加文件组和文件 alter database wc add filegroup wc_fg5  alter database wc add file ( 	name = wc_fg5_1, 	filename = 'd:\wc_fg5_1.ndf', 	size = 1MB ) to filegroup wc_fg5  --8.2指定下一个要使用的分区文件组 alter partition scheme wcLeftRangeScheme next used [wc_fg5]  --8.3定义一个新的边界值来创建一个新的分区 alter partition function wcLeftRange() split range ('2009-01-01')   insert into wcT(wcId,wcV,wcDate) values(7,'5','2008-12-31 23:59:59'),       (8,'6','2009-01-01 23:59:59')   --8.4移除一个分区,其实就是把2个分区合并成一个分区,行重新分配到目标分区 alter partition function wcLeftRange() merge range ('2007-01-01')   --8.5新建一个历史表 create table wcThistory 	(wcId bigint not null , 	 wcV varchar(100) not null, 	 wcDate datetime not null, 	 constraint pk_wchistory 	 primary key (wcId,wcDate) 	) on [wc_fg3] 	       --8.6把源表的第2个分区转到目标表 --要求:源表和目标表必须在同一个文件组上,且目标表必须为空 --当然,目标表也可以是分区表,也必须为空,且在同一个文件组上 alter table wcT switch partition 2 to wcThistory   --9.显示分区表的分区信息 select *  from sys.partitions p inner join sys.allocation_units au         on p.hobt_id = au.container_id inner join sys.data_spaces ds         on ds.data_space_id = au.data_space_id where p.object_id = 213575799   --10.删除分区函数,分区方案 drop partition scheme wcLeftRangeScheme  drop partition function wcLeftRange 

在表已经存在的情况下，构建分区表，通过删除主键约束，重建主键约束，同时按照分区架构来建立聚集索引。当然，如果表本来就没有主键，只有一个聚集索引，那么可以直接删除聚集索引，然后在重建聚集索引时指定分区架构，其实就是索引分区，只不过这个索引里存储的是真正的表的数据。

--1.创建数据库 create database wc on primary ( 	name = wc_data, 	filename = 'D:\wc_data.mdf' ) log on ( 	name = wc_log1, 	filename = 'd:\wc_log1.ldf'   ), ( 	name = wc_log2, 	filename = 'd:\wc_log2.ldf' )   --2.增加文件组 alter database wc add filegroup wc_fg1  alter database wc add filegroup wc_fg2  alter database wc add filegroup wc_fg3  alter database wc add filegroup wc_fg4  --3.把文件添加到文件组中 alter database wc add file ( 	name = wc_fg1_1, 	filename = 'd:\wc_fg1_1.ndf', 	size = 1MB ) to filegroup wc_fg1  alter database wc add file ( 	name = wc_fg2_1, 	filename = 'd:\wc_fg2_1.ndf', 	size = 1MB ) to filegroup wc_fg2  alter database wc add file ( 	name = wc_fg3_1, 	filename = 'd:\wc_fg3_1.ndf', 	size = 1MB ) to filegroup wc_fg3  alter database wc add file ( 	name = wc_fg4_1, 	filename = 'd:\wc_fg4_1.ndf', 	size = 1MB ) to filegroup wc_fg4   --4.创建分区函数 use wc go  create partition function wcLeftRange(datetime) as range left for values('2006-01-01','2007-01-01','2008-01-01')  create partition function wcRightRange(datetime) as range right for values('2006-01-01','2007-01-01','2008-01-01')   --5.创建分区方案 create partition scheme wcLeftRangeScheme as partition wcLeftRange to (wc_fg1,wc_fg2,wc_fg3,wc_fg4)   --6.1创建表,下面是把已经存在的表改为分区表,其实分区表说到底就是对聚集索引的分区, --所以只要重建主键索引就可以了 create table dbo.wcT 	(wcId bigint not null, 	 wcV varchar(100) not null , 	 wcDate datetime not null, 	 constraint pk_wcid_date  	 primary key(wcId,wcDate) 	)  --6.2添加数据 insert into dbo.wcT(wcId,wcV,wcDate) values(1,'2','2006-01-01 00:00:00'),       (2,'1','2005-12-31 23:59:59'),       (3,'2','2006-12-31 23:59:59'),       (4,'3','2007-01-01 00:00:00'),       (5,'4','2008-01-01 00:00:00'),       (6,'4','2008-12-31 23:59:59')   --6.3现在需要把原表按照一个分区架构来分区 --6.3.1如果这么删除,会报错,因为这个索引正用于 PRIMARY KEY 约束的强制执行 drop index pk_wcid_date on dbo.wcT   --6.5.2重建聚集索引,操作失败，因为表'wcT'上已存在名称为'pk_wcid_date'的索引或统计信息。 create clustered index pk_wcid_date on wcT(wcId,wcDate)  on wcLeftRangeScheme(wcDate)   --6.3.3首先需要删除主键约束,应该这么写才是对的 alter table dbo.wcT drop constraint pk_wcid_date   --6.3.4再次建立主键约束,指定创建聚集索引,同时指定分区架构 alter table dbo.wcT add constraint pk_wcid_date primary key clustered (wcId,wcDate)  on wcLeftRangeScheme(wcDate)     --7.显示每条数据所属分区号,从1开始计算 select *,         --$partition函数,后面是分区函数名称,列名称        $partition.wcLeftRange(wcDate) as partition  from wcT   --8.1再次增加文件组和文件 alter database wc add filegroup wc_fg5  alter database wc add file ( 	name = wc_fg5_1, 	filename = 'd:\wc_fg5_1.ndf', 	size = 1MB ) to filegroup wc_fg5  --8.2指定下一个要使用的分区文件组 alter partition scheme wcLeftRangeScheme next used [wc_fg5]  --8.3定义一个新的边界值来创建一个新的分区 alter partition function wcLeftRange() split range ('2009-01-01')   insert into wcT(wcId,wcV,wcDate) values(7,'5','2008-12-31 23:59:59'),       (8,'6','2009-01-01 23:59:59')   --8.4移除一个分区,其实就是把2个分区合并成一个分区,行重新分配到目标分区 alter partition function wcLeftRange() merge range ('2007-01-01')   --8.5新建一个历史表 create table wcThistory 	(wcId bigint not null , 	 wcV varchar(100) not null, 	 wcDate datetime not null, 	 constraint pk_wchistory 	 primary key (wcId,wcDate) 	) on [wc_fg3] 	       --8.6把源表的第2个分区转到目标表 --要求:源表和目标表必须在同一个文件组上,且目标表必须为空 --当然,目标表也可以是分区表,也必须为空,且在同一个文件组上 alter table wcT switch partition 2 to wcThistory   --9.显示分区表的分区信息 select *  from sys.partitions p inner join sys.allocation_units au         on p.hobt_id = au.container_id inner join sys.data_spaces ds         on ds.data_space_id = au.data_space_id where p.object_id = 213575799   --10.删除分区函数,分区方案 drop partition scheme wcLeftRangeScheme  drop partition function wcLeftRange  

显示分区信息          

select t.name as '表名',        i.type_desc as '索引类型',        case when is_primary_key = 1 then '主键'             else '非主键'        end as '键',                ds.name as '分区方案名称',        ds.type_desc '对象类型',                case when ds.is_default = 1 then '默认文件组'             else '非默认文件组'        end as '是否默认文件组',                pf.name '分区函数',        pf.type_desc '分区函数类型',        pf.fanout '函数创建的分区数',        case when pf.boundary_value_on_right =1                  then '边界值包含在边界的right区域'             when pf.boundary_value_on_right = 0                  then '边界值包含在边界值的left区域'        end as '边界说明',                       prv.boundary_id as '边界id',        tp.name AS '边界值的数据类型',        prv.value '边界值'         from sys.tables t inner join sys.indexes i 	    on i.object_id = t.object_id 	     inner join sys.data_spaces ds         on ds.data_space_id = i.data_space_id          inner join sys.partition_schemes ps         on ds.data_space_id = ps.data_space_id  inner join sys.partition_functions pf         on pf.function_id = ps.function_id  inner join sys.partition_range_values prv         on prv.function_id = pf.function_id          inner join sys.partition_parameters pp         on pp.function_id = prv.function_id            and pp.parameter_id = prv.parameter_id  inner join sys.types tp         on tp.system_type_id = pp.system_type_id            and tp.user_type_id = pp.user_type_id where i.object_id = 213575799        and i.index_id = 1 

显示表名、分区方案、分区函数、边界值、文件组等信息，还有存储在分区的记录数据

select * from ( select t.name as table_name,            p.partition_number,        p.rows,                ps.name as partition_scheme_name,        pf.name as partition_function_name,        tp.name as partition_boundary_value_type,                dsp.name as filegroup_name,                --prv.boundary_id,        --prv.value,                case when pf.boundary_value_on_right =0                  and p.partition_number = 1                  and p.partition_number = boundary_id                  then '无穷小 < Value' + ' <= ' + convert(varchar(20),prv.value,120)                               when pf.boundary_value_on_right = 0                  and p.partition_number <> 1                  and p.partition_number = boundary_id                  then convert(varchar(20),                        (select value                          from sys.partition_range_values pfu                         where pfu.function_id =prv.function_id and                               pfu.boundary_id = prv.boundary_id -1                        ),120)                        + ' < Value' + ' <= ' + convert(varchar(20),prv.value,120)             when pf.boundary_value_on_right = 0                  and p.partition_number = pf.fanout                  and p.partition_number = prv.boundary_id + 1                  then convert(varchar(20),prv.value,120) + ' < Value <= ' + '无穷大'                             end as range_of_value          from sys.tables t inner join sys.indexes i         on t.object_id = i.object_id inner join sys.partitions p         on p.object_id = i.object_id            and p.index_id = i.index_id inner join sys.allocation_units au         on au.container_id = p.hobt_id  inner join sys.data_spaces ds         on ds.data_space_id = i.data_space_id inner join sys.partition_schemes ps         on ps.data_space_id = ds.data_space_id  inner join sys.partition_functions pf         on pf.function_id = ps.function_id inner join sys.partition_range_values prv         on prv.function_id = pf.function_id inner join sys.partition_parameters pp         on pp.function_id = prv.function_id            and pp.parameter_id = prv.parameter_id inner join sys.types tp         on tp.system_type_id = pp.system_type_id            and tp.user_type_id = pp.user_type_id  inner join sys.data_spaces dsp         on au.data_space_id = dsp.data_space_id  where i.index_id = 1       and p.object_id = 213575799 )a left join wcT w        on $partition.wcLeftRange(wcDate) = a.partition_number where a.range_of_value is not null

2、数据库的数据文件属于文件组，每一个数据库有一个主要的文件组，另外可以按需添加文件组。在超大型数据库中要为数据库添加新的文件组，通过把数据划分成几个阵列，使备份管理变的简单，并且潜在的提升了性能。

alter database wc add filegroup wc_fg6  alter database wc add file ( 	name= wc_fg6_1, 	filename = 'd:\wc_fg6_1.ndf' ) to filegroup wc_fg6   create table dbo.wcT1   (    vid int,    vv varchar(100)   ) on [wc_fg6]  --指定文件组名    create table dbo.wcT2   (    vid int,    vv varchar(100)   ) on [default] --指定是默认文件组   --当表中有text,ntext,image,varchar(max),nvarchar(max),varbinary(max),xml类型的列时， --可以指定TEXTIMAGE_ON,允许这些数据存储在独立的文件组中    create table dbo.wcT3   (    vid int,    vv varchar(max)   ) on [primary]          --主文件组primary，这里主文件组也是默认文件组     textimage_on [wc_fg6]       

select t.name,        i.name,                ps.name,        dsp.name from sys.tables t inner join sys.partitions p         on t.object_id = p.object_id inner join sys.indexes i         on p.object_id = i.object_id            and p.index_id = i.index_id  inner join sys.data_spaces ds         on ds.data_space_id = i.data_space_id  inner join sys.partition_schemes ps         on ps.data_space_id = ds.data_space_id  inner join sys.destination_data_spaces dds   --分区和文件组的对应关系          on dds.partition_scheme_id = ps.data_space_id            and dds.destination_id =  p.partition_number   --分区号             inner join sys.data_spaces dsp         on dds.data_space_id = dsp.data_space_id  --目的地文件组             where p.object_id = object_id('wcT') 

数据压缩减少磁盘空间使用 

--在创建表时通过with指定压缩选项 create table dbo.wcT4   (    vid int,    vv varchar(100)   ) on [default] --指定是默认文件组  with (DATA_COMPRESSION = ROW)  --页压缩包括行压缩、前缀压缩、字典压缩 alter table dbo.wcT4 rebuild with (DATA_COMPRESSION = PAGE)  --不压缩数据 alter table dbo.wcT4 rebuild with (DATA_COMPRESSION = NONE)  --估计数据压缩可以节省的空间 EXEC sys.sp_estimate_data_compression_savings 	@schema_name = 'dbo', 	@object_name = 'wcT4', 	@index_id = NULL, 	@partition_number = NULL, 	@data_compression = 'ROW'  --指定表的某个分区采用页级压缩 alter table wcT rebuild partition = 3 with (DATA_COMPRESSION = PAGE)	   --在创建分区表时指定各个分区的压缩级别 create table wcT6 	(wcId int not null,      wcDate datetime not null) on wcLeftRangeScheme(wcDate) with (DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 2),       DATA_COMPRESSION = ROW  ON PARTITIONS (4),       DATA_COMPRESSION = NONE ON PARTITIONS (3))   --分区号必须要加括号   

 查看分区是否压缩

select data_compression,        data_compression_desc from sys.partitions where object_id = 213575799