首页 新闻 搜索 专区 学院

求助哪位朋友知道:MS_SQL2005中分区方案与文件组的关联视图

0
悬赏园豆:100 [待解决问题]
需求:查询分区方案Fs_WriteLog所包含的文件组,
已知条件:
CREATE PARTITION SCHEME Fs_WriteLog AS PARTITION Fn_OneMonth(datetime) to (WriteLog1,WriteLog2)

需求详情:我现在知道有Fs_WriteLog分区方案,我想通过一种查询的方式知道该分区方案中包含的文件组名称
如上命令可以看出,该分区包含文件组WriteLog1,WriteLog2。

随心而为的主页 随心而为 | 初学一级 | 园豆:60
提问于:2011-06-27 15:06
< >
分享
所有回答(3)
0
邀月 | 园豆:25375 (高人七级) | 2011-06-28 09:17
0

--创建新的文件组
ALTER DATABASE [KYK_B2C_Publish]
ADD FILEGROUP Partition_File_1

ALTER DATABASE [KYK_B2C_Publish]
ADD FILE
(
 NAME='Test_Partition_Table_1',
 FILENAME='E:\database\Testdatabase\Test_Partition_Table_1.ndf',
 SIZE=200MB,
 FILEGROWTH=10MB
) TO FILEGROUP Partition_File_1

--创建分区函数
IF EXISTS(SELECT * FROM SYS.PARTITION_Functions WHERE NAME ='RangeByPassportID_PartFuncion')
DROP PARTITION FUNCTION RangeByPassportID_PartFuncion
CREATE PARTITION FUNCTION RangeByPassportID_PartFuncion(bigint) AS RANGE LEFT FOR
VALUES(100000,200000,300000,400000)
--创建分区结构
IF EXISTS(SELECT * FROM SYS.PARTITION_SCHEMES WHERE NAME='Partition_Scheme_Test')
DROP PARTITION SCHEME Partition_Scheme_Test
CREATE PARTITION SCHEME [Partition_Scheme_Test] AS PARTITION [RangeByPassportID_PartFuncion]
TO ([Partition_File_1],[Partition_File_2],[Partition_File_3],[Partition_File_4],[Partition_File_5])

按顺序做就可以了,这就是表分区。

菜鸟就是我 | 园豆:205 (菜鸟二级) | 2011-06-28 14:30
请大家把我的问题先弄清楚,谢谢你们的回答
支持(0) 反对(0) 随心而为 | 园豆:60 (初学一级) | 2011-07-06 17:43
0

坐等楼主给分。呵呵

select a.name as [partition name],c.name as [filegroup name] from sys.partition_schemes a inner join sys.destination_data_spaces b on a.data_space_id=b.partition_scheme_id inner join sys.filegroups c on b.data_space_id=c.data_space_id

where a.name='Fs_WriteLog' order by b.partition_scheme_id,b.destination_id

刘鸿海 | 园豆:655 (小虾三级) | 2011-07-21 11:26
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册