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

Transcription

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

2 目標と前提条件 目標 MCP Microsoft SQL Server 2012 へのクエリ 資格 に対応する知識を学習 前提条件 リレーショナル データベースに関する基本知識を有していること SQL Server データベース エンジンに関する基本知識を有し 管理 ツールの操作に慣れていること 2

3 2 種類の資格 MCSA (マイクロソフト認定ソリューション アソシエイト) コア プラットフォーム スキルの所有を証明 MCSE (マイクロソフト認定ソリューション エキスパート) 卓越した IT スキルのスタンダードとして世界中で認知 多様なソリューションにわたって奥深い専門技術を実証 変わり続けるテクノロジーに対応する最先端のスキルを証明 MCSE:Data Platform 大規模データ ソリューションの構築と管理のた めの幅広いスキルセットを証明 MCSE:Business Intelligence BI に関する専門スキルを証明 MCSA:SQL Server 2012 SQL Server 2012 コア プラットフォーム スキルの所有を証明 3

4 MCSA: SQL Server 2012 SQL Server の MCP 資格に初めて挑戦される方 SQL Server 2008 で SQL Server の MCP 資格を取得している方 4

5 MCSE: Data Platform SQL Server の MCP 資格に初めて挑戦される方 SQL Server 2008 で SQL Server の MCP 資格を取得している方 5

6 MCSE: Business Intelligence SQL Server の MCP 資格に初めて挑戦される方 SQL Server 2008 で SQL Server の MCP 資格を取得している方 6

7 試験内容 受験対象者 SQL Server データベースの管理者 導入者 システム エンジニア およびデータベース アプリケーションの開発者 出題範囲 データベースオブジェクトの作成 (24%) データの操作 (27%) データの変更 (24%) クエリのトラブルシューティングと最適化 (25%) 所要時間 170 分 7

8 コースの概要 第1章 データベース オブジェクトの作成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 第2章 データの取得と変更 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DML ステートメントによるデータの変更 第3章 関数とストアド プロシージャ 関数 ストアド プロシージャ 第4章 クエリのトラブル シューティングと最適化 クエリの最適化 トランザクション管理 エラー処理 8

9 章の構成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 9

10 テーブルとは テーブルはデータを表形式の構造に格納 列の集まりとして定義される 各行には一意なレコードが置かれる 列 列名 社員ID 部門ID 社員名 電話番号 住所 生年月日 更新日 鈴木 和康 東京都杉並区 1972/1/ /4/ 矢嶋 聡 東京都板橋区 1978/4/3 2004/4/1 行に置かれた 荒木 達也 神奈川県横浜市 1974/12/5 2001/4/ 西 昭彦 東京都墨田区 1969/10/ /4/1 10 レコード

11 テーブル定義の基本構文 CREATE TABLE ステートメント CREATE TABLE <スキーマ名>.<テーブル名>( <列名1> <データ型> (NULL NOT NULL), <列名2> <データ型> (NULL NOT NULL) ) ALTER TABLE ステートメントによる列の追加 ALTER TABLE <スキーマ名>.<テーブル名> ADD <列名1> <データ型> (NULL NOT NULL) 11

12 システム データ型 (1/2) カテゴリ データ型 バイト数 説明 整数 bigint int smallint tinyint ,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])] ^ ^38 1の固定長の有効桁数と小数点部桁数を持つ数値 概数 float[(n)] real E E の浮動小数点数 -3.40E E + 38 の浮動小数点数 通貨 money smallmoney ,337,203,685, ,337,203,685, の金額データ値 -214, , の金額データ値 日付と時刻 datetimeoffset datetime Datetime2 smalldatetime date time 年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: :59: ,000 8,000 2 GB 2 GB 8,000文字以内の固定長非Unicodeデータ 8,000文字以内の可変長非Unicodeデータ 2,147,483,647文字以内の可変長非Unicodeデータ 2,147,483,647文字以内の可変長非Unicodeデータ ,000 8,000 2 GB 2 GB 4,000文字以内の固定長Unicodeデータ 4,000文字以内の可変長Unicodeデータ 1,073,741,823文字以内の可変長非Unicodeデータ 1,073,741,823 文字以内の可変長非Unicodeデータ char[(n)] varchar[(n)] 非 Unicode 文字 varchar(max) text Unicode 文字 12 nchar[(n)] nvarchar[(n)] nvarchar(max) ntext

13 システム データ型 (2/2) カテゴリ データ型 バイト数 説明 バイナリ binary[(n)] varbinary[(n)] varbinary(max) 0 8,000 8,000 バイト以内の固定長バイナリ データ 0 8,000 8,000 バイト以内の可変長バイナリ データ 0 2 GB (または16バイト ポインタ) 2,147,483,647 バイト以内の可変長バイナリ データ 画像 image 0 2 GB (または16バイト ポインタ) 2,147,483,647 バイト以内の可変長バイナリ データ グローバル識別 子 uniqueidentifier 16 グローバルな一意識別子 (GUID) を格納するデータ型 xml xml 0 2 GB 2,147,483,647 バイト以内の可変長XMLデータ 特殊 bit cursor timestamp sysname sql_variant table hierarchyid geometry geography ,016 テーブルの定義内容による ,060 (Undocumented) 0 8,060 (Undocumented) 1 または 0 カーソルへの参照を格納 データベース タイムスタンプ値 オブジェクト名を格納するユーザー定義データ型 (nvarchar(128)) int 値 binary 値 および char 値を格納できる テーブル形式のデータを格納 階層内の位置情報を格納する CLR のデータ型 ユークリッド座標系のデータを格納する CLR データ型 球体地球座標系のデータを格納する CLR データ型 8,000 バイト以上のドキュメントや動画などのメディア ファイルは varbinary(max) を使用 13

14 計算列と PERSISTED の効果 PERSISTED とマークされた計算列 計算値をテーブルに物理的に保存し 計算列のいずれかが更新された場合 その値を 更新するように指定できる 次のテーブルに StockInStore と StockInWarehouse を合計した値を持つ PERSISTED とマークされた計算列 (TotalCount) を追加する 次の ALTER TABLE ステートメントを実行する ALTER TABLE Inventory ADD TotalCount AS (ProductsInStore + ProductsInWarehouse) PERSISTED 内部的にはインデックスが作成される インデックスが不要の場合は PERSISTED を記述しない 14

15 データ整合性 ドメインの整合性 列に格納される値が取りうる範囲を定義域 ドメイン と呼ぶ 例 社員年齢は 18歳 以上 65 歳未満 性別は 男性 女性 の2種類 列に適正な値が格納されるように値の種類 形式 範囲 NULL 値を 許容するかなどを規定する データ型 NULL 値許容属性 CHECK 制約 DEFAULT 制約で設定 エンティティの整合性 テーブルに格納される行の一意性の保証 PRIMARY KEY 制約 UNIQUE 制約で設定 参照整合性 テーブル間の参照関係に矛盾がない状態を維持 FOREIGN KEY 制約 トリガーで設定 15

16 データ整合性と制約 データ整合性 対象 制約 説明 ドメイン 整合性 列 DEFAULT INSERT ステートメントの VALUE 句に明示的な値が指定さ れていない場合 既定値を挿入 CHECK INSERT UPDATE ステートメント実行時に格納される値を 制限 NULL NULL 値を許可するか拒否 (NOT NULL) するかを指定 テーブル FOREIGN KEY 格納できるデータ値を同一テーブル または外部テーブルの 指定された列に格納される値の範囲に制限 テーブル PRIMARY KEY 各行を一意に識別する 内部的には インデックスが作成される NULL 値は許可されない 列 UNIQUE 各行を一意に識別する 内部的には インデックスが作成される NULL 値は 1 件入力できる テーブル FOREIGN KEY 格納できるデータ値を同一テーブル または外部テーブルの 指定された列に格納される値の範囲に制限 参照されているキー値の変更時の動作が指定できる 列 CHECK 格納できるデータ値を同一テーブルの別の列にに格納される 値の範囲に制限 エンティティ 整合性 参照整合性 16

17 制約の構文 PRIMARY KEY 制約 主キーとして設定し 列に重複した値が入力されないようにする 内部的に一意なクラスター化インデックスが作成される ALTER TABLE <テーブル名> ADD CONSTRAINT <制約名> PRIMARY KEY CLUSTERED (<列名1>, <列名n>)) FOREIGN KEY 制約 格納できるデータ値を同一テーブル または外部テーブルの指定された列に格納され る値の範囲に制限 ALTER TABLE <テーブル名> ADD CONSTRAINT <制約名> FOREIGN KEY (<列名>) REFERENCES <テーブル名> (<列名>) DEFAULT 制約 列に既定値を格納 ALTER TABLE <テーブル名> ADD CONSTRAINT <制約名> DEFAULT (<既定値>)) 17

18 参照整合性 PRIMARY KEY 制約と FOREIGN KEY 制約により実装 社員 部門 部門ID 部門名 PK 1 外部テーブル参照 18 社員ID 部門ID 上司ID 社員名 電話番号 E メール 更新日 PK FK FK 2 自己テーブル参照

19 制約の定義例 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 19

20 Q1 PRIMARY KEY 制約 問 Customers テーブルは複数のソース テーブルから顧客データをインポート する ここで Customers テーブルの各行が一意であることを保証したい CustID (PK) Name CustID (PK) Name 鈴木 和康 西 昭彦 矢嶋 聡 荒木 達也 Customers SourceID CustID Name 鈴木 和康 矢嶋 聡 荒木 達也 西 昭彦 次の CREATE TABLE ステートメントを実行する CREATE TABLE Customers ( SourceID int NOT NULL,CustID int NOT NULL,Name nvarchar(255) NOT NULL,CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (SourceID, CustID)) 20

21 Q2 FOREIGN KEY 制約 テーブル間の参照整合性を設定する 問 Orders テーブルの CustomerID が Customers テーブルの CustomerID の値の範囲であることを保証したい 次の ALTER TABLE ステートメントを実行する ALTER TABLE Orders ADD CONSTRAINT FX_Order_CusomerID FOREIGN KEY (CusomerID) REFERENCES Cusomers (CusomerID) 21

22 一意な値の発行 IDENTITY プロパティによる整数値の発行 IDENTITY (シード値 増分値) 列のプロパティとして設定 引数で指定されたシード値から開始し 以降 増分値を加算した値を発行 NEWID() 関数による GUID 値の発行 uniqueidentifier 型に GUID 値を格納 ネットワーク上のサーバー間で複製されるテーブルの一意性を確保 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) ) SEQUENCE オブジェクト NEXT VALUE FOR <シーケンス名> でシーケンス値を取得 CREATE SEQUENCE <オブジェクト名> AS <整数型> START WITH <初期値> INCREMENT BY <増分値> MINVALUE <最小値> MAXVALUE <最大値> (CYCLE NO CYCLE) UPDATE <テーブル名> SET <列名> = NEXT VALUE FOR <オブジェクト名> 22

23 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 23

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 24

25 SQL Server のインデックス クラスター化インデックス テーブルに 1 個 リーフノードには インデックス付けされた列の値に基づきソートされたデータ行を格納 CREATE CLUSTERED INDEX <インデックス名> ON <テーブル名>(<列名>, ) 非クラスター化インデックス テーブルに 999 個まで リーフノードには データ行へのポインタ (行識別子:RID) を格納 但し 既にクラスター化インデックスがある場合は クラスター化インデックスのキー値を格納 CREATE INDEX <インデックス名> ON <テーブル名>(<列名>, ) 一意なインデックスの作成では UNIQUE キーワードを指定する インデックス キーのサイズと複合インデックス列の数の制約 キー列の最大サイズ 900 バイト 16 列まで PRIMARY KEY 制約を設定した列 他の列にクラスター化インデックスが存在しない場合 自動的に一意のクラスター化インデックスが作成される 他の列にクラスター化インデックスが存在する場合 一意の非クラスター化インデックスが作成される 25

26 クラスター化インデックスの構造 ProductID にクラスター化インデックスを作成した場合 完全一致 検索クエリ SELECT ProductID,ProductNumber,Color FROM Production.Product WHERE ProductID = 714 生成される 実行プラン Clustered Index Seek ① クラスター化インデックス ProductID ページ: : :142 ② ページ:141 ページ: : :101 ProductID で 並び替えられ たデータ行 リンクリスト ③ ページ: FR-R92B FR-R92R-58 Black Red 707 HL-U509-R Red 708 HL-U509 Black 709 SO-B909-M White 26 01:102 01:103 ページ:102 ページ:101 ページ: SO-B909-L White 715 LJ-0192-L Multi 720 FR-R92R-52 Red 711 HL-U509-B Blue 716 LJ-0192-X Multi 721 FR-R92R-56 Red 712 CA-1098 Multi 717 FR-R92R-62 Red 722 FR-R38B-58 Black 713 LJ-0192-S Multi 718 FR-R92R-44 Red 723 FR-R38B-60 Black 714 LJ-0192-M Multi 719 FR-R92R-48 Red 724 FR-R38B-62 Black

27 非クラスター化インデックスの構造 ProductID に非クラスター化インデックスを作成した場合 SELECT ProductID,ProductNumber,Color FROM Production.Product WHERE ProductID = 714 完全一致 検索クエリ 生成される 実行プラン Index Seek + RID Lookup ① 非クラスター化インデックス ページ:170 ② ProductID ページ: : : :120:03 01:110:04 01:110:02 01:120:01 01:100:05 ④ ③ RID ファイル番号: ページ番号:行番号 ページ: :110:03 01:130:04 01:100:04 01:110:01 01:120:05 RID LOOKUP ヒープ ページ:100 ページ:110 ページ: :100:03 01:130:03 01:120:04 01:120:02 01:110:05 ページ:120 01:130:02 01:130:01 01:130:05 01:100:01 01:100:02 ページ: FR-R38B-60 Black 713 LJ-0192-S Multi 708 HL-U509 Black 721 FR-R92R-56 Red 724 FR-R38B-62 Black 707 HL-U509-R Red 718 FR-R92R-44 Red 720 FR-R92R-52 Red Black 716 LJ-0192-X Multi 711 HL-U509-B Blue 722 FR-R38B-58 Black 715 LJ-0192-L Multi 710 SO-B909-L White 680 FR-R92B CA-1098 Multi 706 FR-R92R-58 Red 717 FR-R92R-62 Red 709 SO-B909-M White 719 FR-R92R-48 Red 714 LJ-0192-M Multi ページ: : :183 ページ:181 ページ:180 ファイル番号: ページ番号 : :172

28 スキーマ データベース オブジェクトはスキーマ に置かれる アプリケーションに対する名前空間の形成 <サーバー名>.<データベース名>.<スキーマ名>.<オブジェクト名> インスタンス データベース データベース Production dbo Sales dbo スキーマ スキーマ作成の基本構文 CREATE SCHEMA <スキーマ名> AUTHORIZATION <所有者名> スキーマ変更の基本構文 ALTER SCHEMA <新しいスキーマ名> TRANSFER <既存のスキーマ名>.<オブジェクト名> 28

29 章の構成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 29

30 ビューとは? クエリにより定義される仮想テーブル ビューが参照するベース テーブルに対するフィルターの役目をする ベース テーブルへの直接のアクセスを許可したくない場合に使用 ビューの種類 標準のビュー 1 つ以上のベース テーブルまたは ビューのデータを 1 つの新しい仮想テーブル に組み合わせる インデックス付きビュー ビューに一意なクラスター化インデックスを作成することにより ビューを具体化 (永続化) する パーティション ビュー 1 つ以上のサーバーに分散した 1 つ以上のベース テーブルを基に 行方向にパー ティション分割されたデータを結合する 30

31 標準のビューの定義例 部門テーブル 社員テーブル 部門ID 部門名 社員ID 部門ID 社員名 電話番号 生年月日 更新日 10 企画部 鈴木 和康 〇 〇 1972/1/ /4/1 11 営業部 矢嶋 聡 〇 1978/4/3 2004/4/1 12 生産部 荒木 達也 〇 1974/12/5 2001/4/ 西 昭彦 〇 1969/10/ /4/1 部門名 社員名 電話番号 企画部 鈴木 和康 〇 〇 営業部 矢嶋 聡 〇 営業部 荒木 達也 〇 vw_部門別社員情報 CREATE VIEW vw_部門別社員情報 WITH SCHEMABINDING AS SELECT 部門名,社員名,電話番号 FROM dbo.部門 JOIN dbo.社員 ON 部門.部門ID = 社員.部門ID WHERE 部門.部門ID IN (10,11) WITH CHECK OPTION WITH SCHEMABINDING ベース テーブルの定義にビューをバインド ベース テーブルに対し ビュー定義に影響を与える変更は行えない WITH CHECK OPTION 行がビューに表示されなくなるような変更は実行できない 31

32 ビューを使用したデータの変更 元になる複数のテーブルを同時に変更することはできない 以下の列は変更できない 集計関数または計算から得られた列を変更できない GROUP BY HAVING または DISTINCT 句の影響を受ける列を変更でき ない 32

33 インデックス付きビュー インデックス付きビューの利点 ビューを永続化し パフォーマンスを向上させる クエリ オプティマイザーがクエリの解決にビューを使用できるようになる インデックス付きビューの選択シナリオ パフォーマンス向上がメンテナンスのオーバー ヘッドより重要 基になるテーブルのデータが頻繁に変更されない クエリで多数のテーブルの結合や集計を実行する インデックス付きビューの要件 ビューに作成する最初のインデックスの種類は一意なクラスター化インデックス SCHEMABINDING オプションを使用する スキーマ バインドによって ベース テーブルのスキーマにビューをバインドする ビューから他のビューは参照できない ベース テーブルはビューと同じデータベース内にあり ビューと同じ所有者にする ビューが参照するテーブルやユーザー定義関数は <スキーマ名>.<オブジェクト名> の 2 部構成の形式で参照する 1 部 3 部 または 4 部構成の形式で構成されるオブジェクト名は使用できない ビューから非決定的関数は参照できない GROUP BY を記述する場合 ビュー定義に COUNT_BIG(*) を記述する 33

34 Q5 インデックス付きビューの定義 問 次の要件を満たす dbo.vwtokyostudents という名前のビューを定義する Province が Tokyo の生徒のすべての情報を表示する ビューから Address テーブルにデータを挿入する場合 Province が Tokyo 以外 の生徒の情報は 挿入させないようにする 作成するビューにインデックスを作成できるようにする 次の CREATE VIEW ステートメントを実行する 34 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

35 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) 35

36 章の構成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 36

37 トリガーとは? 特別な種類のストアド プロシージャ トリガーとトリガーを引き起こしたステートメントは 1 つのトランザクショ ンに含まれる DML トリガー テーブルに作成 INSERT UPDATE または DELETE ステートメントによりテーブル変更時 にデータベース エンジンにより起動される 2 つの種類 AFTER トリガー INSERT UPDATE または DELETE ステートメントの実行後に起動される INSTEAD OF トリガー テーブル またはビューに作成 INSERT UPDATE または DELETE のステートメントの代わりに起動される DDL トリガー サーバーに作成 DDL ステートメント実行時に起動される データベースオブジェクトの変更を監査 37

38 DMLトリガー定義の基本構文 DML トリガー CREATE TRIGGER <スキーマ名>.<トリガー名> ON {<テーブル名> <ビュー名>} {FOR AFTER INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} AS BEGIN END FOR と記述した場合 AFTER と同じ 38

39 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 FROM DELETED 39

40 DML INSTEAD OF トリガー トリガーを起動するステートメントの動作を置き換える 使用例 1 つ以上の列に対するエラー チェックや値チェックの実行 行の挿入 更新 削除操作に対する追加の処理を実行 UNION ALL や INNER JOIN で結合された複数のベース テーブルを持つ ビューに対する挿入 更新 削除操作 40

41 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 41

42 Q7 ビューへのトリガーの作成 リレーションシップが設定された Employee と Address テーブル 各テーブルの主キー列には IDENTITY プロパティが設定されている 次の定義文で vwemployeeinfo という名前のビューを作成した ビューに対して INSERT や INSERT SELECT ステートメントが実行された場合 Employee と Address テーブルにデータの挿入を可能にするためのトリガーを定義する 42 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 int INSERT INTO dbo.address(province,city,address1,address2) SELECT Province,City,Address1,Address2 FROM INSERTED = SCOPE_IDENTITY() INSERT INTO dbo.employee(lastname,firstname,birthdate,addressid) SELECT LastName,FirstName,BirthDate,@AddressID FROM INSERTED END

43 コースの概要 第1章 データベース オブジェクトの作成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 第2章 データの取得と変更 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DML ステートメントによるデータの変更 第3章 関数とストアド プロシージャ 関数 ストアド プロシージャ 第4章 クエリのトラブル シューティングと最適化 クエリの最適化 トランザクション管理 エラー処理 43

44 章の構成 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DMLステートメントによるデータの変更 44

45 SELECT ステートメント 基本構文 SELECT 選択リスト [ INTO 新しいテーブル名 ] FROM <スキーマ名>.<テーブル名> [ WHERE 検索条件 ] [ ORDER BY 並べ替えで使用する列名 [ ASC DESC ] ] テーブルの結合 (ANSI 89) 内部結合のみ SELECT 選択リスト FROM <スキーマ名>.<テーブル名>, <スキーマ名>.<テーブル名> WHERE 結合条件 テーブルの結合 (ANSI 92) SELECT 選択リスト FROM <スキーマ名>.<テーブル名> JOIN <スキーマ名>.<テーブル名> ON 結合条件 45

46 テーブルの結合 社員と部門テーブルの定義内容 各テーブルに格納されたデータ 46

47 内部結合 内部結合 SELECT d.departmentname,e.employeename FROM dbo.department d INNER JOIN dbo.employee e ON d.departmentid = e.departmentid 結果セット 47

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 結果セット 48

49 外部結合 完全外部結合 SELECT d.departmentname,e.employeename FROM dbo.department d FULL OUTER JOIN dbo.employee e ON d.departmentid = e.departmentid 結果セット 49

50 クロス結合 クロス結合 SELECT d.departmentname,e.employeename FROM dbo.department d CROSS JOIN dbo.employee e 結果セット 50

51 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 51

52 サブ クエリの記述 SELECT INSERT UPDATE および DELETE ステートメントの内部 または別のサブクエリの内部で入れ子になっているクエリ 別のクエリの結果セットに依存するクエリ 式が使えるところであればどこにでも記述できる SOME ANY サブ クエリが返すスカラー値のいずれかの値と比較 比較演算子の条件が一つでも True になる場合 True を返す 条件がすべて False になる場合 False を返す ALL サブ クエリが返すスカラー値のすべての値と比較 比較演算子の条件がすべて True になる場合 True を返す 条件が一つでも False になる場合 False を返す 52

53 SOME ANY ALL IN 演算子 テーブルに格納されているデータ 比較演算子の条件が一つでも True になる場合 True を返す (<> ANY と同等) -- 社員の Tanaka が所属していない部署 SELECT * FROM dbo.department WHERE DepartmentID <> SOME (SELECT DepartmentID FROM dbo.employee WHERE EmployeeName = 'Tanaka') 比較演算子の条件がすべて True になる場合 True を返す (NOT IN と同等) -- 社員が誰も所属していない部署 SELECT * FROM dbo.department WHERE DepartmentID <> ALL (SELECT DepartmentID FROM dbo.employee) 53

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) 54

55 共通テーブル式 (CTE) ANSI-99 SQL 規格に準拠した一時的な名前付き結果セット 派生テーブルの改良 一度だけ評価されステートメントが完了するまで保持される 複数回の参照 自己参照が可能 WITH 句で開始 WITH <CTE 名> AS (<SELECT ステートメント>) <DML ステートメント> 名前 AS に続き丸括弧内に表現式 複数の CTE を記述する場合 カンマで区切る 結果セットを返す SELECT ステートメントが続く Sales_CTE という名前 の CTE の定義 CTE のデータを取得する SELECT CTE を参照する SELECT 55 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 ORDER BY SalesPersonID, SalesYear GO

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() ) 56

57 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 57

58 章の構成 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DMLステートメントによるデータの変更 58

59 集計クエリの 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 ORDER BY ProductID 59

60 Q12 集計クエリ 問 次の集計クエリを記述しましたが 実行するとエラーになる SELECT SalesOrderID,ProductID,AVG (OrderQty),SUM (UnitPrice),MAX (UnitPriceDiscount) FROM Sales.SalesOrderDetail この集計クエリを次の要件を満たすようにして完成する SELECT と FROM 句は上記クエリを利用する SalesOrderID ごとに降順に並び替えて ProductID ごとに集計する ProductID は昇順に表示する SELECT SalesOrderID,ProductID,AVG (OrderQty),SUM (UnitPrice),MAX (UnitPriceDiscount) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID,ProductID ORDER BY SalesOrderID DESC,ProductID ASC 60

61 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 61

62 ROLLUP CUBE GROUPING SETS ROLLUP 単純な GROUP BY 集計行と小計行 および総計行を生成 SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_ordersummary GROUP BY ROLLUP (CustomerID, ProductID) CUBE ROLLUP 集計行とクロス集計行を生成 SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_ordersummary GROUP BY CUBE (CustomerID, ProductID) GROUPING SETS 指定したグループの集計のみを生成する 62 SELECT CustomerID, ProductID, SUM([Total Amount] AS Total FROM dbo.v_ordersummary GROUP BY GROUPING SETS ((CustomerID, (ProductID), (ProductID), ())

63 順位付け関数 順位付けの対象となる行セットのパーティション分割と並べ替え方法を指定 順位付け関数 説明 ROW_NUMBER() 順序番号を生成 RANK() 対象行より 1 つ前の行数に 1 を加算した値 (同じ順位の行には同じ順位番号が付けられ 番号が飛ぶ) DENSE_RANK() 対象行より 1 つ前の行の順位数に 1 を加算した値 NTILE() 対象の結果セットを指定された数で分割 構文 SELECT <選択リスト>,<順位付け関数>RANK() OVER (ODER BY <列名> [ ASC DESC ] ) AS <別名> FROM <スキーマ名>.<テーブル名> ORDER BY <スキーマ名>. <列名> [ ASC DESC ] 63

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 結果セット 64

65 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 65

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 66

67 LAG 関数と LEAD 関数 LAG 関数 自己結合を使用せずに同じ結果セットの前の行からデータにアクセス 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') LEAD 関数 自己結合を使用せずに同じ結果セットの後続の行からデータにアクセス 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') 67

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 68

69 章の構成 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DMLステートメントによるデータの変更 69

70 XML クエリ SQL クエリを XML データに変換 FOR XML モード 70 FOR XML モード XML の構造 RAW 行セットを汎用識別子 <row> を持つ XML に変換 属性(既定) または要素 (ELEMENTS ディレクティブを記述) に展開 AUTO 行セットを XML に変換 複数テーブルの親子関係を階層に反映 属性(既定) または要素 (ELEMENTS ディレクティブを記述) に展開 PATH XML 構造の厳密な制御が可能 列の別名に XPath 式を記述し要素と属性の展開パターン および階層構造 を制御する(要素 (既定) または属性(AS 句で XPath 式を記述) に展開)

71 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=" " SalesAmount=" " Name="Tanaka" City="Chiba" /> <row OrderID="6" OrderDate=" " SalesAmount=" " Name="Tanaka" City="Chiba" /> 71

72 Q17 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 AUTO <Orders OrderID="1" OrderDate=" " SalesAmount=" "> <Customers Name="Tanaka" City="Chiba" /> </Orders> <Orders OrderID="6" OrderDate=" " SalesAmount=" "> <Customers Name="Tanaka" City="Chiba" /> </Orders> 72

73 Q18 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 AUTO,ELEMENTS 73 <Orders> <OrderID>1</OrderID> <OrderDate> </OrderDate> <SalesAmount> </SalesAmount> <Customers> <Name>Tanaka</Name> <City>Chiba</City> </Customers> </Orders>

74 Q19 XML クエリ 適切な FOR XML モードを指定する SELECT 属性として展開 Name AS AS 属性として展開,OrderID,OrderDate,SalesAmount FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1 FOR XML PATH ('Customers') 74 <Customers Name="Tanaka" City="Chiba"> <OrderID>1</OrderID> <OrderDate> </OrderDate> <SalesAmount> </SalesAmount> </Customers> <Customers Name="Tanaka" City="Chiba"> <OrderID>6</OrderID> <OrderDate> </OrderDate> <SalesAmount> </SalesAmount> </Customers>

75 章の構成 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DML ステートメントによるデータの変更 75

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 76

77 バイナリ ラージ オブジェクト (BLOB) の更新 BLOB データ型 varchar(max) nvarchar(max) varbinary(max) 8000 バイト以上のデータを格納 varbinary(max) には文書ファイル 画像 動画などを格納できる Windows API からもアクセスする場合は FILESTREAM 属性を指定 データの更新方法 UPDATE ステートメントに列に対する WRITE メソッドを記述 WRITE メソッドの構文 <列名>.WRITE サンプル コード 77 table ( SummaryBefore nvarchar(max), SummaryAfter nvarchar(max)) GO UPDATE Production.Document SET DocumentSummary.WRITE (N'features',28,10) OUTPUT DELETED.DocumentSummary, INSERTED.DocumentSummary WHERE Title = N'Front Reflector Bracket Installation' GO SELECT SummaryBefore, SummaryAfter

78 MERGE ステートメント ANSI SQL 2006 準拠 データソースを対象のテーブル またはビューと結合し その結合結果に基づい て複数の操作を実行 単一ステートメントで INSERT UPDATE および DELETE 操作を実行できる 複数の行セット入力に使用 MERGE <対象テーブル> USING <ソース テーブル> ON <結合条件> WHEN MATCHED THEN UPDATE SET <対象テーブル>.<列名> = <ソース テーブル>.<列名> WHEN MATCHED AND <式> THEN DELETE WHEN NOT MATCHED BY TARGET THEN INSERT VALUES( ) 78

79 Q20 MERGE ステートメント 問 TerritoryInfo テーブルのデータをメンテナンスするストアドプロシージャ を完成させる TerritoryCode が存在する場合は更新する TerritoryCode が存在しない場合は挿入する CREATE PROCEDURE nvarchar(10) AS BEGIN SET NOCOUNT ON MERGE dbo.territoryinfo AS target @Country) AS source (TerritoryCode, TerritoryName, Country) ON (target.territorycode = source.territorycode) WHEN MATCHED THEN UPDATE SET target.territoryname = source.territoryname, target.country = source.country WHEN NOT MATCHED BY TARGET THEN INSERT(TerritoryCode, TerritoryName, Country) VALUES(source.TerritoryCode, source.territoryname, source.country); END 79

80 コースの概要 第1章 データベース オブジェクトの作成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 第2章 データの取得と変更 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DML ステートメントによるデータの変更 第3章 関数とストアド プロシージャ 関数 ストアド プロシージャ 第4章 クエリのトラブル シューティングと最適化 クエリの最適化 トランザクション管理 エラー処理 80

81 章の構成 関数の使用 関数の作成 ストアド プロシージャの作成と変更 81

82 CONVERT() 関数 値を指定されたデータ型に変換する 基本構文 CONVERT ( <データ型>, <値> ) 使用例 CREATE PROCEDURE varchar(20) AS SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate > CONVERT(datetime,@Parameter1) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(date, GETDATE()) AND PurchaseTime < DATEADD(day, 1, CONVERT(date, GETDATE())) 82

83 TRY_PARSE() 関数 値を指定されたデータ型に変換する 変換に失敗する場合 NULL を返す 基本構文 TRY_PARSE (<値> AS <データ型> ) 使用例 varchar(max) = ' ' SELECT IIF(TRY_PARSE(@var AS decimal(10,3)) IS NULL, 'True', 'False' ) AS Result 83

84 FORMAT() 関数 形式とオプションのカルチャを指定し 書式設定された値を返す 基本構文 FORMAT (<値>, <形式> [, <カルチャ>] ) 使用例 SELECT FORMAT(GETDATE(), 'yyyy-mm-dd', 'ja-jp') カルチャ情報に基づく各国対応した形式への変換が可能 84

85 EOMONTH 関数 オプションのオフセットを使用し 指定された日付の月の最終日を返す 基本構文 EOMONTH ( <日付> [, <月数>] ) 使用例 DATETIME = GETDATE() SELECT EOMONTH ) AS '当月末日' SELECT EOMONTH 1 ) AS '来月末日' SELECT EOMONTH -1 ) AS '先月末日' 85

86 章の構成 関数の使用 関数の作成 ストアド プロシージャの作成と変更 86

87 関数を使用するシナリオ SELECT などの Transact-SQL ステートメント内で使用する 関数を呼び出すアプリケーション内で使用する 別のユーザー定義関数の定義内で使用する ビューをパラメータ化したり インデックス付きビューの機能を向上さ せる テーブルの計算列を定義する 他のテーブルを参照するような場合 列の CHECK 制約を定義する 87

88 関数の種類 スカラー値関数 単一値を返す 式を記述できる箇所で使用 テーブル値関数 結果セットを返す FROM 句に使用 パラメータを持つビューに似ている 88

89 関数定義の基本構文 インライン関数 単一の SELECT ステートメントの結果を返す CREATE FUNCTION スキーマ名.関数名 データ型, ) RETURNS データ型 [WITH オプション] AS RETURN( SELECT 列名1,列名2, FROM スキーマ名.テーブル名 WHERE 行の検索条件) 複数ステートメント関数 BEGIN END ブロック構造 CREATE FUNCTION スキーマ名.関数名 (@パラメータ名1 データ型, ) RETURNS データ型 [WITH オプション] AS BEGIN RETURN END 89

90 スカラー値関数の例 RETURNS 句で戻り値のデータ型を指定 CREATE FUNCTION dbo.leapyearcheck datetime) RETURNS int AS BEGIN int bit = YEAR(@Date) IF((@Year % 4 = 0 % 100!= 0) % 400 = 0) = 1 ELSE = 0 RETURN (@LeapYear) END 同じデータ型のスカラー式が許容される任意の場所で呼び出せる SELECT FirstName,LastName,year(BirthDate) AS BirthYear,dbo.LeapYearCheck(BirthDate) AS LeapYear FROM HumanResources.Employee 90

91 テーブル値関数の例 RETURNS に table データ型を指定 関数の内容は 単一の SELECT ステートメント 返されるデータ構造はクエリ定義によって決まる 依存するテーブルの構造を変更させたくない場合 WITH 句に SHEMABINDING を記述 CREATE FUNCTION dbo.getstockqty int) RETURNS table WITH SCHEMABINDING AS RETURN( SELECT l.name,pi.bin,pi.shelf,pi.quantity FROM Production.ProductInventory pi INNER JOIN Production.Location l ON pi.locationid = l.locationid WHERE ProductID ) FROM 句に記述して呼び出す SELECT * FROM dbo.getstockqty(2) 91

92 テーブル値関数の結合 CROSS APPLY を使用したテーブル値関数の結果との結合 外部テーブルを左入力 テーブル値関数は右入力とする SELECT cp.plan_handle,object_name(qp.objectid) AS 'object_name',qp.query_plan,cp.objtype FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE cp.objtype ='Proc' 外部結合の場合は OUTER APPLY を記述 92

93 章の構成 関数の使用 関数の作成 ストアド プロシージャの作成と変更 93

94 ストアド プロシージャとは? Transact-SQL ステートメント または Microsoft.NET Framework コードのコレクションに名前を付けたもの EXECUTE ステートメントで実行 入力パラメータを受け入れ 出力パラメータ値を返す 成功または失敗を示すステータス値を返す ストアド プロシージャの種類 システム ストアド プロシージャ 拡張システム ストアド プロシージャ ユーザー定義の Transact-SQL ストアド プロシージャ ユーザー定義の SQLCLR ストアド プロシージャ 関数との違い エラー ハンドリングが可能 DDL ステートメントが記述できる EXECUTE で呼び出すため クエリやビューの中には記述できない 94

95 ストアド プロシージャの定義 ストアド プロシージャの構文 CREATE PROCEDURE プロシージャ名 入力パラメーター1 データ型 [= 既定値 入力パラメーター2 データ型 [= 既定値 出力パラメーター1 データ型 出力パラメーター2 データ型 OUTPUT], WITH オプション AS SQLステートメント 主要なオプション ENCRYPTION CREATE PROCEDURE ステートメントの元のテキストを 暗号化した形式に変換 することを EXECUTE AS プロシージャを実行するコンテキストの指定 RECOMPILE プロシージャのプランをキャッシュしない 95

96 EXECUTE AS による実行コンテキスト制御 所有者が異なるオブジェクトの場合 EXECUTE AS で所有者に偽装できる 鈴木さんが所有する ストアドプロシージャ (EXECUTE AS 佐藤 ) 山本さん 佐藤さんが所有する テーブル 山本さんの EXECUTE 権限が チェックされる 佐藤さんの SELECT 権限が チェックされる EXECUTE AS に設定できる引数 96 引数 説明 CALLER モジュール内のステートメントは モジュールを実行しているユーザー本人のコンテキストで 動作するため モジュールを実行したユーザーは そのモジュールが参照するすべてのデータ ベース オブジェクトに対して適切な権限が付与されている必要がある OWNER モジュール内のステートメントを モジュールの所有者のコンテキストで実行する 所有者が 指定されていないモジュールについては そのモジュールのスキーマの所有者のコンテキスト が使用される この引数は DDLトリガーまたはログオントリガでは指定できない データベースユーザー名 指定したデータベースユーザーのコンテキストで実行する サーバースコープの DDL トリ ガー またはログオントリガーには指定できない ログイン名 指定したログインのコンテキストで実行する サーバースコープの DDL トリガーまたはログ オン トリガーにのみ指定できる

97 ストアド プロシージャ定義と実行 プロシージャの定義 出力パラメーターの指定 OUTPUT 句を指定すると呼び出し元に値が返る 戻り値の指定 RETURN 句を指定すると呼び出し元に int 型の値が返る CREATE PROCEDURE money OUTPUT AS SET NOCOUNT ON = SalesPrice FROM dbo.products WHERE ProductID RETURN プロシージャの実行 97 出力パラメーターの指定 OUTPUT 句を指定した変数に値が渡る money int = dbo.usp_getproductinfo 771,@Price OUTPUT PRINT N'Return Code = ' + CAST(@RC AS nvarchar(8)) + N' Price = ' + CAST(@Price AS nvarchar(8))

98 ストアド プロシージャ実行時の情報取得 直前に実行したステートメントの整数値のエラー番号を返す 次のステートメントの実行前に変数に退避しないと上書きされる 直前に実行したステートメントに影響を受けた行数を返す 次のステートメントの実行前に変数に退避しないと上書きされる int int EXECUTE usp_deleteproducts = IF (@Error <> 0) PRINT N'ERROR = ' + CAST(@Error AS nvarchar(8)) + CAST(@Rowcount AS nvarchar(8)) + N' Rows Deleted!' 98

99 カーソルを使用した呼び出しの例 結果セットの値によりプロシージャを実行したい場合などに使用する int DECLARE contact_cursor CURSOR FOR SELECT EmployeeID FROM dbo.employee OPEN contact_cursor FETCH NEXT FROM contact_cursor WHILE = 0 BEGIN EXECUTE FETCH NEXT FROM contact_cursor END CLOSE contact_cursor DEALLOCATE contact_cursor GO 99

100 Q21 ストアド プロシージャの作成 問 次の要件を満たすストアド という名前のひとつの int 型のパラメーターを使用する usp_getproductinfoという名前 のストアド プロシージャを作成する パラメーターで指定された値の行数のみを返す SalesPrice の値を降順で表示する 定義内容を暗号化する 次の CREATE PROCEDURE ステートメントを実行する CREATE PROCEDURE int WITH ENCRYPTION AS SELECT Products.Name,Products.SalesPrice FROM dbo.products ORDER BY Products.SalesPrice DESC 100

101 コースの概要 第1章 データベース オブジェクトの作成 T-SQL を使用したテーブルの作成と変更 ビューの作成と変更 DML トリガーの作成と変更 第2章 データの取得と変更 SELECT ステートメントによる基本的なクエリ 集計クエリと順位付け XML データへのクエリ DML ステートメントによるデータの変更 第3章 関数とストアド プロシージャ 関数 ストアド プロシージャ 第4章 クエリのトラブル シューティングと最適化 クエリの最適化 トランザクション管理 エラー処理 101

102 章の構成 クエリの最適化 トランザクション管理 エラー処理 102

103 実行プラン クエリ エンジンが生成するクエリ プランとユーザー コンテキスト クエリ プラン ユーザー コンテキストを含まない再入可能な読み取り専用のデータ構造体 実行コンテキスト 検索条件の値やパラメータ値など実行に固有のデータ 103

104 クエリ プラン生成のフロー クエリ エンジン 新しいステートメント 言語処理 実行プランを発見 コンパイル済みプラン を発見 プロシージャ キャッシュ の検索 プランが発見できない クエリ オプティマイザ クエリ実行 解析 自動パラメータ化 ビルド ビューの展開 インデックス 統計情報 クエリの最適化 インデックスと統計情報を参照 実行プランを生成 メモリ割当ての許可 と並列プラン化 実行 プランをキャッシュに格納 104

105 実行プランの表示と制御 ステートメント解析時に推定のグラフィカル プランを表示 SET SHOWPLAN_XML ON GO ステートメント解析時に推定のテキスト プランを表示 SET SHOWPLAN_ALL ON GO ステートメント実行時に実際に使用したグラフィカル プランを表示 SET STATISTICS XML ON GO ステートメント実行時に実際に使用したテキスト プランを表示 SET STATISTICS PROFILE ON GO グラフィカル プランは クエリ実行に不足するインデックスが見つかると推奨 されるインデックスを提案する機能も含む 105

106 クエリの最適化 アドホック クエリの最適化 optimize for ad hoc workloads サーバー構成オプション 毎回 動的に作成される再利用性が低いアドホック クエリのプランでプ ロシージャ キャッシュがいっぱいにならないように指定できる EXECUTE sys.sp_configure N'optimize for ad hoc workloads', N'1' GO RECONFIGURE WITH OVERRIDE GO 106

107 統計情報の表示 統計のプロパティ 107

108 手動による統計情報の作成と更新 統計情報の作成 CREATE STATISTICS テーブル サイズが大きい場合 サンプリングにより統計情報を生成できる CREATE STATISTICS <統計情報名> ON { <テーブル名> <ビュー名> } ( <列名> [,...n ] ) [ WHERE <フィルター述部> ] [ WITH [ [ FULLSCAN SAMPLE <数値> { PERCENT ROWS }[, ] ] [ NORECOMPUTE ] ] 統計情報の手動更新 統計情報の行数見積もりと実際の行数が異なるような場合 実行する 更新のためのステートメント UPDATE STATISTICS sp_updatestats 108

109 章の構成 クエリの最適化 トランザクション管理 エラー処理 109

110 トランザクションとは 論理的な 1 つの作業単位として実行される一連の操作 トランザクション処理に要求される ACID プロパティ 110 ACID プロパティ 説明 原子性 (Atomicity) トランザクションは分離できない 1 つの作業単位 トランザクション内のデータ変更は すべて実行されるか まっ たく実行されないかのどちらかでなければならない 一貫性 (Consistency) トランザクション完了時 すべてのデータが一貫した状態になけ ればならない 分離性 (Isolation ) 同時実行トランザクションからの変更は 他のトランザクション によって行われる変更から独立している 持続性 (Durability ) トランザクション中にインスタンス停止が発生しても完了したト ランザクションによる変更結果は持続されなければならない

111 トランザクションの記述 暗黙的なトランザクション 明示的なトランザクション SET IMPLICIT_TRANSACTIONS ON で開始 トランザクションは自動的に開始され 終了または取り消しを記述 ANSI-92 標準 Oracle の既定モード SET IMPLICIT_TRANSACTIONS ON 処理1 トランザクション 明示的にトランザクションの開始 終了 取り消しを記述 明示的に開始しない場合ステートメント 実行時に一文ずつ確定する(自動コミット モード) SQL Server の既定モード BEGIN TRAN TRN1 処理1 処理2 処理2 COMMIT COMMIT TRAN TR1 処理3 ROLLBACK トランザクション トランザクション BEGIN TRAN TRN2 処理3 トランザクション ROLLBACK TRAN TR2 111

112 XACT_ABORT ステートメント実行時にエラーが発生した場合 トランザクション全体 を確実にロールバックさせる場合 XACT_ABORT を ON に設定する 例文 SET XACT_ABORT ON GO XACT_ABORT が OFF の場合 エラーの重大度レベルにより エラー が発生したステートメントのみが ロールバックされ トランザクショ ン処理は継続される場合がある 112

113 既定のトランザクション分離レベルでのロック動作 基本的なロック モード ロックモードの種類 説明 共有 (Shared:S) SELECT ステートメントなどデータ変更を伴わない読み取り操作で使用 更新 (Update:U) INSERT UPDATE DELETE ステートメントによるデータ変更を前提とした読み取り操作で使用 排他 (exclusive:x) INSERT UPDATE DELETE ステートメントによるデータ変更で使用 基本的なロックの互換性 要求されたモード 既に許可されているモード IS S U IX X インテント共有 (IS) 共有 (S) 更新 (U) インテント排他 (IX) 排他 (X) トランザクション分離レベルで制御可能 113

114 ロック ヒント オプション DML 文で指定 既定は行ロック INSERT ステートメントを テーブル ロックを使用して実行する例 INSERT INTO dbo.orderhistorical WITH (TABLOCK) SELECT * FROM dbo.ordersummary データベース (S) テーブル (IX) ページ (IX) 行 114 ロック ヒントの種類 ロック ヒント トランザクション分 離レベルの指定 SERIALIZABLE REPEATABLEREAD READCOMMITTED READCOMMITTEDLOCK READUNCOMMITTED NOLOCK ロック粒度の指定 ROWLOCK PAGLOCK TABLOCK TABLOCKX ロック期間の指定 HOLDLOCK ロック モードの指定 UPDLOCK XLOCK (X)

115 トランザクションの同時実行時の問題 複数のユーザーにより開始されるトランザクション間の相互関係 同時に処理しても良いか? もしくは完全な分離が必要か? トランザクション実行時の同時実行時の問題 115 発生する問題 説明 更新データの損失 更新されたデータが上書きされる ダーティ リード 未コミットデータが読み込まれる 反復不可能読み取り 参照の都度 変化するデータ ファントム 現れたり消えたりするレコード

116 トランザクション分離レベルの種類 ロック方式 (ANSI/ISO 標準) READ UNCOMMITED (未コミット読み取り) READ COMMITTED (コミット済み読み取り) データベース エンジンの既定の分離レベル REPEATABLE READ (再読み込み可能読み取り) SERIALIZABLE (直列化) SET ステートメントによりセッション レベルで制御可能 SET TRANSACTION ISOLATION LEVEL <トランザクション分離レベル名> マルチ バージョニング方式 (SQL Server 2005 よりサポート) READ COMMITTED SNAPSHOT SNAPSHOT (スナップショット分離) バージョン管理 更新時間と SELECT ステートメント開始時のトランザクション シーケンス番号(XSN)を使用 して tempdb 上に確実に更新前イメージが残るように管理される tempdb 上のデータは 1 分ごとにチェックされ 参照される可能性がなくなった時点でガベー ジ コレクションによって自動的に削除される 参考 - 参考

117 トランザクション分離レベルと同時実行制御 問題点 ダーティ リード 反復不可能読 み取り ファントム 同時実行制御 分離レベル READ UNCOMMITTED 可 可 可 READ COMMITTED 不可 可 可 REPEATABLE READ 不可 不可 可 SERIALIZABLE 不可 不可 不可 READ COMMITTED SNAPSHOT SNAPSHOT 不可 可 可 物理排他 (悲観的ロック) 物理排他 (悲観的ロック) 物理排他 (悲観的ロック) 物理排他 (悲観的ロック) 論理排他 (楽観的ロック) 不可 不可 不可 117 論理排他 (楽観的ロック)

118 ロック方式 READ COMMITTED (既定) TX1 BEGIN TRANSACTION UPDATE Products SET Price = 263 WHERE ID = 1001 T2 Price COMMIT TRANSACTION T4 Price T5 T6 SELECT ID,Price FROM Products WHERE ID = 1001 更新後データを取得 T7 118 SELECT ID,Price FROM Products WHERE ID = 1001 ブロックされる 更新後データ (確定済み) ID BEGIN TRANSACTION T3 更新後データ (未確定) ID TX2 T1 ID Price COMMIT TRANSACTION

119 ロック方式 REPEATABLE READ SERIALIZABLE TX1 BEGIN TRANSACTION UPDATE Products SET Price = 263 WHERE ID = 1001 T2 Price COMMIT TRANSACTION T4 Price SELECT ID,Price FROM Products WHERE ID = 1001 ブロックされる T5 更新後データ (確定済み) ID BEGIN TRANSACTION T3 更新後データ (未確定) ID TX2 T1 T6 SELECT ID,Price FROM Products WHERE ID = 1001 ブロックされる T7 119 COMMIT TRANSACTION

120 マルチ バージョニング方式 READ COMMITTED SNAPSHOT TX1 BEGIN TRANSACTION T2 UPDATE Products SET Price = 263 WHERE ID = 1001 更新後データ (未確定) TX2 T1 T3 更新前データ ID Price ID Price tempdb COMMIT TRANSACTION T4 Price T5 T6 ID Price SELECT ID,Price FROM Products WHERE ID = 1001 ステートメント実行時点の 確定済みデータを返す 更新後データを取得 T7 120 SELECT ID,Price FROM Products WHERE ID = 1001 更新前データを取得 更新後データ (確定済み) ID BEGIN TRANSACTION ID Price COMMIT TRANSACTION

121 マルチ バージョニング方式 SNAPSHOT TX1 BEGIN TRANSACTION T2 UPDATE Products SET Price = 263 WHERE ID = 1001 更新後データ (未確定) TX2 T1 T3 更新前データ ID Price ID Price tempdb COMMIT TRANSACTION T4 Price SELECT ID,Price FROM Products WHERE ID = 1001 更新前データを取得 T5 更新後データ (確定済み) ID BEGIN TRANSACTION T6 ID Price SELECT ID,Price FROM Products WHERE ID = 1001 トランザクション開始時点 の確定済みデータを返す 更新前データを取得 T7 121 ID Price COMMIT TRANSACTION

122 READ_COMMITTED_SNAPSHOT の設定 ステートメント レベルの読み取り一貫性 SELECT を発行した時点でのデータを読み取ることを保証 更新前データは tempdb に格納 データベースに対して有効化する (既定は無効) ALTER DATABASE データベース名 SET READ_COMMITTED_SNAPSHOT ON 122

123 SNAPSHOT の設定 トランザクション レベルの読み取り一貫性 トランザクション発行時点でのデータ保証 更新前データは tempdb に格納 排他ロックを無視して旧バージョンの読み取りが可能 データベースに対して有効化(既定は無効) ALTER DATABASE データベース名 SET ALLOW_SNAPSHOT_ISOLATION ON SNAPSHOT 分離レベルを使用するセッションで分離レベルを変更 SET TRANSACTION ISOLATION LEVEL SNAPSHOT 123

124 マルチ バージョニング方式の選択肢 読み取りと書き込みの競合を回避 READ COMMITTED SNAPSHOT ステートメント レベルの読み取り一貫性 ステートメント実行時点の確定済みデータを返す SNAPSHOT トランザクション レベルの読み取り一貫性 トランザクション開始時点の確定済みデータを返す READ_COMMITED_SNAPSHOT に比べると tempdb への負荷が高い 124

125 章の構成 クエリの最適化 トランザクション管理 エラー処理 125

126 例外処理の実装 実行コードの記述 BEGIN TRY END TRY ブロック エラー処理の記述 BEGIN CATCH END BEGIN TRANSACTION BEGIN TRY EXEC dbo.usp_updatedata COMMIT TRANSACTION END TRY BEGIN CATCH IF 0 ROLLBACK TRANSACTION END CATCH 126

127 エラー情報の取得 ERROR_ 関数 TRY ブロックで発生した例外のエラー情報を返す CATCH ブロック内で値が不変 127 エラー関数の種類 返される情報 型 ERROR_NUMBER() エラー番号 int ERROR_SEVERITY() 重大度 int ERROR_STATE() エラー状態番号 int ERROR_PROCEDURE() エラーが発生したストアド プロシージャ トリガの名前 nvarchar(126) ERROR_LINE() エラーを発生させたルーチン内の行番号 Int ERROR_MESSAGE() エラーの完全なテキスト メッセージ nvarchar(2048)

128 トランザクションの検査 実行中のトランザクションの数 XACT_STATE() 関数 アクティブなトランザクションの有無 128 戻り値 説明 1 アクティブなユーザー トランザクションが存在して トランザクションのコミットなど 任意の操作を実行できる 0 アクティブなユーザー トランザクションが存在しない -1 アクティブなユーザー トランザクションが存在するが コミットできないトランザ クションとして分類されるエラーが発生した

129 Q22 トランザクションの検査 次の要件を満たすように顧客データを削除するストアドプロシージャを完成する このストアドプロシージャは 他のトランザクションから呼び出される 顧客データの削除に成功した場合 監査ログの記録に失敗してもトランザクションを コミットしたい この要件を見た右ために 14 行目に入力するべきコードは 1: CREATE PROC int 2: AS 3: BEGIN 4: int 5: BEGIN TRANSACTION 6: BEGIN TRY 7: DELETE dbo.customers WHERE Customers.CustomerID 8: 9: INSERT INTO Audit.Log(Operation,ErrorCode,OperationDate) 10: VALUES ('DELETE ' + CONVERT(varchar(10),@CustomerID),@ERROR,SYSDATETIME()) 11: COMMIT TRANSACTION 12: END TRY 13: BEGIN CATCH 14: IF (XACT_STATE()) = 1 15: COMMIT TRANSACTION 16: ELSE 17: ROLLBACK TRANSACTION 18: END CATCH 19: END 129

130 解答集 Answers Q1 (SourceID, CustID) Q12 SalesOrderID,ProductID SalesOrderID DESC,ProductID ASC Q2 FOREIGN KEY (CusomerID) REFERENCES Cusomers (CusomerID) Q13 MostRecentOrderDate DESC Q3 DEFAULT NEWID() WITH VALUES Q14 RANK() OVER (ORDER BY ProductCatalog.SalesPrice DESC) Q NEXT VALUE FOR CLASS_Sequence Q15 E Q5 WITH SCHEMABINDING AS WITH CHECK OPTION Q16 RAW Q6 UNIQUE CLUSTERED INDEX Q17 AUTO Q7 ON VwEmployeeInfo INSTEAD OF INSERT Q18 AUTO,ELEMENTS Q8 LEFT OUTER JOIN Q19 PATH ('Customers') Q9 B D Q20 UPDATE SET target.territoryname = source.territoryname,target.country = source.country INSERT(TerritoryCode, TerritoryName, Country) VALUES(source.TerritoryCode, source.territoryname, source.country); Answers Q10 cte.mostresentorderdate < DATEADD(YEAR, -2, GETDATE() Q21 WITH ENCRYPTION TOP Products.SalesPrice DESC Q11 MAX cte.updateddate > p.updateddate Q22 IF (XACT_STATE()) = 1 130

131 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. 131