読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

便利な SQL の関数とか、構文とか、その他色々まとめてみる - その5 ( SUBSTRING から STUFF へ ) -

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

 インデックスと付加列を列挙してみる - 都内で働く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 がリリースみたいな感じでしょう.... 無用な心配ですかね?

MCP教科書 SQL Server 2012 (試験番号:70-462)

MCP教科書 SQL Server 2012 (試験番号:70-462)

 
MCP教科書 SQL Server 2012(試験番号:70-462) (EXAMPRESS)

MCP教科書 SQL Server 2012(試験番号:70-462) (EXAMPRESS)

 
MCP教科書 SQL Server 2008 (試験番号:70-432)

MCP教科書 SQL Server 2008 (試験番号:70-432)