011_分区
011_分区
/*
1、一个文件或文件组不能由多个数据库使用。例如，任何其他数据库都不能使用包含 sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。
2、一个文件只能是一个文件组的成员。
3、一个文件组可以包含多个文件，一个数据表在创建时可以指定要将数据放在那一个文件组上，而没有办法指定是要放在哪一个文件上，文件组对组内的所有文件都使用按比例填充策略。
4、事务日志文件不能属于任何文件组。
注：一个文件组中有多个文件时，文件组对组内的所有文件都使用按比例填充策略。
如果文件 f1 有 100 MB 可用空间，文件 f2 有 200 MB 可用空间，则从文件 f1 中分配一个区，从文件 f2 中分配两个区，依此类推。这样，两个文件几乎同时填满，并且可获得简单的条带化。
分区建立在表的基础上，只有将分区建立在不同【磁盘】下的不同【文件组】下的【文件】下，才可以减轻I/O压力。
建立在同一个磁盘上的分区文件（无论是否为不同文件组），可以降低备份成本。
删除文件组，须达到以下条件：
1、修改分区表，使其不依赖分区方案。
2、删除分区方案（依赖要删除的文件组）。DROP PARTITION SCHEME [Part_func_scheme_Name]
删除文件,要先清空文件里的数据，删除之前数据一定要记得先备份，可将数据复制到其他表
DBCC SHRINKFILE (FileName, EMPTYFILE);
每个数据文件都会有GAM、SGAM、DCM、BCM、PFS页面
*/
--创建文件组
ALTER DATABASE TEST ADD FILEGROUP test_filegroup01
ALTER DATABASE TEST ADD FILEGROUP test_filegroup02
ALTER DATABASE TEST ADD FILEGROUP test_filegroup03
--删除文件组
ALTER DATABASE TEST REMOVE FILEGROUP test_filegroup03
--创建文件
ALTER DATABASE test ADD FILE
(NAME=N'test_filegroup01_file01',FILENAME=N'E:\sql_2008\test_filegroup01_file01.ndf',SIZE=3MB,FILEGROWTH=1MB)
TO FILEGROUP test_filegroup01
ALTER DATABASE test ADD FILE
(NAME=N'test_filegroup01_file02',FILENAME=N'E:\sql_2008\test_filegroup01_file02.ndf',SIZE=3MB,FILEGROWTH=1MB)
TO FILEGROUP test_filegroup01
ALTER DATABASE test ADD FILE
(NAME=N'test_filegroup02_file01',FILENAME=N'E:\sql_2008\test_filegroup02_file01.ndf',SIZE=3MB,FILEGROWTH=1MB)
TO FILEGROUP test_filegroup02
--删除文件
ALTER DATABASE TEST REMOVE FILE test_filegroup02_file01
--查看文件组和文件信息
SELECT a.name 文件组名,a.data_space_id 文件组id,a.type 文件组类型,b.name 文件逻辑名,b.filename 文件物理名,*
FROM sys.filegroups a,sys.sysfiles b
WHERE a.data_space_id=b.groupid
--创建分区函数--test_partifunc_01
CREATE PARTITION FUNCTION test_partifunc_01(int)
AS RANGE LEFT FOR VALUES(50)--分为两个区，<50和>=50,如果想分为三个区，则为values(50,100)
--创建分区方案(将分区函数，文件组附加到分区方案中)--test_sche_partifunc
CREATE PARTITION SCHEME test_sche_partifunc
AS PARTITION test_partifunc_01--分区函数
TO (test_filegroup01,test_filegroup02)
--创建待分区的表--test_patition
create TABLE test_patition(
id INT
,date nvarchar(20)
) ON test_sche_partifunc(id)--将按照id字段来分区
--向插入数据
DECLARE @tp INT=1
WHILE @tp<100
BEGIN
INSERT INTO test_patition VALUES(@tp,cast( floor(rand()*100) AS NVARCHAR(20)))
set @tp=@tp+1
END
--创建分区索引--[test_index_partition]
CREATE CLUSTERED INDEX [test_index_partition] ON [dbo].[test_patition] ([id])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [test_sche_partifunc]([id])
--注：使用分区索引查询，可以避免多个cpu操作多个磁盘时产生的冲突。
--分区拆分
--在分区函数中新增一个边界值，即可将一个分区变为2个。
alter partition function test_partifunc_01()
split range(N'50') --将第二个分区拆为2个分区
--注意：如果分区函数已经指定了分区方案，则分区数需要和分区方案中指定的文件组个数保持对应一致。
--合并分区
--与拆分分区相反，去除一个边界值即可。
alter partition function test_partifunc_01()
merge range(N'50') --将第二第三分区合并
--注意：如果分区函数已经指定了分区方案，则分区数需要和分区方案中指定的文件组个数保持对应一致。
--分区视图
--分区视图是先建立带有字段约束的相同表,而约束不同,例如,第一个表的id约束为0-50,第二表为>50
--创建完一系列的表之后，用union all 连接起来创建一个视图，这个视图就形成啦分区视同。
--查看在分区文件2上的数据 （test_partifunc_01是分区函数）
SELECT * FROM test_patition WHERE $PARTITION.test_partifunc_01(id)=2
--查看分区函数
SELECT * FROM sys.partition_functions
--查看普通函数
SELECT * FROM sys.objects WHERE type='p'
--查看分区方案
SELECT * FROM sys.partition_schemes
--查看边界值点
select * from sys.partition_range_values
EXEC [sys].[sp_helpdb] @dbname=test
select * FROM sys.system_internals_allocation_units
DBCC inD(TEST,test_patition,-1)
DBCC TRACEON(3604,-1)
DBCC PAGE(test,1,8,3)
-- 显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO off
--查看分区相关信息
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('test_patition'), NULL,NULL, 'detailed')
--查看分区信息
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
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
