use SalesDB1
go
-- 檢視表是否已經分區
SELECT *
FROM sys .tables AS t
JOIN sys .indexes AS i
ON t .[object_id] = i .[object_id] AND i .[type] IN ( 0,1 )
JOIN sys .partition_schemes ps
ON i .data_space_id = ps .data_space_id
WHERE t .name = 'table' ; -- 隻加表名不需要加上架構名
GO
-- 查詢庫中的那些表有分區
select tbl .name
from sys .partition_functions pf
join sys .partition_schemes ps on pf. function_id = ps. function_id
join sys .indexes idx on idx. data_space_id = ps. data_space_id and idx.index_id >1
join sys .tables tbl on idx. object_id = tbl. object_id
order by tbl. name
-- 确定已經分區的表的列:
SELECT
t.[object_id] AS ObjectID
, t .name AS TableName
, ic .column_id AS PartitioningColumnID
, c .name AS PartitioningColumnName
FROM sys .tables AS t JOIN sys.indexes AS i
ON t .[object_id] = i .[object_id] AND i .[type] <= 1 -- clustered index or a heap
JOIN sys .partition_schemes AS ps
ON ps .data_space_id = i .data_space_id
JOIN sys .index_columns AS ic
ON ic .[object_id] = i .[object_id]
AND ic .index_id = i .index_id AND ic .partition_ordinal >= 1 -- because 0 = non-partitioning column
JOIN sys .columns AS c
ON t .[object_id] = c .[object_id] AND ic .column_id = c .column_id
WHERE t .name = 'Table' ;
GO
-- 每個分區的邊界值
SELECT t .name AS TableName , i .name AS IndexName , p .partition_number, p.partition_id , i .data_space_id, f. function_id, f.type_desc , r.boundary_id , r .value AS BoundaryValue
FROM sys .tables AS t
JOIN sys .indexes AS i
ON t .object_id = i .object_id
JOIN sys .partitions AS p
ON i .object_id = p .object_id AND i .index_id = p .index_id
JOIN sys .partition_schemes AS s
ON i .data_space_id = s .data_space_id
JOIN sys .partition_functions AS f
ON s .function_id = f .function_id
LEFT JOIN sys. partition_range_values AS r
ON f .function_id = r .function_id and r .boundary_id = p.partition_number
WHERE t .name = 'Table' AND i.type <= 1
ORDER BY p. partition_number;
select * from sys.filegroups -- 檢視資料庫的檔案組
select * from sys.sysfiles -- 檢視資料庫檔案
select * from sys.partition_functions -- 檢視分區函數
select * from sys.partition_range_values -- 邊界值
select * from sys.partition_schemes --分區架構
-- 添加檔案組,并指定檔案組的檔案
ALTER DATABASE [Data Partition DB2]ADD FILEGROUP [Data Partition DB FG5]
ALTER DATABASE [Data Partition DB2]ADD FILE(NAME='Data Partition DB FG5',
FILENAME='D:\Database\Data Partition DB FG5.ndf') TO FILEGROUP [Data Partition DB FG5];
-- 添加分區函數,及分區架構
alter partition scheme ps_OrderDate next used [FG4]
alter partition function pf_OrderDate() split range ('2005/01/01')
--删除分區又稱合并分區,簡單講就是兩個分區的資料進行合并,比如我們想合并年的分區和年的分區到一個分區,我們可以用如下的代碼:
alter partition function pf_OrderDate() merge range ('2003/01/01')