データベース管理者の必須スキル！SQL Serverでマテリアライズドビューを作成・更新・削除する方法

SQL Server でマテリアライズド ビューを作成する方法

マテリアライズド ビューの作成方法

CREATE MATERIALIZED VIEW materialized_view_name
AS
SELECT
    ...
FROM
    ...
[
    WITH (
        [オプション]
    )
];

オプション

• WITH (DISTRIBUTION = {PARTITION BY | HASH BY} ({column_list}))：マテリアライズド ビューのパーティション化方法を指定します。
• WITH (CLUSTERED COLUMNSTORE INDEX)：マテリアライズド ビューにクラスタ化された列ストア インデックスを作成します。
• WITH (NOEXPAND)：マテリアライズド ビューの定義に含まれる集計関数を展開しません。
• WITH (REFRESH ON COMMIT)：ベース テーブルがコミットされるたびにマテリアライズド ビューを自動的に更新します。
• WITH (START WITH {date_time})：指定した日時以降にのみマテリアライズド ビューを更新します。

マテリアライズド ビューは、手動で更新するか、REFRESH MATERIALIZED VIEW ステートメントを使用して自動的に更新することができます。

REFRESH MATERIALIZED VIEW materialized_view_name;

マテリアライズド ビューを削除するには、DROP MATERIALIZED VIEW ステートメントを使用します。

DROP MATERIALIZED VIEW materialized_view_name;

マテリアライズド ビューは、次のような場合に役立ちます。

• 複雑な集計クエリを高速に実行する必要がある場合
• データ分析レポートを定期的に作成する必要がある場合
• データ ウェアハウジング ソリューションを構築する必要がある場合

• マテリアライズド ビューは物理的な表として格納されるため、ストレージ領域を占有します。
• マテリアライズド ビューを更新する必要があるたびに、ベース テーブルのデータを読み込んで再構築する必要があります。
• マテリアライズド ビューは、常に最新の状態とは限りません。

CREATE MATERIALIZED VIEW customer_order_totals
AS
SELECT
    c.customer_id,
    SUM(oi.order_amount) AS total_order_amount
FROM
    Sales.Customers AS c
INNER JOIN
    Sales.Orders AS oi
ON
    c.customer_id = oi.customer_id
GROUP BY
    c.customer_id;

このコードは、Sales スキーマの Customers テーブルと Orders テーブルを参照するマテリアライズド ビュー customer_order_totals を作成します。このビューには、customer_id 列と total_order_amount 列が含まれます。total_order_amount 列は、各顧客の注文合計金額を表します。

このビューは、次のようなクエリを使用して照会できます。

SELECT
    *
FROM
    customer_order_totals;

このクエリは、すべての顧客の注文合計金額を返します。

特定の顧客の注文合計金額を取得するには、次のようなクエリを使用できます。

SELECT
    *
FROM
    customer_order_totals
WHERE
    customer_id = 123;

このクエリは、顧客 ID 123 の注文合計金額を返します。

このマテリアライズド ビューは、REFRESH MATERIALIZED VIEW customer_order_totals ステートメントを使用して手動で更新できます。

REFRESH MATERIALIZED VIEW customer_order_totals;

このステートメントは、Sales テーブルと Orders テーブルの最新データを使用して、マテリアライズド ビューを更新します。

DROP MATERIALIZED VIEW customer_order_totals;

このステートメントは、customer_order_totals マテリアライズド ビューを削除します。

説明

マテリアライズド ビューの使用を検討している場合は、次の点に注意する必要があります。

• SQL Server Management Studio (SSMS) を使用する: SSMS を使用して、グラフィカル ユーザー インターフェイスでマテリアライズド ビューを作成できます。
• Azure Data Studio を使用する: Azure Data Studio を使用して、グラフィカル ユーザー インターフェイスでマテリアライズド ビューを作成できます。
• PowerShell を使用する: PowerShell を使用して、マテリアライズド ビューを作成するスクリプトを作成できます。

SSMS を使用してマテリアライズド ビューを作成する方法

1. SSMS で、マテリアライズド ビューを作成するデータベースに接続します。
2. オブジェクト エクスプローラー で、ビュー フォルダを展開します。
3. ビュー フォルダを右クリックし、新しいビュー を選択します。
4. 新しいビュー ダイアログ ボックスで、マテリアライズド ビュー オプションを選択します。
5. マテリアライズド ビューの 名前 、 所有者 、 スキーマ を指定します。
6. マテリアライズド ビューの定義を クエリ エディター ペインに入力します。
7. オプション タブをクリックして、マテリアライズド ビューのオプションを指定します。
8. OK をクリックして、マテリアライズド ビューを作成します。

Azure Data Studio を使用してマテリアライズド ビューを作成する方法

1. Azure Data Studio で、マテリアライズド ビューを作成するデータベースに接続します。

$sql = "
CREATE MATERIALIZED VIEW materialized_view_name
AS
SELECT
    ...
FROM
    ...
[
    WITH (
        [オプション]
    )
];
"

Invoke-SqlCmd -Database database_name -Sql $sql