便利な SQL の関数とか、構文とか、その他色々まとめてみる - その5 ( SUBSTRING から STUFF へ ) - - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　もうすぐ試験があるというのに、勉強せずにブログを書いています... 興味が湧かないと本当に何もしない性格です(笑)　

　インデックスと付加列を列挙してみる - 都内で働くSEの技術的なひとりごとで記述したクエリを改善します。STUFF (Transact-SQL) 関数を使って少し書き直してみましょう。前回記事のクエリは下記の通りです。

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
    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
    FOR XML PATH('')
) AS cra2 (incnm)
WHERE o.modify_date > GETDATE() - 10

　変更した個所に下線を引っ張っています。SUBSTRING ではなく、STUFF 関数変更しています。CROSS APPLY 内で編集している個所は、列名の前にカンマを付加するように変えています。あとは、色々な情報をもってくるようにしています。( なんとなくほしいと思ったので.... )

SELECT [データベース名] = DB_NAME(),
       [スキーマ名] = s.name,
       [テーブル名] = t.name,
       [インデックス名] = i.name,
       [インデックスタイプ] = i.type_desc,
       [インデックスな列達] = STUFF(indnm,1,1,''),
       [付加な列達] = STUFF(incnm,1,1,''),
       [テーブルの概算件数] = ps.row_count,
       [スキャン数] = ius.user_scans,
       [最後にスキャンした日時] = ius.last_user_scan,
       [ルックアップ数] = ius.user_lookups,
       [最後にルックアップした日時] = ius.last_user_lookup,
       [統計数] = statsresult
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 
INNER JOIN sys.dm_db_partition_stats ps
    ON t.object_id = ps.object_id 
INNER JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.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
    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
    FOR XML PATH('')
) AS cra2 (incnm)
CROSS APPLY
(
    SELECT COUNT(name) 
    FROM sys.stats s
    WHERE s.object_id = i.object_id
) AS cra3 (statsresult)
Order by [スキーマ名],[テーブル名]

　SUBSTRING 版と STUFF 版を比較すると、予想通り STUFF 版のほうが2秒程度高速でした。記述も短縮できて、少しだけかっこよくなりましたね。監視系の SQL とはいえども、パフォーマンスに気を使うのは大事ですね。

　さぁ、試験勉強再開しよう.... もう眠いです.... もう寝ようかな.... もう諦めようかな.... MCP だったら気合入れて勉強できるんですけどね。そういえば、今後マイクロソフトって、バージョンアップ間隔を短くしていますが、MCP の資格とかそのバージョンアップに追いついていけるんでしょうか？現在の SQL Server のバージョンは、2014 で、来年には 2015 がリリースみたいな感じでしょう.... 無用な心配ですかね？