MCP Microsoft SQL Server 2012 へのクエリ 試験対策コース エディフィストラーニング株式会社 ラーニングソリューション部 1

MCP 70-461：

Microsoft ^® SQL Server 2012 へのクエリ 試験対策コース

エディフィストラーニング株式会社 ラーニングソリューション部

2

目標と前提条件

目標

「MCP 70-461：Microsoft® SQL Server 2012 へのクエリ」資格 に対応する知識を学習

前提条件

リレーショナル データベースに関する基本知識を有していること SQL Server データベース エンジンに関する基本知識を有し、管理 ツールの操作に慣れていること

MCSA:SQL Server 2012

SQL Server 2012 コア プラットフォーム スキルの所有を証明

2 種類の資格

MCSA (マイクロソフト認定ソリューション アソシエイト)

コア プラットフォーム スキルの所有を証明

MCSE (マイクロソフト認定ソリューション エキスパート)

卓越した IT スキルのスタンダードとして世界中で認知 多様なソリューションにわたって奥深い専門技術を実証

変わり続けるテクノロジーに対応する最先端のスキルを証明

MCSE:Data Platform

大規模データ ソリューションの構築と管理のた めの幅広いスキルセットを証明

MCSE:Business Intelligence

BI に関する専門スキルを証明

4

MCSA: SQL Server 2012

SQL Server の MCP 資格に初めて挑戦される方

SQL Server 2008 で SQL Server の MCP 資格を取得している方

http://www.microsoft.com/ja-jp/learning/certification/mcsa/sql-server/default.aspx

MCSE: Data Platform

SQL Server の MCP 資格に初めて挑戦される方

SQL Server 2008 で SQL Server の MCP 資格を取得している方

http://www.microsoft.com/ja-jp/learning/certification/mcse/sql-server/data-

6

MCSE: Business Intelligence

SQL Server の MCP 資格に初めて挑戦される方

SQL Server 2008 で SQL Server の MCP 資格を取得している方

http://www.microsoft.com/ja-jp/learning/certification/mcse/sql-server/business-

intelligence/default.aspx

70-461 試験内容

受験対象者

SQL Server データベースの管理者、導入者、システム エンジニア、

およびデータベース アプリケーションの開発者

出題範囲

1. データベースオブジェクトの作成 (24%) 2. データの操作 (27%)

3. データの変更 (24%)

4. クエリのトラブルシューティングと最適化 (25%)

所要時間

170 分

http://www.microsoft.com/ja-jp/learning/mcp/exam.aspx?cert=1&id=70-461

8

コースの概要

第1章 データベース オブジェクトの作成

T-SQL を使用したテーブルの作成と変更 ビューの作成と変更

DML トリガーの作成と変更

第2章 データの取得と変更

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DML ステートメントによるデータの変更

第3章 関数とストアド プロシージャ

関数

ストアド プロシージャ

第4章 クエリのトラブル シューティングと最適化

クエリの最適化

トランザクション管理 エラー処理

章の構成

T-SQL を使用したテーブルの作成と変更 ビューの作成と変更

DML トリガーの作成と変更

10

テーブルとは？

テーブルはデータを表形式の構造に格納 列の集まりとして定義される

各行には一意なレコードが置かれる

社員ID 部門ID 社員名 電話番号 住所 生年月日 更新日

1000 10 鈴木 和康 03-0123-0022 東京都杉並区 1972/1/11 2002/4/1 1001 11 矢嶋 聡 03-1234-0023 東京都板橋区 1978/4/3 2004/4/1 1002 11 荒木 達也 045-456-0024 神奈川県横浜市 1974/12/5 2001/4/1 1003 12 西 昭彦 03-7890-0025 東京都墨田区 1969/10/12 2003/4/1

← 行に置かれた レコード 列→

← 列名

テーブル定義の基本構文

CREATE TABLE ステートメント

ALTER TABLE ステートメントによる列の追加

CREATE TABLE <

>.<

>(

<

1> <

> (NULL | NOT NULL) , <

2> <

> (NULL | NOT NULL)

・

・

)

ALTER TABLE <

>.<

>

ADD <

1> <

> (NULL | NOT NULL)

12

システム データ型 (1/2)

カテゴリ データ型 バイト数 説明

整数

bigint int smallint tinyint

8 4 2 1

-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807 の整数値 -2,147,483,648 ～ 2,147,483,647 の整数値

-32,768 ～ 32,767 の整数値 0 ～ 255の整数値

真数

decimal[(p[, s])]

numeric[(p[, s])] 5 ～ 17 - 10^38 +1 ～ 10^38 – 1の固定長の有効桁数と小数点部桁数を持つ数値 概数 float[(n)]

real

8 4

-1.79E + 308 ～ 1.79E + 308 の浮動小数点数 -3.40E + 38 ～ 3.40E + 38 の浮動小数点数

通貨 money

smallmoney

8 4

-922,337,203,685,477.5808 ～ 922,337,203,685,477.5807 の金額データ値 -214,748.3648 ～ 214,748.3647の金額データ値

日付と時刻

datetimeoffset

time

8 ～ 10 8 6 ～ 8 4 3 3 ～ 5

1年1月1日 ～ 9999年12月31日の日付とタイム ゾーン オフセットが付いた時刻データ 1753年1月1日 ～ 9999年12月31日の日付と時刻データ

1年1月1日 ～ 9999年12月31日の日付と時刻データ 1990年1月1日 ～ 2079年6月6日の日付と時刻データ 1年1月1日 ～ 9999年12月31日の日付データ

0:00:00.0000000 ～ 23:59:59.9999999

非 Unicode 文字

char[(n)]

varchar[(n)]

varchar(max) text

0 ～ 8,000 0 ～ 8,000 0 ～ 2 GB 0 ～ 2 GB

8,000文字以内の固定長非Unicodeデータ 8,000文字以内の可変長非Unicodeデータ

2,147,483,647文字以内の可変長非Unicodeデータ 2,147,483,647文字以内の可変長非Unicodeデータ

Unicode 文字

nchar[(n)]

nvarchar[(n)]

nvarchar(max) ntext

0 ～ 8,000 0 ～ 8,000 0 ～ 2 GB 0 ～ 2 GB

4,000文字以内の固定長Unicodeデータ 4,000文字以内の可変長Unicodeデータ

1,073,741,823文字以内の可変長非Unicodeデータ 1,073,741,823 文字以内の可変長非Unicodeデータ

システム データ型 (2/2)

カテゴリ データ型 バイト数 説明

バイナリ

binary[(n)]

varbinary[(n)]

varbinary(max)

0 ～ 8,000 0 ～ 8,000

0 ～ 2 GB (または16バイト ポインタ)

8,000 バイト以内の固定長バイナリ データ 8,000 バイト以内の可変長バイナリ データ

2,147,483,647 バイト以内の可変長バイナリ データ 画像 image 0 ～ 2 GB (または16バイト ポインタ) 2,147,483,647 バイト以内の可変長バイナリ データ グローバル識別

子

uniqueidentifier

グローバルな一意識別子 (GUID) を格納するデータ型

xml

xml

特殊

bit cursor timestamp sysname sql_variant table hierarchyid geometry geography

1 0 ～ 8 8 256

0 ～ 8,016

テーブルの定義内容による 0 ～ 892

0 ～ 8,060 (Undocumented) 0 ～ 8,060 (Undocumented)

1 または 0

カーソルへの参照を格納

データベース タイムスタンプ値

オブジェクト名を格納するユーザー定義データ型 (nvarchar(128))

int 値、binary 値、および char 値を格納できる テーブル形式のデータを格納

階層内の位置情報を格納する CLR のデータ型

ユークリッド座標系のデータを格納する CLR データ型 球体地球座標系のデータを格納する CLR データ型

8,000 バイト以上のドキュメントや動画などのメディア ファイルは、varbinary(max) を使用

14

計算列と PERSISTED の効果

PERSISTED とマークされた計算列

計算値をテーブルに物理的に保存し、計算列のいずれかが更新された場合、その値を 更新するように指定できる

次のテーブルに StockInStore と StockInWarehouse を合計した値を持つ PERSISTED とマークされた計算列 (TotalCount) を追加する

次の ALTER TABLE ステートメントを実行する

内部的にはインデックスが作成される

インデックスが不要の場合は、PERSISTED を記述しない

ALTER TABLE Inventory

ADD TotalCount AS (ProductsInStore + ProductsInWarehouse) PERSISTED

データ整合性

ドメインの整合性

列に格納される値が取りうる範囲を定義域（ドメイン）と呼ぶ

例

社員年齢は 18歳 以上 65 歳未満 性別は「男性」、「女性」の2種類

列に適正な値が格納されるように値の種類、形式、範囲、NULL 値を 許容するかなどを規定する

データ型、NULL 値許容属性、CHECK 制約、DEFAULT 制約で設定

エンティティの整合性

テーブルに格納される行の一意性の保証 PRIMARY KEY 制約、UNIQUE 制約で設定

参照整合性

テーブル間の参照関係に矛盾がない状態を維持 FOREIGN KEY 制約、トリガーで設定

16

データ整合性と制約

データ整合性 対象 制約 説明

ドメイン 整合性

列 DEFAULT INSERT ステートメントの VALUE 句に明示的な値が指定さ れていない場合、既定値を挿入

CHECK INSERT、UPDATE ステートメント実行時に格納される値を 制限

NULL NULL 値を許可するか拒否 (NOT NULL) するかを指定

テーブル FOREIGN KEY 格納できるデータ値を同一テーブル、または外部テーブルの 指定された列に格納される値の範囲に制限

エンティティ 整合性

テーブル PRIMARY KEY 各行を一意に識別する

内部的には、インデックスが作成される NULL 値は許可されない

列 UNIQUE 各行を一意に識別する

内部的には、インデックスが作成される NULL 値は 1 件入力できる

参照整合性 テーブル FOREIGN KEY 格納できるデータ値を同一テーブル、または外部テーブルの 指定された列に格納される値の範囲に制限

参照されているキー値の変更時の動作が指定できる

列 CHECK 格納できるデータ値を同一テーブルの別の列にに格納される

値の範囲に制限

制約の構文

PRIMARY KEY 制約

主キーとして設定し、列に重複した値が入力されないようにする 内部的に一意なクラスター化インデックスが作成される

FOREIGN KEY 制約

格納できるデータ値を同一テーブル、または外部テーブルの指定された列に格納され る値の範囲に制限

DEFAULT 制約

列に既定値を格納

ALTER TABLE <

>

ADD CONSTRAINT <

> FOREIGN KEY (<

>) REFERENCES <

> (<

>)

ALTER TABLE <

>

ADD CONSTRAINT <

> PRIMARY KEY CLUSTERED (<

1>, <

n>))

ALTER TABLE <

>

18

参照整合性

PRIMARY KEY 制約と FOREIGN KEY 制約により実装

部門ID 部門名

社員ID 部門ID 上司ID 社員名 電話番号 E メール 更新日

部門 社員

PK PK

FK

FK

1

外部テーブル参照

2

制約の定義例

CREATE TABLE [部門](

[

ID] int IDENTITY(1,1) NOT NULL ,[部門名] nvarchar(50) NOT NULL

CONSTRAINT [PK_部門] PRIMARY KEY CLUSTERED ([部門ID]) ) GO

CREATE TABLE [社員](

[社員ID] int IDENTITY(1000,1) NOT NULL ,[

ID] int NOT NULL

,[上司ID] int NOT NULL

,[社員名] nvarchar(50) NOT NULL ,[電話番号] nchar(12) NOT NULL ,[Eメール] nvarchar(125) NOT NULL ,[生年月日] date NOT NULL

,[

] datetime NOT NULL CONSTRAINT [DF_

_

] DEFAULT(GETDATE()), CONSTRAINT [PK_社員] PRIMARY KEY CLUSTERED ([社員ID])

,CONSTRAINT [UQ_社員] UNIQUE ([Eメール])) GO

ALTER TABLE [社員]

ADD CONSTRAINT [FK_社員_上司] FOREIGN KEY([上司ID]) REFERENCES [社員]([社員ID])

GO

ALTER TABLE [社員]

ADD CONSTRAINT [FK_社員_部門] FOREIGN KEY([部門ID]) REFERENCES [部門]([部門ID])

GO

20

Q1：PRIMARY KEY 制約

問：Customers テーブルは複数のソース テーブルから顧客データをインポート する。ここで Customers テーブルの各行が一意であることを保証したい

次の CREATE TABLE ステートメントを実行する

SourceID CustID Name

1 121 鈴木 和康

2 7856 矢嶋 聡

2 7859 荒木 達也

1 122 西 昭彦

CustID (PK) Name

121 鈴木 和康

122 西 昭彦

CustID (PK) Name

7856 矢嶋 聡 7859 荒木 達也 Customers

CREATE TABLE Customers ( SourceID int NOT NULL ,CustID int NOT NULL

,Name nvarchar(255) NOT NULL

,CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (SourceID, CustID))

Q2：FOREIGN KEY 制約

テーブル間の参照整合性を設定する

問：Orders テーブルの CustomerID が Customers テーブルの CustomerID の値の範囲であることを保証したい

次の ALTER TABLE ステートメントを実行する

ALTER TABLE Orders

ADD CONSTRAINT FX_Order_CusomerID FOREIGN KEY (CusomerID)

REFERENCES Cusomers (CusomerID)

22

一意な値の発行

IDENTITY プロパティによる整数値の発行 IDENTITY (シード値、増分値)

列のプロパティとして設定

引数で指定されたシード値から開始し、以降、増分値を加算した値を発行

NEWID() 関数による GUID 値の発行 uniqueidentifier 型に GUID 値を格納

ネットワーク上のサーバー間で複製されるテーブルの一意性を確保

SEQUENCE オブジェクト

NEXT VALUE FOR <シーケンス名> でシーケンス値を取得

CREATE TABLE Sales.

(

受注

ID int NOT NULL IDENTITY(1,1) ,

ID int NOT NULL

,

smallint NULL

,rowguid uniqueidentifier NOT NULL

CONSTRAINT DF_rowguid DEFAULT (newid() WITH VALUES) )

CREATE SEQUENCE <

> AS <

>

START WITH <

>

INCREMENT BY <

>

MINVALUE <

>

MAXVALUE <

>

(CYCLE | NO CYCLE)

UPDATE <

> SET <

> = NEXT VALUE FOR <

>

Q3：GUID 値を発行する DEFAULT 制約

NEWID() 関数

GUID (グローバル一意識別子) として機能する 16 バイトの 2 進数値 (Uniqueidentifier 型) を発行する

問：既存データを格納している Accounts テーブルに、新規データが挿 入された時、GUID 値を生成し、格納するための列として

AccountGUID を追加したい

次の ALTER TABLE ステートメントを実行する

ALTER TABLE dbo.Accounts

ADD AccountGUID Uniqueidentifier NOT NULL CONSTRAINT DF_AccountGUID DEFAULT

NEWID()

WITH VALUES

24

Q4：SEQUENCE を使用した連番の発行

問：100 人の生徒情報を格納している Students テーブルに Class 列が 追加された。その列に 1 から 10 の値を 1 ずつ増分しながら、循環さ せて格納したい

次の CREATE SEQUENCE と UPDATE ステートメントを実行する

CREATE SEQUENCE CLASS_Sequence AS int

START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10 CYCLE

UPDATE Students SET CLASS = NEXT VALUE FOR CLASS_Sequence

SQL Server のインデックス

クラスター化インデックス テーブルに 1 個

リーフノードには、インデックス付けされた列の値に基づきソートされたデータ行を格納

非クラスター化インデックス テーブルに 999 個まで

リーフノードには、データ行へのポインタ (行識別子:RID) を格納

但し、既にクラスター化インデックスがある場合は、クラスター化インデックスのキー値を格納

一意なインデックスの作成では、UNIQUE キーワードを指定する インデックス キーのサイズと複合インデックス列の数の制約

キー列の最大サイズ 900 バイト、16 列まで

PRIMARY KEY 制約を設定した列

他の列にクラスター化インデックスが存在しない場合

自動的に一意のクラスター化インデックスが作成される

他の列にクラスター化インデックスが存在する場合

CREATE CLUSTERED INDEX <

> ON <

>(<

>,

)

CREATE INDEX <

> ON <

>(<

>,

)

26

680 FR-R92B-58 Black

706 FR-R92R-58 Red

707 HL-U509-R Red

708 HL-U509 Black

709 SO-B909-M White

・・ ・・・ ・・・ ・・・

710 SO-B909-L White

711 HL-U509-B Blue

712 CA-1098 Multi

713 LJ-0192-S Multi

714 LJ-0192-M Multi

・・ ・・・ ・・・ ・・・

715 LJ-0192-L Multi

716 LJ-0192-X Multi

717 FR-R92R-62 Red

718 FR-R92R-44 Red

719 FR-R92R-48 Red

・・ ・・・ ・・・ ・・・

720 FR-R92R-52 Red ・・・

721 FR-R92R-56 Red ・・・

722 FR-R38B-58 Black ・・・

723 FR-R38B-60 Black ・・・

724 FR-R38B-62 Black ・・・

・・ ・・・ ・・・ ・・・

680 01:100 710 01:101

・・ ・・・・

715 01:102 720 01:103

・・ ・・・・

680 01:141 715 01:142

・・ ・・・・

ページ:100 ページ:101 ページ:103

ページ:141

①

②

③

ページ:142 ページ:140

ProductID で 並び替えられ たデータ行

リンクリスト ProductID

Clustered Index Seek クラスター化インデックス

ページ:102

SELECT ProductID,ProductNumber,Color FROM Production.Product

WHERE ProductID = 714

クラスター化インデックスの構造

ProductID にクラスター化インデックスを作成した場合

生成される 実行プラン 完全一致 検索クエリ

680 01:180 710 01:181

・・ ・・・・

715 01:182 720 01:183

・・ ・・・・

710 01:110:03 711 01:130:04 712 01:100:04 713 01:110:01 714 01:120:05

・・ ・・・・・・

720 01:130:02 721 01:130:01 722 01:130:05 723 01:100:01 724 01:100:02

・・ ・・・・・・

715 01:100:03 716 01:130:03 717 01:120:04 718 01:120:02 719 01:110:05

・・ ・・・・・・

680 01:171 715 01:172

・・ ・・・・

ページ:171 ページ:172

ページ:170

ページ:180 ページ:181 ページ:182 ページ:183

①

②

③

④

ファイル番号:

ページ番号

Index Seek + RID Lookup 非クラスター化インデックス

680 01:120:03 706 01:110:04 707 01:110:02 708 01:120:01 709 01:100:05

・・ ・・・・・・

SELECT ProductID,ProductNumber,Color FROM Production.Product

WHERE ProductID = 714

RID LOOKUP

ファイル番号:RID ページ番号:行番号 ProductID

721 FR-R92R-56 Red ・・・

720 FR-R92R-52 Red ・・・

716 LJ-0192-X Multi ・・・

711 HL-U509-B Blue ・・・

722 FR-R38B-58 Black ・・・

708 HL-U509 Black ・・・

718 FR-R92R-44 Red ・・・

680 FR-R92B-58 Black ・・・

717 FR-R92R-62 Red ・・・

714 LJ-0192-M Multi ・・・

713 LJ-0192-S Multi ・・・

707 HL-U509-R Red ・・・

710 SO-B909-L White ・・・

706 FR-R92R-58 Red ・・・

719 FR-R92R-48 Red ・・・

723 FR-R38B-60 Black ・・・

724 FR-R38B-62 Black ・・・

715 LJ-0192-L Multi ・・・

712 CA-1098 Multi ・・・

709 SO-B909-M White ・・・

ページ:100 ページ:110 ページ:120 ページ:130

非クラスター化インデックスの構造

ProductID に非クラスター化インデックスを作成した場合

ヒープ

生成される 実行プラン

完全一致 検索クエリ

28

スキーマ

データベース オブジェクトはスキーマ に置かれる

アプリケーションに対する名前空間の形成

<サーバー名>.<データベース名>.<スキーマ名>.<オブジェクト名>

スキーマ作成の基本構文 スキーマ変更の基本構文

データベース

Production dbo Sales

データベース

dbo

インスタンス

スキーマ

ALTER SCHEMA <新しいスキーマ名> TRANSFER <既存のスキーマ名>.<オブジェクト名>

CREATE SCHEMA <スキーマ名> AUTHORIZATION <所有者名>

章の構成

T-SQL を使用したテーブルの作成と変更 ビューの作成と変更

DML トリガーの作成と変更

30

ビューとは?

クエリにより定義される仮想テーブル

ビューが参照するベース テーブルに対するフィルターの役目をする ベース テーブルへの直接のアクセスを許可したくない場合に使用

ビューの種類

標準のビュー

1 つ以上のベース テーブルまたは、ビューのデータを 1 つの新しい仮想テーブル に組み合わせる

インデックス付きビュー

ビューに一意なクラスター化インデックスを作成することにより ビューを具体化 (永続化) する

パーティション ビュー

1 つ以上のサーバーに分散した 1 つ以上のベース テーブルを基に、行方向にパー ティション分割されたデータを結合する

標準のビューの定義例

社員ID 部門ID 社員名 電話番号 生年月日 更新日

1001 10 鈴木 和康 03-0123-〇△×〇 1972/1/11 2002/4/1 1002 11 矢嶋 聡 03-1234-〇☐×△ 1978/4/3 2004/4/1 1003 11 荒木 達也 045-456-△×△〇 1974/12/5 2001/4/1 1004 12 西 昭彦 03-7890-〇×△△ 1969/10/12 2003/4/1

WITH SCHEMABINDING

ベース テーブルの定義にビューをバインド

ベース テーブルに対し、ビュー定義に影響を与える変更は行えない

WITH CHECK OPTION

部門ID 部門名

10 企画部

11 営業部

12 生産部

社員テーブル 部門テーブル

部門名 社員名 電話番号

企画部 鈴木 和康 03-0123-〇△×〇 営業部 矢嶋 聡 03-1234-〇☐×△

営業部 荒木 達也 045-456-△×△〇 vw_部門別社員情報

CREATE VIEW vw_

WITH SCHEMABINDING

AS

SELECT

,

,

FROM dbo.部門

JOIN dbo.

ON 部門.部門ID

=

.

ID

WHERE 部門.部門ID IN (10,11)

WITH CHECK OPTION

32

ビューを使用したデータの変更

元になる複数のテーブルを同時に変更することはできない 以下の列は変更できない

集計関数または計算から得られた列を変更できない

GROUP BY、HAVING、または DISTINCT 句の影響を受ける列を変更でき ない

インデックス付きビュー

インデックス付きビューの利点

ビューを永続化し、パフォーマンスを向上させる

クエリ オプティマイザーがクエリの解決にビューを使用できるようになる

インデックス付きビューの選択シナリオ

パフォーマンス向上がメンテナンスのオーバー ヘッドより重要 基になるテーブルのデータが頻繁に変更されない

クエリで多数のテーブルの結合や集計を実行する

インデックス付きビューの要件

ビューに作成する最初のインデックスの種類は一意なクラスター化インデックス SCHEMABINDING オプションを使用する

スキーマ バインドによって、ベース テーブルのスキーマにビューをバインドする ビューから他のビューは参照できない

ベース テーブルはビューと同じデータベース内にあり、ビューと同じ所有者にする ビューが参照するテーブルやユーザー定義関数は、<スキーマ名>.<オブジェクト名>

の 2 部構成の形式で参照する

1 部、3 部、または 4 部構成の形式で構成されるオブジェクト名は使用できない ビューから非決定的関数は参照できない

GROUP BY を記述する場合、ビュー定義に COUNT_BIG(*) を記述する

34

Q5：インデックス付きビューの定義

問：次の要件を満たす dbo.vwTokyoStudents という名前のビューを定義する

Province が「Tokyo」の生徒のすべての情報を表示する

ビューから Address テーブルにデータを挿入する場合、Province が「Tokyo」以外 の生徒の情報は、挿入させないようにする

作成するビューにインデックスを作成できるようにする

次の CREATE VIEW ステートメントを実行する

CREATE VIEW dbo.vwTokyoStudents WITH SCHEMABINDING

AS

SELECT *

FROM Student s

INNER JOIN Address a ON s.AddressID = a.AddressID

INNER JOIN EducationHistory e ON s.StudentID = e.StudentID WHERE a.Province = 'Tokyo'

WITH CHECK OPTION

Q6：インデックス付きビューの定義

問：次のビュー参照のパフォーマンスを向上させるには？

次のステートメントを実行する

CREATE VIEW Sales.vwCustomerSalesAmount WITH SCHEMABINDING

AS SELECT

O.CustomerID ,C.Name

,SUM(O.SalesAmount) AS CustomerTotal ,COUNT_BIG(*) AS Count

FROM Sales.Orders AS O

INNER JOIN Sales.Customers AS C ON O.CustomerID = C.CustomerID GROUP BY O.CustomerID,C.Name

CREATE UNIQUE CLUSTERED INDEX idx_vwCustomerSalesAmount

ON Sales.vwCustomerSalesAmount (CustomerID)

36

章の構成

T-SQL を使用したテーブルの作成と変更 ビューの作成と変更

DML トリガーの作成と変更

トリガーとは?

特別な種類のストアド プロシージャ

トリガーとトリガーを引き起こしたステートメントは 1 つのトランザクショ ンに含まれる

DML トリガー

テーブルに作成

INSERT、UPDATE、または DELETE ステートメントによりテーブル変更時 にデータベース エンジンにより起動される

2 つの種類

AFTER トリガー

INSERT、UPDATE、または DELETE ステートメントの実行後に起動される INSTEAD OF トリガー

テーブル、またはビューに作成

INSERT、UPDATE、または DELETE のステートメントの代わりに起動される

DDL トリガー

サーバーに作成

DDL ステートメント実行時に起動される データベースオブジェクトの変更を監査

38

DMLトリガー定義の基本構文

DML トリガー

FOR と記述した場合、AFTER と同じ

CREATE TRIGGER <スキーマ名>.<トリガー名>

ON {<

>| <

>}

{FOR | AFTER | INSTEAD OF}

{[INSERT][,][UPDATE][,][DELETE]}

AS BEGIN

・・・

END

DML AFTER トリガーの例

inserted テーブル

挿入されたデータ行を保持 deleted テーブル

削除されたデータ行を保持

例：Products テーブルの RetailPrice 列、または SalesPrice 列の値が変更された場合、

PriceUpdateHistory テーブルに変更前の値を記録するトリガー

CREATE TRIGGER dbo.TrgPriceUpdate ON Products AFTER UPDATE

AS

IF UPDATE(RetailPrice) OR UPDATE(SalePrice) INSERT INTO dbo.PriceUpdateHistory

SELECT ProductID

,RetailPrice AS OldRetailPrice ,SalePrice AS OldSalePrice

,GETDATE() AS UpdatedDateTime

40

DML INSTEAD OF トリガー

トリガーを起動するステートメントの動作を置き換える 使用例

1 つ以上の列に対するエラー チェックや値チェックの実行 行の挿入、更新、削除操作に対する追加の処理を実行

UNION ALL や INNER JOIN で結合された複数のベース テーブルを持つ ビューに対する挿入、更新、削除操作

DML INSTEAD OF トリガーの例

次の要件を満たすトリガーを Transactions テーブルに作成する

Transactions テーブルにデータが挿入される場合、Accounts テーブルに AccountNumber が存在する場合のみ データが挿入されるようにする 複数データが挿入される場合でも正しく動作する必要がある

次の CREATE TRIGGER ステートメントを実行する

CREATE TRIGGER TrgValidateAccountNumber ON Transactions

INSTEAD OF INSERT AS

BEGIN

INSERT INTO Transactions

SELECT TransactionID ,AccountNumber ,Amount ,Date FROM INSERTED

WHERE AccountNumber IN (SELECT AccountNumber FROM Accounts)

END

42

Q7 ビューへのトリガーの作成

リレーションシップが設定された Employee と Address テーブル 各テーブルの主キー列には、IDENTITY プロパティが設定されている

次の定義文で vwEmployeeInfo という名前のビューを作成した

ビューに対して、INSERT や INSERT ・・・ SELECT ステートメントが実行された場合、

Employee と Address テーブルにデータの挿入を可能にするためのトリガーを定義する

CREATE VIEW vwEmployeeInfo AS

SELECT e.LastName ,e.FirstName ,e.BirthDate ,a.AddressID ,a.Province ,a.City ,a.Address1 ,a.Address2

FROM dbo.employee e INNER JOIN dbo.address a ON e.AddressID = a.AddressID

CREATE TRIGGER trgVwEmployeeInfo ON VwEmployeeInfo

INSTEAD OF INSERT AS

BEGIN

DECLARE @AddressID int

INSERT INTO dbo.Address(Province,City,Address1,Address2) SELECT Province,City,Address1,Address2 FROM INSERTED SELECT @AddressID = SCOPE_IDENTITY()

INSERT INTO dbo.Employee(LastName,FirstName,BirthDate,AddressID)

SELECT LastName,FirstName,BirthDate,@AddressID FROM INSERTED

END

コースの概要

第1章 データベース オブジェクトの作成

T-SQL を使用したテーブルの作成と変更 ビューの作成と変更

DML トリガーの作成と変更

第2章 データの取得と変更

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DML ステートメントによるデータの変更

第3章 関数とストアド プロシージャ

関数

ストアド プロシージャ

第4章 クエリのトラブル シューティングと最適化

クエリの最適化

トランザクション管理 エラー処理

44

章の構成

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DMLステートメントによるデータの変更

SELECT ステートメント

基本構文

テーブルの結合 (ANSI 89)・・・内部結合のみ

テーブルの結合 (ANSI 92)

SELECT

[ INTO

]

FROM <

>.<

>

[ WHERE

]

[ ORDER BY

[ ASC | DESC ] ]

SELECT

FROM <

>.<

> , <

>.<

>

WHERE

SELECT

FROM <

>.<

>

JOIN <

>.<

>

ON

46

テーブルの結合

社員と部門テーブルの定義内容

各テーブルに格納されたデータ

内部結合

内部結合

結果セット

SELECT d.DepartmentName,e.EmployeeName FROM dbo.Department d

INNER JOIN dbo.Employee e

ON d.DepartmentID = e.DepartmentID

48

外部結合

左外部結合

結果セット

右外部結合

結果セット

SELECT d.DepartmentName,e.EmployeeName FROM dbo.Department d

LEFT OUTER JOIN dbo.Employee e ON d.DepartmentID = e.DepartmentID

SELECT d.DepartmentName,e.EmployeeName FROM dbo.Department d

RIGHT OUTER JOIN dbo.Employee e

ON d.DepartmentID = e.DepartmentID

外部結合

完全外部結合

結果セット

SELECT d.DepartmentName,e.EmployeeName FROM dbo.Department d

FULL OUTER JOIN dbo.Employee e

ON d.DepartmentID = e.DepartmentID

50

クロス結合

クロス結合

結果セット

SELECT d.DepartmentName,e.EmployeeName FROM dbo.Department d

CROSS JOIN dbo.Employee e

Q8：テーブルの結合

問：次の要件を満たす SELECT ステートメントを記述する

顧客が購買した日を確認するため、Customers テーブルの Name 列とOrders テーブ ルの OrderDate 列を表示する

何も購買していない顧客の情報も表示する

次の SELECT ステートメントを実行する

SELECT c.Name

,o.OrderDate

FROM Customers AS c

LEFT OUTER JOIN Orders AS o

ON c.CustomerID = o.CustomerID

52

サブ クエリの記述

SELECT、INSERT、UPDATE、および DELETE ステートメントの内部、

または別のサブクエリの内部で入れ子になっているクエリ 別のクエリの結果セットに依存するクエリ

式が使えるところであればどこにでも記述できる SOME、ANY

サブ クエリが返すスカラー値のいずれかの値と比較

比較演算子の条件が一つでも True になる場合、True を返す 条件がすべて False になる場合、False を返す

ALL

サブ クエリが返すスカラー値のすべての値と比較

比較演算子の条件がすべて True になる場合、True を返す 条件が一つでも False になる場合、False を返す

SOME、ANY、ALL、IN 演算子

テーブルに格納されているデータ

比較演算子の条件が一つでも True になる場合、True を返す (<> ANY と同等)

比較演算子の条件がすべて True になる場合、True を返す (NOT IN と同等)

--

Tanaka

SELECT * FROM dbo.Department WHERE DepartmentID <> SOME

(SELECT DepartmentID FROM dbo.Employee WHERE EmployeeName = 'Tanaka')

--

SELECT * FROM dbo.Department WHERE DepartmentID <> ALL

(SELECT DepartmentID FROM dbo.Employee)

54

Q9： SOME、ANY、ALL、IN 演算子

問：営業担当がいない地域に住む顧客の一覧を表示する

A. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > SOME (SELECT CityID FROM Sales.Salesperson) B. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > ALL (SELECT CityID FROM Sales.Salesperson) C. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > ANY (SELECT CityID FROM Sales.Salesperson) D. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID NOT IN (SELECT CityID FROM Sales.Salesperson) A. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > SOME (SELECT CityID FROM Sales.Salesperson) B. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > ALL (SELECT CityID FROM Sales.Salesperson) C. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID < > ANY (SELECT CityID FROM Sales.Salesperson) D. SELECT CustomerID ,Name ,CityID FROM Sales.Customers

WHERE CityID NOT IN (SELECT CityID FROM Sales.Salesperson)

共通テーブル式 (CTE)

ANSI-99 SQL 規格に準拠した一時的な名前付き結果セット 派生テーブルの改良

一度だけ評価されステートメントが完了するまで保持される 複数回の参照、自己参照が可能

WITH 句で開始

名前 AS に続き丸括弧内に表現式

複数の CTE を記述する場合、カンマで区切る

結果セットを返す SELECT ステートメントが続く

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS

( SELECT

SalesPersonID , SalesOrderID

, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL) SELECT

SalesPersonID

, COUNT(SalesOrderID) AS TotalSales , SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

「Sales_CTE」という名前 の CTE の定義

CTE のデータを取得する SELECT

CTE を参照する SELECT

WITH <CTE

>

AS

(<SELECT

>)

<DML

>

56

Q10：共通テーブル式 (CTE)

問：次の要件を満たす CTE を使用した SELECT ステートメントを記述する

最後に商品を購入してから 2 年間、商品を購入していない顧客名と最新の購入日を表 示する

次の SELECT ステートメントを実行する

WITH CTE (CustomerID, MostResentOrderDate) AS (

SELECT CustomerID, MAX(OrderDate) AS MostResentOrderDate FROM dbo.OrderSummary

GROUP BY CustomerID )

SELECT ci.CustomerName,MostResentOrderDate FROM CTE

INNER JOIN dbo.CustomerInfo ci ON CTE.CustomerID = ci.CustomerID

WHERE cte.MostResentOrderDate < DATEADD(YEAR, -2, GETDATE() )

Q11：共通テーブル式 (CTE)

問：次の要件を満たす CTE を使用した DELETE ステートメントを記述する

Products テーブルの Name 列に重複した値がある場合、削除する UpdatedDate 列の値が古い方を削除する

次の DELETE ステートメントを実行する

WITH CTEDupRecords AS

(

SELECT MAX(UpdatedDate) AS UpdatedDate, Name FROM Products

GROUP BY Name HAVING COUNT(*) > 1 )

DELETE p

FROM Products AS p

JOIN CTEDupRecords AS cte ON cte.Name = p.Name

AND cte.UpdatedDate > p.UpdatedDate

58

章の構成

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DMLステートメントによるデータの変更

集計クエリの SELECT ステートメント

基本構文

集計関数

集計クエリの例

SELECT

FROM

.

[ WHERE

]

[ GROUP BY

] [ HAVING

]

[ ORDER BY

[ ASC | DESC ] ]

集計関数 説明

AVG 値の平均値 COUNT 値の数 COUNT(*) 行の数 MAX 最大値 MIN 最小値 SUM 値の合計値

SELECT ProductID, SUM (LineTotal) AS TotalSalesAmount FROM Sales.SalesOrderDetail

GROUP BY ProductID

60

Q12：集計クエリ

問：次の集計クエリを記述しましたが、実行するとエラーになる

この集計クエリを次の要件を満たすようにして完成する SELECT と FROM 句は上記クエリを利用する

SalesOrderID ごとに降順に並び替えて ProductID ごとに集計する ProductID は昇順に表示する

SELECT

SalesOrderID ,ProductID ,AVG (OrderQty) ,SUM (UnitPrice)

,MAX (UnitPriceDiscount) FROM Sales.SalesOrderDetail

SELECT

SalesOrderID ,ProductID ,AVG (OrderQty) ,SUM (UnitPrice)

,MAX (UnitPriceDiscount) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ,ProductID

ORDER BY SalesOrderID DESC ,ProductID ASC

Q13：集計クエリ

問：次の要件を満たす集計クエリを記述する

結果セットには顧客の名前、注文日の順序で表示する

注文日は顧客ごとに最新の日付のみを表示し、列名を「MostResentOrderDate」とする 注文日を降順に並び替えて表示する

ANSI 92 以降の構文に準拠する

次の SELECT ステートメントを実行する

SELECT

C.Name

, MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C

INNER JOIN Orders AS O

ON C.CustomerID = O.CustomerID GROUP BY C.Name

ORDER BY MostRecentOrderDate DESC

62

ROLLUP、CUBE、GROUPING SETS

ROLLUP

単純な GROUP BY 集計行と小計行、および総計行を生成

CUBE

ROLLUP 集計行とクロス集計行を生成

GROUPING SETS

指定したグループの集計のみを生成する

SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_OrderSummary

GROUP BY ROLLUP (CustomerID, ProductID)

SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_OrderSummary

GROUP BY CUBE (CustomerID, ProductID)

SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_OrderSummary

GROUP BY GROUPING SETS ((CustomerID, (ProductID), (ProductID),

())

順位付けの対象となる行セットのパーティション分割と並べ替え方法を指定

構文

順位付け関数

順位付け関数 説明

ROW_NUMBER() 順序番号を生成

RANK()

DENSE_RANK()

NTILE()

SELECT <

>

,<

>RANK() OVER (ODER BY <

> [ ASC | DESC ] ) AS <

>

FROM <

>.<

>

ORDER BY <

>. <

> [ ASC | DESC ]

64

サンプル クエリ

結果セット

順位付け関数

SELECT CustomerID,[Total Amount]

,ROW_NUMBER()OVER(ORDER BY [Total Amount] DESC) AS Order_ROW_NUMBER ,RANK() OVER(ORDER BY [Total Amount] DESC) AS Order_Rank

,DENSE_RANK() OVER(ORDER BY [Total Amount] DESC) AS Order_Dense_Rank ,NTILE(100) OVER(ORDER BY [Total Amount] DESC) AS Ntile_100

FROM dbo.OrderSummary2

ORDER BY [Total Amount] DESC

Q14 順位付け関数

問：次の要件を満たす順位付け関数を使用したクエリを記述する ProductCatalog テーブルの列を参照し、製品リストを作成する

テーブルに定義された列の順序ですべての列の値を表示し、最後の列に RriceRank という名前の 列を追加し、SalesPrice 列の値で行の順位付けをする

SalesPrice の値を降順で表示する

複数の製品で SalesPrice 列の値が同じ場合、同じ順序を付けて番号を飛ばす

次の SELECT ステートメントを実行する

SELECT

ProductCatalog.ProductID ,ProductCatalog.Name ,ProductCatalog.RetailPrice ,ProductCatalog.SalesPrice

,RANK() OVER (ORDER BY ProductCatalog.SalesPrice DESC) AS PriceRank FROM Sales.ProductCatalog

ORDER BY ProductCatalog.SalesPrice DESC

66

Q15 順位付け関数

科目ごとに最高得点の生徒を得点と一緒に表示する

A. SELECT StudentID, Name, RANK() OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

B. SELECT StudentID, Name, DENSE_RANK() OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

C. SELECT StudentID, Name, NTILE(3) OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

D. SELECT Subject, StudentID, Score AS Value

FROM (SELECT Subject, StudentID, Score AS Score,

RANK () OVER (PARTITION BY Subject ORDER BY Score ASC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

E. SELECT Subject, StudentID, Score AS Value

FROM (SELECT Subject, StudentID, Score AS Score,

RANK () OVER (PARTITION BY Subject ORDER BY Score DESC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

F. SELECT StudentID, Name, Score AS Value

FROM (SELECT StudentID, Name, Score AS Score,

RANK () OVER (PARTITION BY StudentID ORDER BY Score ASC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

G. SELECT StudentID, Name, Score AS Value

FROM (SELECT StudentID, Name, Score AS Score,

RANK () OVER (PARTITION BY StudentID ORDER BY Score DESC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

A. SELECT StudentID, Name, RANK() OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

B. SELECT StudentID, Name, DENSE_RANK() OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

C. SELECT StudentID, Name, NTILE(3) OVER (ORDER BY AVG (Score) DESC) AS Value FROM dbo.StudentScore GROUP BY StudentID, Name ORDER BY Value

D. SELECT Subject, StudentID, Score AS Value

FROM (SELECT Subject, StudentID, Score AS Score,

RANK () OVER (PARTITION BY Subject ORDER BY Score ASC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

E. SELECT Subject, StudentID, Score AS Value

FROM (SELECT Subject, StudentID, Score AS Score,

RANK () OVER (PARTITION BY Subject ORDER BY Score DESC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

F. SELECT StudentID, Name, Score AS Value

FROM (SELECT StudentID, Name, Score AS Score,

RANK () OVER (PARTITION BY StudentID ORDER BY Score ASC) AS Rank FROM dbo.StudentScore) tmp WHERE Rank = 1

G. SELECT StudentID, Name, Score AS Value

FROM (SELECT StudentID, Name, Score AS Score,

RANK () OVER (PARTITION BY StudentID ORDER BY Score DESC) AS Rank

FROM dbo.StudentScore) tmp WHERE Rank = 1

LAG 関数と LEAD 関数

LAG 関数

自己結合を使用せずに同じ結果セットの前の行からデータにアクセス

LEAD 関数

自己結合を使用せずに同じ結果セットの後続の行からデータにアクセス

SELECT YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006')

SELECT YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006')

68

UNION と UNION ALL

UNION 演算子

結果セットを結合する

複数のステートメントの選択リスト内の式 (列名、算術式、集計関数など) の数は、すべて同じにする

重複する行は削除される

重複する行を削除したくない場合、UNION ALL を記述する

SELECT

COUNT (*) AS NumberOf Sales

,SUM (SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders

UNION ALL SELECT

COUNT (*) AS NumberOfSales

,SUM (SalesAmount) AS TotalSalesAmount

FROM InternationalSalesOrders

章の構成

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DMLステートメントによるデータの変更

70

XML クエリ

SQL クエリを XML データに変換 FOR XML モード

FOR XML モード XML の構造

RAW 行セットを汎用識別子 <row> を持つ XML に変換

属性(既定)、または要素 (ELEMENTS ディレクティブを記述) に展開 AUTO 行セットを XML に変換

複数テーブルの親子関係を階層に反映

属性(既定)、または要素 (ELEMENTS ディレクティブを記述) に展開 PATH XML 構造の厳密な制御が可能

列の別名に XPath 式を記述し要素と属性の展開パターン、および階層構造

を制御する(要素 (既定) または属性(AS 句で XPath 式を記述) に展開)

Q16：XML クエリ

適切な FOR XML モードを指定する

SELECT OrderID ,OrderDate ,SalesAmount ,Name

,City

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1

FOR XML RAW

<row OrderID="1" OrderDate="2013-04-01" SalesAmount="1000.0000" Name="Tanaka" City="Chiba" />

<row OrderID="6" OrderDate="2013-04-02" SalesAmount="280.0000" Name="Tanaka" City="Chiba" />

72

Q17：XML クエリ

<Orders OrderID="1" OrderDate="2013-04-01" SalesAmount="1000.0000">

<Customers Name="Tanaka" City="Chiba" />

</Orders>

<Orders OrderID="6" OrderDate="2013-04-02" SalesAmount="280.0000">

<Customers Name="Tanaka" City="Chiba" />

</Orders>

SELECT OrderID ,OrderDate ,SalesAmount ,Name

,City

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1

FOR XML AUTO

適切な FOR XML モードを指定する

Q18：XML クエリ

<Orders>

<OrderID>1</OrderID>

<OrderDate>2013-04-01</OrderDate>

<SalesAmount>1000.0000</SalesAmount>

<Customers>

<Name>Tanaka</Name>

<City>Chiba</City>

SELECT OrderID ,OrderDate ,SalesAmount ,Name

,City

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1

FOR XML AUTO ,ELEMENTS

適切な FOR XML モードを指定する

74

Q19：XML クエリ

<Customers Name="Tanaka" City="Chiba">

<OrderID>1</OrderID>

<OrderDate>2013-04-01</OrderDate>

<SalesAmount>1000.0000</SalesAmount>

</Customers>

<Customers Name="Tanaka" City="Chiba">

<OrderID>6</OrderID>

<OrderDate>2013-04-02</OrderDate>

<SalesAmount>280.0000</SalesAmount>

SELECT

Name AS '@Name' ,City AS '@City' ,OrderID

,OrderDate ,SalesAmount FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1

FOR XML PATH ('Customers')

属性として展開 属性として展開

適切な FOR XML モードを指定する

章の構成

SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け

XML データへのクエリ

DML ステートメントによるデータの変更

76

OUTPUT 句を使用したデータの更新

OUTPUT ・・・ INTO 句

更新されたデータのアーカイブや検証で使用

UPDATE ステートメントで影響を受ける行の情報を INSERTED、および DELETED テーブルの値として返す

INTO に指定したテーブル、またはテーブル型変数にデータを挿入 価格変更の情報を格納する例

UPDATE Production.Product SET ListPrice = ListPrice * 1.05 OUTPUT INSERTED.ProductID

,DELETED.ListPrice ,INSERTED.ListPrice

INTO Production.ProductListPrice_Log (ProductID, OldPrice, NewPrice)

GO

バイナリ ラージ オブジェクト (BLOB) の更新

BLOB データ型

varchar(max)、 nvarchar(max)、varbinary(max) 8000 バイト以上のデータを格納

varbinary(max) には文書ファイル、画像、動画などを格納できる Windows API からもアクセスする場合は、FILESTREAM 属性を指定

データの更新方法

UPDATE ステートメントに列に対する WRITE メソッドを記述 WRITE メソッドの構文

サンプル コード

<

>.WRITE (<

>,@Offset,@Length)

DECLARE @MyTableVar table ( SummaryBefore nvarchar(max) , SummaryAfter nvarchar(max)) GO

UPDATE Production.Document

SET DocumentSummary .WRITE (N'features',28,10) OUTPUT DELETED.DocumentSummary

, INSERTED.DocumentSummary INTO @MyTableVar

WHERE Title = N'Front Reflector Bracket Installation'