【SQL Server高可用性】分区表、数据库文件组
对分区表，特别需要注意的是：
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
--这个很重要
use wc
go
--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
