Como obter o esquema de partição para dados de fluxo de arquivos?

Versão curta: existe uma maneira de obter o esquema de partição para os dados FILESTREAM usados ​​por um índice clusterizado criado em uma tabela particionada?

Versão mais longa: digamos que você queira particionar uma tabela que armazena dados FILESTREAM, então a documentação diz:

CRIAR ÍNDICE (Transact-SQL)

Se a tabela for particionada, a cláusula FILESTREAM_ON deve ser incluída e deve especificar um esquema de partição de grupos de arquivos FILESTREAM que use a mesma função de partição e colunas de partição que o esquema de partição para a tabela. Caso contrário, um erro será gerado.

Então você cria grupos de arquivos para linhas e dados de fluxo de arquivos, uma função de partição e dois esquemas de partição (novamente um para as linhas e dados de fluxo de arquivos, respectivamente), assim:

USE [master]
GO

CREATE DATABASE [FSPartitionTest]
 ON  PRIMARY 
( NAME = N'FSPartitionTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FSPartitionTest.mdf'), 
 FILEGROUP [DataPartitionA] 
( NAME = N'Data_A', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Data_A.ndf'), 
 FILEGROUP [DataPartitionB] 
( NAME = N'Data_B', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Data_B.ndf'), 
 FILEGROUP [FSPartitionA] CONTAINS FILESTREAM  DEFAULT
( NAME = N'FS_A', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FS_A'), 
 FILEGROUP [FSPartitionB] CONTAINS FILESTREAM 
( NAME = N'FS_B', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FS_B')
 LOG ON 
( NAME = N'FSPartitionTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FSPartitionTest_log.ldf')
GO

USE [FSPartitionTest]
GO

CREATE PARTITION FUNCTION [APartitionFunction] (INT)  
AS RANGE LEFT FOR VALUES (1);
GO

CREATE PARTITION SCHEME [DataPartitionScheme]
AS PARTITION [APartitionFunction]
TO ([DataPartitionA], [DataPartitionB]);
GO

CREATE PARTITION SCHEME [FSPartitionScheme]
AS PARTITION [APartitionFunction]
TO ([FSPartitionA], [FSPartitionB]);
GO

CREATE TABLE [FilestreamTable] (
    [Partition] INT NOT NULL
    , [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
        CONSTRAINT [UX_FilestreamTable_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
    , [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
    , INDEX [UX_FilestreamTable_Partition_Id] UNIQUE CLUSTERED  (
                [Partition],
                [Id]
            ) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionScheme]
)  ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionScheme]
GO

Então você pode consultar os esquemas de partição e os índices assim:

SELECT *
FROM sys.partition_schemes

SELECT T.[name], I.[name], I.[data_space_id]
FROM sys.tables AS T
JOIN sys.indexes AS I
    ON T.[object_id] = I.[object_id] 

Ambos os esquemas de partição aparecem, ambos usam a mesma função de partição e o índice clusterizado faz referência ao DataPartitionSchemein sys.indexes. No entanto, o esquema de partição usado para os dados FILESTREAM não é referenciado. Neste caso, há apenas um outro esquema de partição usando a mesma função de partição e tendo grupos de arquivos filestream como alvos. Para qualquer cenário prático, estaríamos prontos. Ninguém adicionaria outro esquema de partição, certo?

CREATE PARTITION SCHEME [FSPartitionSchemeB]
AS PARTITION [APartitionFunction]
TO ([FSPartitionB], [FSPartitionA]);
GO

CREATE TABLE [FilestreamTableB] (
    [Partition] INT NOT NULL
    , [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
        CONSTRAINT [UX_FilestreamTableB_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
    , [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
    , INDEX [UX_FilestreamTableB_Partition_Id] UNIQUE CLUSTERED  (
                [Partition],
                [Id]
            ) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeB]
)  ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeB]
GO

CREATE PARTITION SCHEME [FSPartitionSchemeC]
AS PARTITION [APartitionFunction]
ALL TO ([FSPartitionA]);
GO

CREATE TABLE [FilestreamTableC] (
    [Partition] INT NOT NULL
    , [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
        CONSTRAINT [UX_FilestreamTableC_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
    , [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
    , INDEX [UX_FilestreamTableC_Partition_Id] UNIQUE CLUSTERED  (
                [Partition],
                [Id]
            ) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeC]
)  ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeC]
GO

Como podemos determinar qual tabela usa qual esquema de partição? Há alguma view que faça referência direta ao data_space_idusado por um índice para dados FILESTREAM?

Solução alternativa: Há uma coisa que notei, mas não tenho certeza se funciona 100% do tempo. A sys.partitionsvisualização tem uma filestream_filegroup_idcoluna:

SELECT T.[name]
    , I.[name]
    , I.[data_space_id]
    , P.[partition_number]
    , FG.[name]
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.filegroups AS FG
    ON P.[filestream_filegroup_id] = FG.[data_space_id]

A visualização destination_data_spacestambém tem uma coluna destination_id, que - suponho - corresponde a partition_number:

SELECT PS.[name]
    , DDS.[destination_id]
    , FG.[name]
FROM sys.partition_schemes AS PS
JOIN sys.destination_data_spaces AS DDS
    ON PS.[data_space_id] = DDS.[partition_scheme_id]
JOIN sys.filegroups AS FG
    ON DDS.[data_space_id] = FG.[data_space_id]

Então, acho que é possível verificar se os grupos de arquivos referenciados por destination_data_spacescorrespondem aos referenciados por partitionspara cada partição:

WITH A AS (
    SELECT T.[name] AS [Table]
        , I.[name] AS [Index]
        , PS.[name] AS [PartitionScheme] -- for row data
        , FSPS.[name] AS [FS_PartitionScheme] -- candidate
        , FG.[name] AS [FS_DestinationFilegroup] -- referenced by partition scheme
        , PFG.[name] AS [FS_PartitionFilegroup] -- referenced by partition
        , CASE WHEN FG.[name] = PFG.[name] THEN 1 ELSE 0 END AS [FilegorupsMatch]
        , COUNT(*) OVER (PARTITION BY T.[object_id], I.[index_id], FSPS.[data_space_id]) AS [NumPartitions]
        , SUM(CASE WHEN FG.[name] = PFG.[name] THEN 1 ELSE 0 END)  OVER (PARTITION BY T.[object_id], I.[index_id], FSPS.[data_space_id]) AS [SumMatches]
    FROM sys.tables AS T
    JOIN sys.indexes AS I
        ON T.[object_id] = I.[object_id]
    --partition scheme used for row data
    JOIN sys.partition_schemes AS PS
        ON I.[data_space_id] = PS.[data_space_id]
    --look for candidate partition schemes, that are used for FILESTREAM data
    --must use the same partition function
    JOIN sys.partition_schemes AS FSPS
        ON PS.[function_id] = FSPS.[function_id]
            AND PS.[data_space_id] <> FSPS.[data_space_id]
    --destination must be a FILESTREAM filegroup
    JOIN sys.destination_data_spaces AS DDS
        ON FSPS.[data_space_id] = DDS.[partition_scheme_id]
    JOIN sys.filegroups AS FG
        ON DDS.[data_space_id] = FG.[data_space_id]
            AND FG.[type_desc] = 'FILESTREAM_DATA_FILEGROUP'
    --get the partition where partition_number matches desination_id
    JOIN sys.partitions AS P
        ON I.[object_id] = P.[object_id]
            AND I.[index_id] = P.[index_id]
            AND P.[partition_number] = DDS.[destination_id]
    --find the filegroup referenced by the partition
    LEFT JOIN sys.filegroups AS PFG
        ON P.[filestream_filegroup_id] = PFG.[data_space_id]
)
SELECT [Table], [Index], [PartitionScheme], [FS_PartitionScheme]
FROM A
WHERE [NumPartitions] = [SumMatches]
GROUP BY [Table], [Index], [PartitionScheme], [FS_PartitionScheme]

Deve haver uma linha por Índice, a menos que algum palhaço faça isso:

CREATE PARTITION SCHEME [FSPartitionSchemeB2]
AS PARTITION [APartitionFunction]
TO ([FSPartitionB], [FSPartitionA]);
GO

Então, se alguém puder confirmar que de fato o partition_numbersempre corresponde ao destination_id, podemos pelo menos identificar o esquema de partição até uma definição única (excluindo NEXT USED), mas ainda não um . definitivo data_space_id.