便利な SQL の関数とか、構文とか、その他色々まとめてみる - その16 ( STRING_AGG )

　v.Next CTP1.1 では便利な関数がいくつか追加されています。前回はその内3つを紹介しました。
ryuchan.hatenablog.com

　集約関数である STRING_AGG が追加されています。SQL Server 2016 までの SQL Server では FOR XML PATH('') を駆使して実現する必要がありました。サンプルは下記の通りです。各テーブルのインデックスの列および付加列の情報を出力するクエリです。

SELECT 
    [データベース名] = DB_NAME(),
    [インデックス名] = i.name,
    [インデックスタイプ] = i.type_desc,
    [スキーマ名] = s.name,
    [テーブル名] = t.name,
    [インデックスな列達] = SUBSTRING(indnm,1,LEN(indnm) - 1),
    [付加な列達] = SUBSTRING(incnm,1,LEN(incnm) - 1)
FROM 
    sys.indexes i
    INNER JOIN sys.tables t ON 
    i.object_id = t.object_id
    INNER JOIN sys.schemas s ON 
    t.schema_id = s.schema_id
    INNER JOIN sys.objects o ON 
    i.object_id = o.object_id
CROSS APPLY 
(
    SELECT 
        c.name + ','
    FROM 
        sys.index_columns ic
        INNER JOIN sys.columns c ON 
        ic.object_id = c.object_id AND 
        ic.column_id = c.column_id
    WHERE 
        i.object_id = ic.object_id AND 
        i.index_id = ic.index_id AND 
        ic.is_included_column = 0
    ORDER BY 
        ic.key_ordinal ASC
    FOR XML PATH('')
) AS cra1(indnm)
CROSS APPLY 
(
    SELECT 
        c.name + ','
    FROM 
        sys.index_columns ic
        INNER JOIN sys.columns c ON 
        ic.[object_id] = c.object_id AND 
        ic.column_id = c.column_id
    WHERE 
        i.object_id = ic.object_id AND 
        i.index_id = ic.index_id AND 
        ic.is_included_column = 1
    ORDER BY 
        ic.key_ordinal ASC
    FOR XML PATH('')
) AS cra2 (incnm)

※上記のクエリ内容について説明している記事は下記の通りです。
ryuchan.hatenablog.com
ryuchan.hatenablog.com

　STRING_AGG を使用すると下記のように記述することができます。スッキリとした感じですね。

SELECT 
    [データベース名] = DB_NAME(),
    [インデックス名] = i.name,
    [インデックスタイプ] = i.type_desc,
    [スキーマ名] = s.name,
    [テーブル名] = t.name,
    [インデックスな列達] = (SELECT 
                                STRING_AGG(c.name,',') WITHIN GROUP (ORDER BY ic.key_ordinal ASC)
                            FROM 
                                sys.index_columns ic
                                INNER JOIN sys.columns c ON 
                                ic.object_id = c.object_id AND 
                                ic.column_id = c.column_id
                            WHERE
                                i.object_id = ic.object_id AND 
                                i.index_id = ic.index_id AND
                                ic.is_included_column = 0),
       [付加な列達] = (SELECT 
                           STRING_AGG(c.name,',')  WITHIN GROUP (ORDER BY ic.key_ordinal ASC)
                       FROM 
                           sys.index_columns ic
                           INNER JOIN sys.columns c ON 
                           ic.[object_id] = c.object_id AND 
                           ic.column_id = c.column_id
                       WHERE 
                           i.object_id = ic.object_id AND 
                           i.index_id = ic.index_id AND 
                           ic.is_included_column = 1)
    FROM 
        sys.indexes i
        INNER JOIN sys.tables t ON 
        i.object_id = t.object_id
        INNER JOIN sys.schemas s ON 
        t.schema_id = s.schema_id
        INNER JOIN sys.objects o ON 
        i.object_id = o.object_id

　v.Next いい感じです。

最近欲しくなってきた。

ニンテンドークラシックミニ ファミリーコンピュータ