积极答复者
请问能够加一列文件组列吗?

问题
-
如下面的SQL语句,能够看出分区情况和数据分布情况
--分区情况
SELECT $PARTITION.Fun_ClassifyResult_ClassId(classid) AS 分区编号 ,
MIN(classid) AS Min_value ,
MAX(classid) AS Max_value ,
COUNT(classid) AS 记录数
FROM [dbo].[ClassifyResult]
GROUP BY $PARTITION.Fun_ClassifyResult_ClassId(classid)
ORDER BY $PARTITION.Fun_ClassifyResult_ClassId(classid)我现在想加入一列,文件组列,就是分区对于哪个文件组
找到两个视图,但是不知道怎么连接
SELECT * FROM sys.[filegroups]
SELECT * FROM sys.[sysfilegroups]
答案
全部回复
-
----分区情况
SELECT c.* ,
b.[groupname] AS '分区方案对应的文件组名称' ,
d.name '当前分区函数对应的分区方案'
FROM sys.destination_data_spaces AS a
INNER JOIN sysfilegroups AS b ON a.[data_space_id] = b.[groupid]
INNER JOIN ( SELECT $PARTITION.Fun_ClassifyResult_ClassId(classid) AS 分区编号 ,
MIN(classid) AS Min_value ,
MAX(classid) AS Max_value ,
COUNT(classid) AS 记录数
FROM [dbo].[ClassifyResult]
GROUP BY $PARTITION.Fun_ClassifyResult_ClassId(classid)
) AS c ON c.[分区编号] = a.[destination_id]
INNER JOIN sys.partition_schemes AS d ON a.[partition_scheme_id] = d.data_space_id
ORDER BY c.[分区编号]