第 1 回データベースの概要と SQL Server のアーキテクチャー～ MCA をめざそう!! MCA データベース Microsoft SQL Server 2000 対応編～

第 1 回データベースの概要と SQL Server のアーキテクチ...

熊倉克己

2002年3月12日

1. データベースの役割

1.1. データベースの概要

データベースは、コンピュータシステム上に構築された記憶保持システムです。データベースシステムはデータ、ハードウエア、ソフトウエア、利用者の４つの要素で構成されています。リレーショナルデータベースは実用化されはじめたのは、１９８０年代です。当時は高性能の汎用機でないと実用的に使用できるものではありませんでしたが、マイクロプロセッサの高性能化、記憶装置の大容量化・低価格化により、ノートパソコンでも、本格的なリレーショナルデータベースが使用できる様になりました。

リレーショナルデータベースが普及した背景には、他のデータベースシステムと比較して、以下の特長があったためです。

・ プログラムとデータとの独立性の高さ
・ システム開発の生産性の高さ
・ SQL （Structured Query Language） の標準化

SQL Server は Windows 上で動作する本格的なリレーショナルデータベースで、標準 SQL の規格である ANSI SQL92 に準拠しています。

リレーショナルデータベースはビジネス分野で広く使用されていますが、文字データや数値だけでなく、マルチメディアなどのデータも取り扱える様に機能が拡張されています。また、オンライントランザクション処理（OLTP）を主体とする基幹系データベースからデータウエアハウス（OLAP）を取り扱う情報系データベースの利用と、利用範囲も拡大しています。

1.2. データの独立性

従来の情報システムは、COBOL などの言語で書かれたアプリケーションプログラムと、プログラムで使用されるファイルで構成されていました。ファイルはプログラムに依存して設計され、プログラム毎にファイルレイアウトが異なったり、データが重複したりします。又、ファイルレイアウトを変更する場合は、プログラムのソースコードを変更する必要があります。これらの問題を解決したのがデータベースです。データベースには以下の3種類があります。

・ 階層型データベース
・ ネットワーク型データベース
・ リレーショナルデータベース

リレーショナルデータベースでは、プログラムとデータの独立性が実現されています。

1.3. データの統合化

また、従来のファイル処理では、プログラムの中で、ファイルとレコードのフォーマットを指定する必要がありましたが、リレーショナルデータベースでは、データ項目の指定だけで済みます。

データベースを使用することにより、データの統合化が図れます。データの統合化の目的は以下の２つです。

・ データの重複の排除
・ データの整合性の確保

データの統合化は、単にリレーショナルデータベースを使用すれば達成するものではありません。

どのようにデータベースを設計するかによります。従来の｢プロセス中心アプローチ｣ではなく、｢データ中心アプローチ｣もしくか、｢オブジェクト指向｣で行う必要があります。

図 1. データの統合化とデータ中心アプローチ

2. リレーショナルモデル

2.1. リレーショナルモデルの構造

リレーショナルデータベースは、数学の集合論に基づき設計されています。リレーショナルモデルの役割は以下の 3 つです。

・データ定義

・整合性制約

・データ操作

データ定義

リレーショナルモデルでは、互いに関係あるデータをリレーション（関係）として扱います。

リレーションは,論理的なデータの結びつきを表現します。ＳＱＬの用語ではテーブル（表）といいます。リレーショナルデータベースでは、複数のテーブルの集まりのデータベースといいます。リレーショナルデータベースでは、ディスクの物理的構造を意識せずに使用できます。データの独立性が保証されています。

リレーショナルデータベースのテーブルはお互いに関連するデータ要素を集めた行と各データ要素を表す列から構成されます。リレーショナルモデルでは、行を組（タプル）、列を属性（アトリビュート）と表現します。列はテーブル上でユニークな名前を持ちます。テーブルは行の論理的な集合として構成されますが。行には順番はなく、又、列にも順番はありません。列は、単一の値でなければならず。複数の値は配列はゆるされません。列のとり得る値の範囲をドメイン（定義域）といいます。例えば、在庫数量であれば、０以上の値となります。

2.2. リレーショナルデータベースのキー

テーブルで行を一意に特定する為の属性（列）の集まりを候補キーといいます。１つのテーブルには、候補キーが複数存在します。例えば、社員テーブルでは、社員番号やメールアドレスが考えられます。同姓同名の社員がいる場合、氏名は候補キーとはなりません。候補キーの中から、１つ選択し主キー （Primary Key）とします。主キーに選択されなかったキーは代替キー （Alternate Key）といいます。

あるテーブルの候補キーを参照する属性の集まりを外部キー (Foreign Key) といいます。

2.3. 整合性制約

データベースのデータが現実の値を正確に反映していることをデータの整合性が保たれているといいます。例えば、在庫数は正の数値でなければならないし、性別は、男もしくは女でなければなりません。データの正しさを保証するのが、整合性制約 (Integrity Constraint) です。整合性制約には以下のものがあります。

制約	説明
主キー制約	行を一意に特定します。これを主キー制約（Primary Key Constraint）といいます。主キーを構成する属性の値にNULL値は許されません。
参照制約	テーブルに外部キーがある場合、その値は参照するテーブルの候補キーの値か、またはNULL値である必要があります。これを参照制約（Referential Constraint）といいます。
ドメイン制約	列のとり得る値の範囲（ドメイン）を定義することを、ドメイン制約（Domain Constraint）といいます。

2.4. データ操作言語

データの操作を行う SQL 言語を、データ操作言語（DML：Data Manipulation Language）といいます。

データ操作言語を使ってデータベースから必要なデータを、テーブル形式で問合せることができます。この場合、ディスクに格納されているテーブルを実テーブル （Base Table）といい、問合せの結果を導出テーブル （Derived Table）といいます。また使用頻度が高い問合せは、あらかじめその内容をデータ操作言語で定義しておき、あたかもテーブルと同じように使うことができます。この仮想的なテーブルをビュー （View）といいます。

リレーショナル代数演算

データ操作言語の基本となる考え方に、リレーショナル代数があります。リレーショナル代数には演算が定義されおり、代表的な演算には以下のものがあります

制約	説明
選択演算：Selection	選択は、リレーション（テーブル）から、ある属性（列）が特定の条件を満たすタプル（行）の集合を得ることです。例えば「商品テーブルから単価が 1,000 円以上のものを抽出する」という場合です。 SELECT * FROM product WHERE unitprice >= 1000
射影演算：Projection	射影は、リレーション（テーブル）から、特定の属性（列）を抜き出して部分集合を得ることです。例えば「商品テーブルから商品番号、商品名、単価を抽出する」という場合です。 SELECT productid, productname, unitprice FROM product
結合演算：Join	結合は、複数のリレーション（テーブル）において、同じ属性（結合列）の値をもつタプル（行）を足し合わせて集合を得ることです。例えば「商品テーブルと注文テーブルを商品番号で結合して、商品注文リストを作成する」という場合です。 SELECT product., order. FROM product, order WHERE product.productid = order.productid

3. データモデリング

データベースは現実の世界に存在するデータを意味のあるデータとして、コンピュータ上に写像したものです。例えば、ビジネスデータ処理であれば、顧客、商品、受発注、販売に係わる活動があります。これらをデータベースに写像する作業をデータモデリングといいます。

3.1. データモデル

データモデルには以下の 2 種類があります。

データモデル	説明
概念データモデル	データベース化の対象となる実世界の構造を記号系で用いて記述したものが概念データモデルです。この記号系の典型がＥＲ（Entity Relationship）モデルです。概念データモデルが、データベース論理設計で最初に作成されます。
論理データモデル	概念データモデルをコンピュータに実装可能な形式に変換する必要があります。この形式を論理データモデルといいます。論理データモデルの主な記号系として、リレーショナルモデル、階層型データモデル、ネットワーク型データモデルなどがあります。リレーショナルデータベースには論理データモデルとして、リレーショナルモデルが採用されています。

データモデル

説明

概念データ
モデル

データベース化の対象となる実世界の構造を記号系で用いて記述したものが概念データモデルです。この記号系の典型がＥＲ（Entity Relationship）モデルです。概念データモデルが、データベース論理設計で最初に作成されます。

論理データ
モデル

概念データモデルをコンピュータに実装可能な形式に変換する必要があります。この形式を論理データモデルといいます。論理データモデルの主な記号系として、リレーショナルモデル、階層型データモデル、ネットワーク型データモデルなどがあります。
リレーショナルデータベースには論理データモデルとして、リレーショナルモデルが採用されています。

図 2. データモデルとモデリング

3.2. 正規化

リレーショナルデータベースにおいて、データをテーブルに展開することを正規化といいます。正規化はデータベース設計の重要な指針です。

正規化したテーブルの形式には、主に以下のような種類があります。

正規形	内容
第１正規形	リレーショナルデータベースでは、テーブルの１つの列には 1 つの値しか格納できません。このように繰り返し項目を排除したテーブルを第 1 正規形といいます。例えば、ある社員が 2 つの資格を保有している場合、テーブルの資格欄には 1 つの値しか入らないため、 2 つのタプル（行）に展開して格納します。このように繰り返し項目をなくすことを第 1 *正規化*するといいます。
第２正規形	第 1 正規形では部分的に同じ属性値をもつタプル（行）が複数存在し、冗長になる場合があります。そこで第1正規形のテーブルから、その一部の属性にだけ従属する属性の組みを外に出すようにテーブルを分割します。このように冗長性を排除したテーブルを第 2 正規形といいます。第 2 正規形は、テーブルが第１正規形であり、かつ候補キーに含まれない属性（非キー属性）が候補キーに完全関数従属する場合をいいます。例えば社員テーブルにおいて、社員番号（候補キー）とそれに従属する氏名、所属、所属場所を外に出すことができます。この場合、社員番号がわかれば氏名や所属などを一意に特定することができます。このようにテーブルを分割して第2正規形にすることを第 2 *正規化*するといいます。
第３正規形	第2正規形でも属性の組み合わせによっては冗長になる場合があります。そこで第2正規形のテーブルから、非キー属性間の従属関係を外に出すようにテーブルを分割します。このようにさらに冗長性を排除したテーブルを第3正規化といいます。第 3 正規形は、テーブルが第2正規形であり、かつ非キー属性がいかなる候補キーにも推移的関数従属しない場合をいいます。例えば第 2 正規形にした社員テーブルにおいて、非キー属性である所属と所属場所は、所属が決まれば所属場所が決まるという従属関係にある場合、所属と所属場所を外に出すことができます。このように第2正規形を第 3 正規形にすることを第 3 *正規形*するといいます。

3.3. ＥＲＤ

中規模以上のデータベースでは、設計が大変重要です。データベースの概念や論理設計の手法として、ER（Entity Relationship）モデルがあります。

ER モデルでは、データはすべてエンティティ（実体）とリレーションシップ（関連）で表現します。ERモデルの表現法としては、ERD（Entity Relationship Diagram）が用いられます。

ERD の構成要素

ＥＲＤの構成要素には、以下の 3 種類の要素があります。

	内容
エンティティ（実体）	分析対象範囲で、組織体の中で管理対象となるものです。企業であれば、例えば社員、顧客、商品、支店などがエンティティの候補となります。ERD では通常、長方形の中にエンティティ名を明記します。
リレーションシップ（関連）	分析対象範囲で、組織体の中で管理対象となるものです。企業であれば、例えば社員、顧客、商品、支店などがエンティティの候補となります。ERD では通常、長方形の中にエンティティ名を明記します。
アトリビュート（属性）	エンティティの性質や特性を表すデータ要素です。例えば社員というエンティティは、氏名、入社年度、生年月日、住所等の属性で構成されます。エンティティには、必ずエンティティの実現値を一意に識別できる属性（主キー）が必要です。

リレーションシップの種類

エンティティ間のリレーションシップには、以下の 3 種類があります。

種類	内容
1 対 1	例えば 1 人の従業員につき 1 台のパソコンが割り当てられているような対応関係です。
1対多	例えば部門と社員、担任と生徒の対応関係などです。リレーショナルデータベースでは、主キーと外部キーの参照制約に相当します。
多対多	例えば専攻科目と学生、商品と顧客の購入するという対応関係などです。このリレーションシップは、リレーショナルデータベースのテーブルに正規形を満足した形で実装できないため、論理データモデルの段階で、1 対多のリレーションシップに分解する必要があります。

サブタイプ

上記のリレーションシップ以外に、サブタイプとスーパタイプのリレーションシップが存在します。このリレーションシップを IS-A リレーションシップといいます。

例えば顧客の中に個人顧客と法人顧客があるとします。顧客エンティティに対して、個人顧客と法人顧客をサブタイプエンティティといいます。逆に個人顧客や法人顧客から見て、顧客エンティティをスーバタイプエンティティといいます。この場合、スーパタイプには共通属性が割り当てられ、サブタイプには固有属性が割り当てられます。

サブタイプとスーパタイプを用いることにより、ERD を階層化することができます。サブタイプをスーパタイプに統合することを汎化といいます。逆にスーパタイプをサブタイプにすることを特化といいます。

4. SQL Server のアーキテクチャ

SQL Server は SQL を使用する、クライアント／サーバー型のリレーショナルデータベース管理システム（RDMS）です。SQL Server を使用するとトランザクション処理の実行、データの格納と分析、アプリケーションの構築が出来ます。

4.1. アーキテクチャ

SQL Server では、新規インストールによって既定でインストールされる４つのサービスがあります。

サービス	説明
MS SQL Server サービス	データベースエンジン
SQL Server Agent	ジョブスケジュール管理
Microsoft 分散トランザクションコーディネータ	分散トランザクション管理
Microsoft Search	フルテキストエンジン

SQL Server は複数のインスタンスを同時実行することが出来ます。既定のインスタンスは１つしか作成出来ませんが、名前付きインスタンスは最大１６作成出来ます。

4.2. データベースオブジェクト

データベースは、データの入っているテーブル、及びその他のオブジェクトの集まりです。

データベースオブジェクトの代表的なものとして、以下のものがあります。

データベースオブジェクト	説明
テーブル	列と関連付けられた行の集合です。
ビュー	テーブルの物理的な定義を隠し、アプリケーションに使い易い仮想的なテーブルを提供します。
ストアドプロシージャ	SQLステートメントの集合に名前が付けられたものです。
トリガ	ユーザーがテーブル内のデータを更新すると、自動的に実行される特殊な形式のストアドプロシージャです。

4.3. データベースの種類

SQL Server には、2 種類のデータベースがあります。

種類	説明
システムデータベース	SQL Server 全体の情報が格納されています。SQL Server はシステムの操作と管理にシステムデータベースを使用します。
ユーザーデータベース	ユーザーデータベースは、ユーザーが作成するデーベースです。

データベース内には、システムテーブル、ユーザーテーブル、インデックスの3種類のオブジェクトが存在します。そのうちシステムテーブルは、システムやデータベース内にあるオブジェクトに関するメタデータと呼ばれる情報が格納されます。メタデータとは、データについての情報です。

4.4. 導入

SQL Server 2000 の導入は、セットアッププログラムを実行して進めていきます。セットアッププログラムでは、インストールの種類として最小、標準、カスタムのどれかを選択できます。

セットアッププログラムは、プログラムファイルとデータファイルをディスク上のフォルダにインストールします。プログラムファイルとデータファイルの規定のインストールパスは以下の通りです。

プログラム	\Program Files\Microsoft SQL Server\Mssql\Binn
データ	\ Program Files\Microsoft SQL Server\Mssql\Data

操作と管理ツール

SQL Server の操作や管理は、SQL Server Enterprise Manager で行います。このツールは、ローカルまたはリモートの SQL Server を管理できる GUI のツールです。Enterprise Manager を使うと SQL を記述しなくても、データベースに対してほとんどすべての操作を行うことができます。

図 3. SQL Server Enterprise Manager

5. 物理データベース設計

論理設計をもとに、データベース管理システム上へ実際にどのようにデータベースを構築するかを設計するのが物理データベース設計です。物理データベース設計をする際、データベース管理システムでのデータ確報方法を知っておく必要があります。

SQL Server の場合、物理データベースの主要なデータ構造として、ページとエクステントがあります。ページは 8 KB の連続したディスク領域で、データの入出力の単位です。データは行単位にページへ格納されます。エクステントはページの集まりで、テーブルとインデックスに領域を割り当てる基本単位です。1エクステントは連続した 8 ページで構成され、サイズは 64 KB になります。

5.1. 逆正規化

物理データベース設計では、データアクセスのパフォーマンスについても考慮する必要があります。

論理データモデリングのところで正規化について説明しましたが、パフォーマンスの向上を目的に、故意に正規化を行わない場合があります。これを逆正規化といいます。正規化して複数に分割されたテーブルにクエリを実行した場合と、逆正規化による単一のテーブルにクエリを実行した場合とでは、逆正規化の方がテーブルの結合が不要な分だけパフォーマンスが向上します。

逆正規化の考慮点

逆正規化を行う際、以下のことを考慮する必要があります。

・ データの整合性に問題が発生する可能性がある
・ 特定のクエリを優先することで、他のクエリのパフォーマンスを犠牲にする場合がある。

5.2. インデックス設計

インデックスとは、テーブルのデータを効率よく検索するために作成されるデータです。物理データベース設計では、インデックスを作成するかどうかや、作成する場合にはその方法についても検討します。

インデックスとは

インデックスは、例えば書籍でいうところの索引に相当します。 SQL Server のインデックスは、B-Tree といわれる方式を採用しています。B-tree はインデックスがツリー構造（階層構造）になっており、高速に目的のデータを検索することができます。インデックスにより、データの取得が高速化されます。その反面、ディスク領域を消費し、オーバーヘッドや保守コストがかかります。インデックスを設計する際は、これらのことを考慮する必要があります。

インデックスの実装上の種類
SQL Serverには、以下の 2 種類のインデックスがあります。

インデックス	説明
クラスタ化インデックス	1 つのテーブルに対して 1 つだけ持つことができます。データページはインデックス列の値でソートされています。
非クラスタ化インデックス	1 つのテーブルに対して最大 249 個まで持つことができます。データページはソートされません。

5.3. 問合せの最適化

SQL Server ではデータにアクセスする際、クエリオプティマイザにより、最も効率的なアクセス方法が選択されます。SQL Server で最初にインデックスの存在を判断し、次にクエリオプティマイザが、テーブルのスキャンとインデックスの使用のどちらが効率的かを判断します。オプティマイザはコストベースで動作します（コストベースオプティマイザ）。オプティマイザは各クエリのタスクに時間コストを割り当て、最もコストの低いタスクのリストを選択して目的の結果セットを生成します。

5.4. 結合の最適化

2 つ以上のテーブルの列と行を合わせた結果テーブルから、1 つの結果セットを返すことができます。これを表の結合といいます。クエリに JOIN 句が含まれる場合、クエリオプティマイザはテーブル、インデックス、および結合の数を評価して、使用する最適な順序と結合方法を決定します。SQL Server の結合方法は以下の 3 種類です。

結合方法	説明
ネクスト化ループ結合	ネクスト化ループは、一方のテーブルを外部入力テーブルとして使用し、もう一方を内部入力テーブルとして使用します。外部入力テーブルと内部入力テーブルはオプティマイザがコストを考慮して選択します。
マージ結合	マージ結合は２つの並び替えられた入力を使用し、その２つをマージします。
ハッシュ結合	ハッシュとは、指定された属性に基づいて、データを分割する方法です。グループ化されたデータは、特定のデータ項目が既存の値と一致するかどうかを判断するのに使用します。

6. SQL（Transact-SQL）

SQL は、リレーショナルデータベースシステムに対してデータの検索や更新・定義などを行うための言語です。SQL は ANSI や JIS によって標準化されており、ほとんどの RDMS では SQL-92 と呼ばれる規格に準拠した SQL を使用します。

SQL Server では、Transact-SQL と呼ばれる SQL を使用します。Transact-SQL は、ANSI SQL-92 に準拠し、なおかつ拡張機能を追加した SQL です。

Transact-SQL は操作の種類によって次の３種類に分類できます。

・ データ操作言語(DML：Data Manipulate Language)
　　　（データベースへの問合せやデータの追加・更新・削除）
・ データ定義言語(DDL：Data Definition Language)
　　　（表などのオブジェクトの作成・定義変更・削除）
・ データ制御言語(DCL：Data Control Language)
　　　（データの変更要求やトランザクションの管理）

6.1. 単純問合せ

データベースのテーブルから必要な情報を取得するには、SELECT ステートメントを使用します。SELECT ステートメントが実行されると、該当データを検索して結果セットに格納し、ユーザー側へ戻します。

SELECT ステートメントの構文

SELECT 選択リスト　...結果セットに返される列や計算式
FROM  テーブル名　...結果セットに返される値が格納されているテーブル名
WHERE  検索条件　...結果セットに返される行に対する検索条件

全ての列の取得
```
SELECT ＊ FROM Employees 
```
SELECT 句のあとに、列名を指定するかわりに＊ (アスタリスク) を指定すると、テーブル内の全ての列を指定したのと同じ意味になります。
特定の列の取得
```
SELECT  EmployeID, LastName  FROM  Employees 
```
SELECT句のあとの選択リストに列名をカンマで区切って指定すると、テーブル内の指定された列が結果セットに返されます。このとき、結果セット内の列の順序は選択リストでの指定順となり、元のテーブル内での列の順序とは異なります。
算術演算結果の取得
```
SELECT  EmployeID, LastName, Salary ＊ 12  FROM  Employees
```
選択リストには列名だけでなく算術演算子を使用した式を指定することができます。このとき、結果セットには計算結果が返されます。

SQL Server で使用可能な算術演算子は次の５種類です。

演算子	処理
＋	加算
－	減算
＊	乗算
／	除算
％	剰余

特定の行の取得

SELECT  LastName, FirstName, Salary ＊ 12  
    FROM  Employees
     WHERE  EmployeeID = 5

WHERE 句で条件を指定すると、条件に合った行だけが結果セットに返されます。 WHERE句の条件式は「列名比較演算子式」で構成されています。使用可能な比較演算子は次の６種類です。

演算子	意味	例
＝	等しい	EmployeeID = 5
>	より大きい	Salary < 200000
<	より小さい	Salary < 200000
>＝	以上	Salary >= 200000
<＝	以下	Salary <= 200000
<>	等しくない	Salary <> 200000
LIKE 注1)	文字列パタンが一致する	FirstName LIKE ‘YAMA%’
BETWEEN	範囲内にある（～以上～以下）	Salary BETWEEN 150000 AND 200000
IN	リスト内にある	EmployeeID IN (5,10,15)
IS NULL注2)	NULL 値である	LastName IS NULL

注1) LIKE演算子では、比較対象の文字列パターンを指定するために以下のワイルドカードを利用します

％任意の文字列
任意の１文字
[] 指定した集合内の任意の１文字
[^] 指定した集合内ない任意の１文字

注2)NULL 値は空白値や長さ０の文字列とは区別されます。また、NULL 値は他の値と比較することができません。NULL 値と検索する場合は IS NULL 演算子のみが有効です。

複数の条件を指定

SELECT  LastName, FirstName, Salary ＊ 12  
    FROM  Employees
     WHERE  LastName LIKE ‘YAMA%’  AND  Salary  >  200000

WHERE 句では、論理演算子の AND や OR を使用して、２つ以上の条件を組み合わせることができます。また、論理演算子のNOTを使用して条件の意味を逆にすることもできます。論理演算子には優先順位があり、NOT、AND、OR の順に条件が評価されます。

データの並べ替え

SELECT  LastName, FirstName, Salary ＊ 12  
    FROM  Employees
     WHERE  Salary  >  200000  
       ORDER BY  Salary  DESC

SELECT ステートメントによって戻される結果セットの行の並び順は、ORDER BY 句で指定することができます。ORDER BY 句で指定した列の値の昇順または降順（DESC キーワード指定時）に行が並べ替えられます。

重複行の排除

SELECT  DISTINCT  Salary
    FROM  Employees
     WHERE  Salary  >  200000  
       ORDER BY Salary DESC

複数の行で列に同一の値が含まれている場合は、結果セットの行が他の行の内容と同一になることもあります。DISTINCT キーワードを使用すると、検索結果の行から重複行を排除して結果セットに返します。

検索結果の列名を変更

SELECT FirstName AS First , LastName  AS  Last  , Salary ＊ 12  AS Sal
    FROM  Employees
     WHERE  Salary  >  200000

結果セットの列には、元の表の列名とちがう名前（別名）をつけることができます。結果セットに計算結果が含まれる場合に、これらの式に別名をつけて見やすくします。

6.2. データのグループ化と集計

データを取得するときにデータをグループ化したり集計を作成したりすることができます。

 集計関数

平均値や合計を計算する関数を集計関数といいます。集計関数を使用すると、テーブル全体、またはグループごとに指定した列を計算し、集計した結果を返します。主な集計関数は以下のとおりです。

集計関数	説明
AVG	数値式の値の平均
COUNT	式の値の数
MAX	式の最大値
MIN	式も最小値
SUM	数値式の値の合計

テーブル集計

SELECT  SUM(Quantity) 
  FROM  [Order Details]

この例では、Order Details テーブルに含まれる Quantity 列のすべての行を合計します

グループごとの集計

SELECT  ProductID, SUM(Quantity) 
  FROM  [Order Details]
    GROUP BY ProductID
　　ORDER BY ProductID

ある列に対する集計値を求める場合は、GROUP BY 句と共に集計関数を使用します。 GROUP BY 句を使用しても並び替え順序は保証されません。結果を並び替える場合は ORDER BY 句を使用します。

6.3. 副問合せ（サブクエリ）

サブクエリとは、SQL ステートメントに埋め込まれたSELECTステートメントです。サブクエリを使用して、複雑なクエリを一連の論理的な手順に分解して、結果として、クエリを１つのステートメントで解決します。サブクエリは、クエリが別のクエリの結果に依存する場合に便利です。

派生テーブルとしてのサブクエリ
```
SELECT O.OrderID, O.CustomerID
 FROM ( SELECT OrderID, CustomerID FROM Orders ) AS O
```
派生テーブルを作成するには、FROM 句でテーブルの代わりにサブクエリを使用します。別名を使って参照します。
式の代わりのサブクエリ
```
SELECT ProductName AS '製品名' ,UnitPrice AS '価格'
     ,( SELECT AVG(UnitPrice) FROM Products) AS '平均' 
     ,UnitPrice-(SELECT AVG(UnitPrice) FROM Products) AS '差'
 FROM Products
```
サブクエリは、SELECT、UPDATE、INSERT、DELETE の各ステートメントの中で、式の代わりに使用することができます。サブクエリの結果は単一の値である必要があります。この例は、製品の価格と平均価格の差を求めています。
相関副問合せ（相関サブクエリ）

相関サブクエリは、外部クエリが選択する各行を変更する動的な式として使用することができます。

外部クエリが選択する各行に対して一度ずつサブクエリを実行します。このサブクエリは、その行に対する式として評価され外部クエリに渡されます。
```
SELECT OrderID, CustomerID
 FROM Orders AS O
  WHERE 50 < (SELECT Quantity
             FROM [Order Details] AS D
             WHERE O.OrderID = D.OrderID
              AND  D.ProductID = 21))
```
この例は、製品番号21を50個より多く注文した顧客の一覧を返します。

相関サブクエリは実質的には、動的に実行されるサブクエリと外部クエリが選択する行との結合（JOIN）になります。

通常、相関サブクエリは結合として記述し直すことができます。

6.4. 結合問合せ

結合は、複数のテーブルをクエリして各テーブルの行と列を含む結果セットを生成します。両方のテーブルの任意の列や、任意の列に基ずく式によって、テーブルを結合します。結合には、内部結合、外部結合、クロス結合の 3 種類があります。３つ以上のテーブルを結合したり、自己結合を使用して、テーブルをそのテーブル自体と結合することができます。

結合の構文

SELECT 選択リスト　　...結果セットに返される列や計算式
FROM  テーブル名    ...結果セットに返される値が格納されているテーブル名
　　　　JOIN　　　　　　...結合するテーブルと結合方法を指定
　　　　ON　　　　　　　...結合するためのテーブル間の共通の列を指定
  WHERE  検索条件     ...結果セットに返される行に対する検索条件

JOIN 句は結合するテーブルと結合方法（内部結合、外部結合、クロス結合）を指定します。 ON句は結合するためのテーブル間の共通の列を指定します。

内部結合
```
SELECT o.OrderID,   c.CompanyName, o.OrderDate 
   FROM  Orders  AS o 
  INNER  JOIN Customers AS c   
  ON o.CustomerID = c.CustomerID
```
内部結合は、両方のテーブルの共通の列の値を比較してテーブルを結合します。条件に合う行のみ返します。
外部結合
```
SELECT  c.CustomerID,c.CompanyName, 
        o.OrderID,   o.OrderDate
 FROM  Customers  AS c LEFT OUTER JOIN Orders  AS o
  ON o.CustomerID = c.CustomerID
```
内部結合では、両方のテーブルで結合条件に合致する行がある場合のみ結果として返されます。これに対し、外部結合では、結合条件に一致しない行も結果に含めることができます。左外部結合の場合、参照された左テーブルのすべての行が返されます。

この例では、Customers テーブルが左テーブルです。注文の無い顧客情報も返されます。
テーブルの自己結合
```
SELECT a.CustomerID, b.CustomerID, a.Phone 
  　　　　FROM    Customers AS a
  　　　　JOIN     Customers AS b
    　　　ON       a.Phone = b.Phone
  　　　　WHERE   a.CustomerID  <  b.CustomerID
```
１つのテーブル内で結合することを自己結合といいます。このとき、テーブルに別名をつけることにより、同じテーブル同士でも、見かけ上、別のテーブルとして処理されます。この例では、同一の電話番号を持つ顧客情報を返します。同一行同士で結合される場合や、2 度結合される場合もあるので、WHERE句で除外しています。

6.5. データの更新

テーブルのデータを追加、変更、削除することができます。以下の SQL ステートメントを使用します。

処理	SQL ステートメント
追加	INSRT
変更	UPDATE
削除	DELETE

新しい行の挿入
```
INSERT  Shippers 
                    (ShipperID, CompanyName, Phone)
           VALUES (4,'Japan Express','(503)666-1234') 
```
INSERT ステートメントにより、テーブルに新しい行を追加することができます。各列に値を指定して、新しい行をテーブルに追加します。
既存のテーブルから行を追加
```
INSERT Shippers 
         SELECT　ShipperID, CompanyName, Phone
　　　　 FROM Japan_Shippers
```
既存の表から行を追加することもできます INSERT・・・SELECT ステートメントは SELECT ステートメントの結果セットを指定したテーブルに追加します。
行の更新
```
UPDATE Products
 SET UnitPrice = (UnitPrice * 1.1)
　　　　WHERE CategoryID = 8 
```
UPDATE ステートメントを使用して、既存の行の値を更新することができます。複数行の値を一度に更新することもできます。WHERE 句を使用することで更新する行を特定できます。SET 句により更新内容を指定できます。この例は、区分が８の商品の価格 10 % アップしています。
行の削除
```
DELETE Orders
　　　　 WHERE DATEDIFF(Year, OrderDate, GETDATE()) >= 2 
```
DELETE ステートメントを使って、テーブルから行を削除することができます。WHERE 句を使用することで削除する行を特定できます。削除された行はトランザクションログに記憶されます。この例は、注文日が、2 年以前の行を削除しています。
```
テーブルの切り捨て 
TRUNCATE TABLE　
　　　　　　　ORDERS 
```
TRUNCATE TABLE ステートメントを使用して、テーブル内のすべての行を削除することができます。削除された行はトランザクションログに記憶されません。
トランザクションの使用

ランザクションとは、論理的な１つの作業単位として実行される一連の操作です。データの論理的一貫性を適用する時点でトランザクションの開始と終了を行う必要があります。
```
BEGIN TRANSACTION
　　　　UPDATE　Savings　SET　balance=　balance　? 100
　　　　　　　　　WHERE　CustomerID　=　7890
　　　　UPDATE　checking　SET　balance=　balance　＋ 100
　　　　　　　　　WHERE　CustomerID　=　7890
　　COMMIT TRANSACTION
```
トランザクションの開始の宣言は BEGIN TRANSACTION で行い、終了の宣言は COMMIT TRANSACTION を使用します。

6.6. データベース定義

データベースを定義するには、CREATE DATABASE ステートメントを使用します。トランザクションログも作成されます。

CREATE DATABASE Sales  
 ON 　PRIMARY　(　NAME = 'Sales_Data', 
　　　　　　　　　　　FILENAME = 'D:\Sales_Data.MDF' , 
　　　　　　　　　　　SIZE = 10, MAXSIZE = 100, FILEGROWTH = 4　) 
　LOG ON　　　　 (　NAME = 'Sales_Log', 
　　　　　　　　　　　FILENAME = 'D:\Sales_Log.LDF' ,
　　　　　　　　　　　SIZE = 5, MAXSIZE = 50, FILEGROWTH = 2　)

ＯＮ句でデータファイルを定義し、LOG ON 句でログファイルを定義します。

パラメータ	説明
PRIMARY	プライマリファイルグループに含むファイルを指定します
FILENAME	物理ファイル名とファイルヘのパスを指定します。
SIZE	ファイルのサイズを指定します。
MAXSIZE	拡張可能なファイルの最大サイズを指定します。
FILEGROWTH	ファイル拡張の増分値を指定します。

データファイルが拡張されたり、データの変更が頻繁に行われると、ファイルのサイズを拡張したり、ファイルを追加したりする必要があります。データベースの拡張の管理には ALTER DATABASE ステートメントを使用します。

ALTER DATABASE Sales
　　　　ADD FILE 
  　　　(　NAME = Sales_Data2,
   　　　　FILENAME=‘D:\ Sales_Data2.ndf’,
   　　　　SIZE=20MB, MAXSIZE=50MB　)
GO 
ALTER DATABASE Sales
   　　　MODIFY FILE ( NAME = ‘Sales_Log’,
   　　　SIZE = 20MB) 
GO

この例では、データファイルを追加し、トランザクションログサイズを拡大しています。

6.7. テーブルの作成と削除

テーブルを作成するには、CREATE TABLE ステートメントを使用します。

CREATE  TABLE  Shippers (
                         ShipperID 　　int 　　　 NOT NULL ,
    　　　　　　　　　　 CompanyName char (40)  NOT NULL ,
                         Phone　　　　char　(24)  NULL 
　)

この時、テーブル名、列名、データの型を指定する必要があります。又、NULL 値を許可するかを指定します。テーブル名はデータベース内で一意である必要があります。列名は、テーブル内で一意である必要がありますが、同一データベース内の異なる複数のテーブルでは同じ列名を使用できます。

テーブルの削除

テーブルの削除には DROP TABLE を使用します。テーブルを削除すると、テーブル定義とデータが削除されます。
```
DROP TABLE  Shippers 
```

6.8. ビューの使用

ビューは、SELECT ステートメントによって定義された論理的なテーブルです。実際のデータが格納されているテーブルに対して、仮想的なテーブルです。ビューの中には実際のデータが保存されていません。ビュー内の行と列はビューを参照したときに動的に作成されます。

ビューの定義
```
CREATE  VIEW  TotalView
AS
　　　SELECT ProductID, SUM(UnitPrice*Quantity) AS Total
 　　　FROM   [Order Details]
 　　　GROUP BY ProductID
```
ビューの定義は CREATE VIEW ステートメントで行います。ビューは SELECT ステートメントに名前をつけたものといえます。

ビューの使用する利点には、以下の項目があります。

ユーザーに必要なデータだけを表示
データベースの複雑さを隠す
ユーザー権限の管理の簡素化
別のアプリケーションへエクスポートするデータの編成

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

ビューを使用してデータを変更することができます。ビューに対する変更は、実際は基になっているテーブルを変更することになります。基になっているテーブルが１つで、SELECT ステートメントに集計関数や GROUP BY 句を使用していないビューからは、データの挿入、更新、削除が自由に行えます。ビューを使用してデータの変更を行う場合は以下の点に注意する必要があります。

基になる複数のテーブルを同時に変更できない
特定の列（計算値、組込み関数、集計関数）を変更できない
ビューによって参照されない列に影響を与える場合
NULL値が許可されていない列、規定値を持たない列

6.9. 整合性制約の定義

データ整合性は、データベースに格納されているデータの一貫性と正確性を定義します。制約により、列に有効なデータ値が入力され、テーブル間の関係が保たれます。

データ整合性には以下の種類があります。

整合性の種類	説明	制約の種類
ドメイン	ある列で有効な一連のデータ値を指定し、その値が列の値として妥当性を保証します	DEFAULT CHECK
実体	値の重複を防ぎ、一意の値を持つことを保証します	PRIMARY KEY UNIQUE
参照	主キーと外部キーのリレーションシップが常に維持されます。	FOREGIN KEY

整合性の種類

説明

制約の種類

ドメイン

ある列で有効な一連のデータ値を指定し、その値が列の値として妥当性を保証します

DEFAULT

CHECK

実体

値の重複を防ぎ、一意の値を持つことを保証します

PRIMARY KEY

UNIQUE

参照

主キーと外部キーのリレーションシップが常に維持されます。

FOREGIN KEY

DEFAULT 制約

INSERT ステートメントの実行時に値が指定されていない場合に、DAFAULT制約で指定された値が列に入力されます。列ごとに 1 つだけ定義できます。
```
ALTER TABLE Customers
ADD　CONSTRAINT  DF_ContactName  
      　　　　　　　　DEFAULT  ‘UNDEFINE’　 FOR ContactName
```
この例では、ContactName 列の既定値を’UNDEFINE’とします。
CHECK 制約

INSERT や UPDATE ステートメントの実行時にデータの整合性をチェックします。
```
ALTER TABLE Employees
ADD 　CONSTRAINT CK_BirthDate
　　　　　　　　　　　CHECK ( BirthDate < GETDATE())
```
この例では、誕生日は、現在の日付より前でなければなりません。
PRIMARY KEY 制約

行の一意に識別する主キーをテーブルに対して指定します。テーブルごとに１つだけ定義できます。NULL 値は許可されません。
```
ALTER TABLE Suppliers 
ADD 　CONSTRAINT PK_ Suppliers 
  　　　PRIMARY KEY CLUSTERED (SupplierID) 
```
この例では、Suppliers テーブルの主キーとして SupplierID を定義します。
UNIQUE 制約

UNIQUE 制約は、ひとつの列に重複した値を格納できないことを指定します。既に主キーが存在し、さらに別の列に対しても値を一意に設定したい場合に使用します。NULL 値は、１つだけ許可されます。
```
ALTER TABLE Suppliers 
　ADD 　CONSTRAINT U_CompanyName 
  　　 　UNIQUE NONCLUSTERED (CompanyName)
```
この例では、Suppliers テーブルの CompanyName に対し UNIQUE 制約を定義します。
FOREIGN KEY 制約 FOREIGN KEY 制約は参照整合性を適用します。FOREIGN KEY 制約は PRIMARY KEY 制約または UNIQUE 制約が設定された列への参照を定義します。
```
ALTER TABLE 　Products
 ADD 　CONSTRAINT 　FK_Products_Categories　
 　　　　FOREIGN  KEY  (CategoryID) 
　　　　　REFERENCES 　Categories (CategoryID)
```
この例では、FOREIGN KEY 制約を使用して Products テーブルの CategoryID 列を Categories テーブルの CategoriyID 列と関連付けます。

7. トランザクション管理

この章では、トランザクションの役割と機能について説明します。

7.1. トランザクションの特性

トランザクションは、論理的な１つの作業単位として実行される一連の操作です。トランザクションが有効であるためには、ACID と呼ばれる４つの属性を備えている必要があります。

属性	説明
Atomiccity（原子性）	トランザクションは、分離できない１つの単位であり、そのデータはすべて実行されるか、どれも実行されないかのどちらかです。
Consistency（一貫性）	トランザクションが完了時にすべてのデータが一貫した状態になければなりません。
Isolation（分離性）	同時実行のトランザクションによってなされる変更は、ほかのすべての同時実行のトランザクションの変更とお互い独立している必要があります。
Durability（持続性）	トランザクションの完了後、その結果はシステム内で持続します。システム障害が発生しても、変更結果は有効です。

トランザクションの定義

アプリケーションはトランザクションの開始タイミングと終了タイミングを指定してトランザクションを制御します。BEGIN TRANSACTION と COMMIT TRANSACTION を使用します。
```
BEGIN TRANSACTION
　　　　INSERT
　　　　UPDATE
　　COMMIT TRANSACTION 
```

7.2. 同時実行制御

同時実行制御は１人のユーザが行った変更がほかのユーザによる変更に悪影響を及ぼさないことを保証するものです。同時実行制御には、以下の 2 種類の制御があります。

種類	説明
ペシミスティック	更新の準備としてデータが読み取られたときにデータがロックされます。ロックを適用したユーザがデータ操作を完了するまで、ほかのユーザはデータを変更する操作を実行できません。
オプティミスティック	データが最初に読み取られた時点では、データはロックされません。更新が実行されたときに、最初に読み取られたデータが変更されたかどうかがチェックされます。データが変更されている場合は、ユーザにエラーが返されトランザクションはロールバックされます。

同時実行制御を行うことにより、以下の問題点が解決されます。

問題点	説明
更新の損失	トランザクションによる変更がほかのトランザクションによって上書きされると、更新が失われることがあります。
ダーティリード	トランザクションがほかのトランザクションから、コミットされていないデータを読み取ったときに発生します。不正確なデータを基にして変更を行う可能性があります。
反復不能読み取り	トランザクションによって同じ行が複数回読み取られ、そのたびにほかのトランザクションによってその行が変更されたときに発生します。
ファントム読み取り	トランザクションがお互いに分離されていない場合に発生します。例えば、ある範囲の行をUPDATEしている際にほかのトランザクションがその範囲に新しい行をINSERTすることができます。次にトランザクションからデータを読み取ると追加の行が存在してしまいます。

SQL Server では、トランザクション分離レベルを指定することで、セッションレベルでロックオプションを制御できます。

オプション	説明
READ UNCOMMITTED	共有ロックを発行しないようにします。ダーティリードが発生する可能性があります。
READ COMMITTED	共有ロックを発行するようにします。ダーティリードは発生しません。
REPEATABLE READ	ダーティリードと反復不能読み取りが発生しません。読み取りロックは、トランザクションの終了まで保持されます。
SERIALIZABLE	トランザクションの WHERE 句に含まれる条件に一致する行を更新したり、新規に挿入することをほかのユーザに対し禁止します。ファントム読み取りは発生しません。

ロック制御

SQL Server には、主に 2 種類のロックがあります。

種類	説明
共有ロック	データの変更も更新も行わない操作は、共有（読み取り）ロックを使用します。
排他ロック	データを変更する（INSERT,UPDATE,DELETE）ステートメントに対しては排他ロックを使用します。排他ロックを取得できるトランザクションは１つだけです。

SQL Serverによってロックされるリソースには主に以下のリソースがあります。

リソース	説明
RID	行識別子。1 行をロックするために使用します。
キー	インデックス内の行ロック、キーの範囲を保護するために使用します。
テーブル	すべてのデータとインデックスを含むテーブル全体
データーベース	データベースの復元時に使用するデータベース全体

最大のパフォーマンスを得るために、ロックのコストを最小にするため、自動的に適切なレベルでリソースをロックします。

SQL Server は、動的ロックアーキテクチャーを使用してクライアントに最適なロックを選択しますが、テーブルレベルのロックオプションを指定することもできます。テーブルロックの例を示します。

USE pubs
BEGIN TRAN
SELECT COUNT(*) FROM authors WITH (TABLOCK, HOLDLOCK)

デッドロック

デッドロックは、２つのトランザクションが別々のオブジェクトについてのロックを持ち、両方のトランザクションが相手のロックしているオブジェクトへのロックを要求したときに発生します。どちらのトランザクションも、相手がロックを開放するのを待ちます。SQL Server は、トランザクションのいずれかを自動的に終了することでデッドロックを終了します。

7.3. 障害回復処理

データ変更が発生すると、トランザクションログにその変更が記憶されます。チェックポイントのタイミングで、コミット済みのトランザクションがディスク上のデータベースファイルに書き込まれます。

トランザクションログにはすべての変更が記録されるため、電源障害、システムソフトウエア障害、クライアント障害、トランザクション取り消し要求（ROLLBACK TRANSACTION）が発生した場合に、SQL Server は自動的にデータを回復できます。 SQL Server の復旧プロセスは、トランザクションログを調べて、データベースの一貫性を保証します。

図 4. リカバリープロセス

前回のチェックポイントから、障害が発生した時点もしくはシャットダウンした時点までのトランザクションログを調べます。
コミット済みトランザクションはロールフォワードされ、データベースに書き込まれます。
コミットされていないトランザクションはロールバックされ、データベースには書き込まれません。

SQL Server の障害には、システム障害とデータベース障害があります。

種類	復旧処理
システム障害（自動復旧）	システム障害時にシステムを再起動すると、SQL Server はデータの一貫性を保証するために、自動復旧プロセスを開始します。このプロセスは自動的に実行されるため、手動で開始する必要はありません。
データーベース障害（手動復旧）	データベース障害後にバックアップから復元操作を行う際に、復旧プロセスを手動で開始できます。

データベース復旧モデル

SQL Serverには、3 種類のデータベース復旧モデルがあります。どのモデルも障害が発生した場合のデータ損失を保護します。

復旧モデル	説明
完全復旧モデル	すべての変更はトランザクションログに記憶されます。現在のトランザクションログが障害を起こしていない限りデータベースを完全に復旧できます。
一括ログ復旧モデル	一部の操作に対しては、ログ領域の使用をより少なくします。現在のトランザクションログが障害を起こしていない限りデータベースを完全に復旧できます。
単純復旧モデル	チェックポイント時にログが切り捨てられます。バックアップした時点にしか復旧できません。

データベースのバックアップ

SQL Server は、いくつかの異なるバックアップ方法を提供します。

バックアップの種類	説明
フルデータベースバックアップ	オリジナルデータベースファイルをバックアップします。
差分バックアップ	前回のフルデータベースバックアップ以降のデータベース変更をバックアップします。
トランザクションログバックアップ	トランザクションログをバックアップします。トランザクションログを切り捨てます。

データベースの復元

例えば、Northwind データベースは以下のバックアップを定期的に行っていたとします。

週 1 回フルデータベースバックアップを行っています。
毎日差分バックアップを行っています。
8 時間ごとにトランザクションログバックアップを行っています。

データﾍベース障害が発生した場合、データベースの復元を行う必要が発生します。

図 5. バックアップと復元

データベース復元は、以下の手順で復元を行います

フルデータベースバックアップからの復元

RESTORE　DATABASE　Northwind FROM 　F1　WITH　NORECOVERY

差分バックアップからの復元

RESTORE　DATABASE　Northwind FROM 　S1　WITH　NORECOVERY

最初のトランザクションログからの復元

RESTORE　DATABASE　Northwind FROM 　L1　WITH　NORECOVERY

２番目のトランザクションログからの復元

RESTORE　DATABASE　Northwind FROM 　L2　WITH　RECOVERY

8. セキュリティ管理

この章では、データベースのセキュリティ管理について説明します。

8.1. SQL Server のセキュリティ

SQL Server のセキュリティは２つのレベルがあります。ログイン認証と、データベースユーザアカウント権限の妥当性検査です。

ログイン認証では、ログインアカウントを識別し、SQL Server に接続できるかどうかだけを検査します。認証が成功すると、SQL Server に接続できます。ユーザが認証されSQL Serverへの接続が許可された後、データベースにアクセスします。データベースにアクセスするには、データベース毎にデータベースユーザが必要です。データベースユーザは SQL Server に登録されているログインアカウントに関連付けて登録します。

図 6. ログインアカウントとデータベースユーザ

ログイン認証

SQL Server に接続するには、ユーザはログインアカウントを持っていなければなりません。Windows 認証、SQL Server 認証の２つの認証メカニズムを使用します。Windows 認証では、既存の Windows ユーザまたはグループを SQL Server ログインアカウントに関連付けて登録します。SQL Server 認証では、ログインアカウントとパスワードを作成します。
権限の管理

データベースユーザがデータベースを操作するには権限が必要です。データベースユーザが何らかの操作を実行すると、そのデータベースユーザに適用されている権限を確認します。例えば、テーブルをのデータを取得するにはSELECT権限が必要です。
ロール

ロールとは、ユーザをまとめるためのグループです。ロールに追加されたユーザは、ロールに適用された権限を継承します。ユーザ数が多い場合や、セキュリティの割り当てが複雑な場合、ロールを利用するとセキュリティ管理が容易になります。以下の３種類のロールがあります。

種類	説明
固定サーバーロール	サーバレベルでのグループ管理権限。
固定データベースロール	データベースレベルでの管理権限
ユーザ定義ロール	グループごとに実行できる権限

9. 分散データベース

分散データベース環境とは、複数サーバーに同じ情報が複数コピーされている環境です。

データをユーザの近くに配置
サイトに自律性を保つ
OLTPとデータウエアハウスの分離
競合の削減

データを配布するには、以下の２つの方法があります。

レプリケーション
分散トランザクション

9.1. レプリケーション

転送元データベースから、通常は別のサーバーにある転送先データベースへ最新のデータを複製します。サイト自律性がサポートされ、サイトを断続的にオンラインにすることができます。

SQL Server では、３種類のレプリケーションを提供します。同じデータベースで、複数のレプリケーションの種類を併用できます。

マージレプリケーションレプリケーションされたデータを変更でき、その後、すべてのサイトからの変更内容は、マージされます。同一の結果セットに収束することが保証されます。複数のサーバーで更新が行われるため、同じデータが更新される場合があります。このため、競合が発生する可能性があります。

種類	説明
	データの新しいスナップショットを定期的に一括転送します。。
トランザクションレプリケーション	変更された部分のレプリケーションを行います。データの遅延が最小になります。
マージレプリケーション	レプリケーションされたデータを変更でき、その後、すべてのサイトからの変更内容は、マージされます。同一の結果セットに収束することが保証されます。複数のサーバーで更新が行われるため、同じデータが更新される場合があります。このため、競合が発生する可能性があります。

9.2. 分散トランザクション

データのすべてのコピーが、同時に同じ値を持つことを保証します。分散トランザクションに含まれている各サーバーは必ず常時オンラインになっている必要があります。各サーバーでトランザクションを完了できなければなりません。常にデータの同期を取る必要があります。

MS DTC（Microsoft 分散トランザクションコーディネ－タ）は２フェーズコミットを使用して、参加しているすべてのサイトでトランザクションが同時に完了することを保証し、分散トランザクションを可能にします。

10. クライアントサーバ型ＤＢ

データベースを利用するには、モデリングするアプリケーションアーキテクチャーの概念の必要となります。

10.1. クライアントサーバ型ＤＢ

SQL Server はクライアントサーバー型のデータベースです。複数のアーキテクチャーを使用してアプリケーションを実装できます。アプリケーションは、以下の３つの論理層に分割できます。

論理層	説明
プレゼンテーション層	データとアプリケーションをユーザに提示するためのロジックが含まれる。
ビジネス層	アプリケーションロジックとビジネスルールが含まれる
データ層	データ定義、データ整合性ロジック、ストアドプロシージャ、およびデータと密接に関連付けられた操作。SQL Server はこの層に含まれます。

これらの論理層は、複数のサーバーに配置することができます。以下の組み合わせがあります。

図 7. アプリケーション・アーキテクチャー

10.2. 共通データベースアクセス

ユーザはデータベース API やをデータオブジェクトを使用して SQLServer にアクセスします。SQL Server は、以下の API をサポートしています。

図 8. 　データベース API とデータベースオブジェクト

データベース API

データベース API は、データベースに接続し、コマンドを渡すインタフェースを提供します。

以下の API があります。

API	機能
ODBC	ODBC はコールレベルインターフェースです。ODBC は SQLServer やその他のRDBにアクセスできますが、その他のデータソースにアクセスすることはできません。
ＯＬＥＤＢ	コンポーネントオジュケトモデル（COM）ベースの API です。OLE DB は、SQLServer やその他の RDB や、その他のデータソースにアクセスできます。

データオブジェクトインターフェース

データオブジェクトインタフェースを利用するとデータベース API を使用するより簡単にデーターベースにアクセスできます。

データオブジェクト	内容
RDO （リモートデータオブジェクト）	RDO は ODBC API をカプセル化します。RDO は Visual Basic や VBA から使用できます。
ADO （ActiveX　データオブジェクト）	ADO は OLE DB API をオブジェクトモデルにカプセル化しています。ADO は、Visual Basic や VBA から使用できます。また、ASP などからも使用できます。

10.3. ストアドプロシージャ

ストアドプロシージャは、SQL ステートメントの集まりをカプセル化し、名前をつけてサーバーに格納します。一度作成したストアドプロシジャーはクライアントから何度でも繰り返し実行できます。

ストアドプロシージャはサーバー上で実行され、クライアントには、実行結果のみを返します。

図 9. ストアド プロシージャの作成と実行

ストアドプロシージャの作成

ストアドプロシージャを作成するときは、SQL ステートメントの構文解析が行われます。 SQL Server は、ストアドプロシージャ名を現在のデータベースのシステムテーブルに格納します。構文エラーが見つかるとストアドプロシージャは作成されません。

ストアドプロシージャの初回実行

ストアドプロシージャが初めて実行されるとき、クエリプロセッサでオブジェクト解決処理を行います。ストアドプロシージャで参照しているテーブルが存在しないと実行時エラーとなります。ストアドプロシージャが正常に解決されると、SQL Server クエリオプティマイザが最適化を行い実行プランを作成します。実行プランをコンパイルして、プロシージャキャッシュに格納します。2 回目以降に呼出された場合は、プロシージャキャッシュ内の実行プランを再利用するので効率的に実行されます。

10.4. トリガ

トリガは、トリガの作成されたテーブルを変更するたびに実行される特殊なストアドプロシージャです。トリガはユーザテーブルと関連付けられており、テーブルでの挿入、更新、削除が行われると、登録されたトリガが自動的に起動されます。トリガを直接起動することはできません。トリガとトリガを起動するステートメントは単一のトランザクションとして扱われます。トリガ内の任意の場所からロールバックすることができます。トリガを作成するには、CREATE TRIGGER ステートメントを使用します。トリガを定義するテーブル、トリガを実行するイベント、トリガによって実行される SQL ステートメントを指定します。トリガは、複雑なデータ整合性の適用や、データベース内の関連テーブル全体での変更の連鎖を行います。

11. データウェアハウスと OLAP

SQL Server が提供するデータベースは、トランザクション処理を主体とする基幹系データベースとして利用から、企業分析を行う情報系データベースでの利用へと拡大しています。データウエアハウスについて説明します。

11.1. データウェアハウスの特徴

データウエアハウスとは、企業の情報を集約し、企業分析を行うためのデータのことです。データウエアハウスに保存されているデータは、OLTP システムのデータとは異なる性質を持っています。

サブジェクト指向
併合性
不揮発性
履歴性

データウエアハウスは企業全体のデータを保存しますが、データマートは特定の部門や特定業務だけのデータを保存します。

データウエアハウジングを構成するコンポーネントは以下のとおりです。

図 10. データウエア ハウジングを構成するコンポーネント

コンポーネント	機能
データ変換サービス	データの移動および変換を行います。
データウエアハウスストレージ	SQL Server Analysis Services が分析を行うためのデータストレージです。
Analysis Services	リレーショナルデータベースからデータを取り出し、分析アプリケーションのために、キューブを作成します。
クライアントアプリケーション	Excel2000 は Microsoft が開発した OLAP クラアントです。Pivot Table が分析サーバーと連携して動作します。

11.2. 多次元モデル

スタースキーマ

データウエアハウスでは、データをスタースキーマと呼ばれる構造に編成します。スタースキーマの特徴は、中心にファクトテーブルがあり、それを取り囲む様に、数多くのディメンションテーブルがあります。
キューブ（多次元モデル）

キューブは、OLAP データベース用の論理的なストレージ構造です。キューブは、ディメンションとメジャーを統合しています。ユーザはこのキューブを操作してクエリーを行います。キューブの各セルには、スプレッドシートのようにそれぞれ１つの値を保持しています。各セルの値は、ディメンションが交差した場所の値です。例えば、商品、地域、期間ごとの売上が格納されています。

図 11. 　キューブとスライス
スライス

スライスは、キューブのどのディメンションのどの項目を他のディメンションの中で抽出するか指定します。

このキューブの例では、特定の製品（Cherries）の 4 半期ごとのすべての地域を売上を抽出しています。
ドリルダウン／ドリルアップ

すべてのディメンションには、階層が含まれています。例えば、期間は、年、四半期、月、日のレベルがあります。階層を構成する複数のレベルに対して、ドリルダウン／ドリルアップが可能です。

処理	説明
ドリルダウン	階層の下の階層を確認できます。例えば、季節変動を確認するには、四半期から月へドリルダウンします。
ドリルアップ	詳細情報は隠され、上の階層の要約情報だけが表示されます。例えば、都市から州へドリルアップします。

11.3. データマイニング

多くの企業では、データの量が多すぎて、多量のデータから意味のある情報を識別することは困難です。

データマイニングは、多量のデータから意味のあるパターンやルールを導き出す自動的な処理です。導き出されたパターンから重要な洞察を行い、従来の手作業の分析方法では見落としがちだった情報を得ることができます。

SQL Server Analysis Servicesには、以下のデータマイニング技法があります。

技法	説明
クラスタリング	お互いに類似したデータをグループ化するときに使用します。マーケットや顧客分析として使用されます。
ディシジョンツリー	分類、予測の両方に使用されます。一連の質問およびルールを使用してデータのケースを分類します。一定タイプのケースに特有の結果が現れる可能性を予測できます。

12. Web コンピューティング

Web コンピューティングによるデータ公開やデータ交換が一般化されています。 Webコンピューティングの現状について説明します。

12.1. ＷｅｂサーバとＤＢサーバの連携

WWW の普及により、インターネットベースの Web システムの需要が高まりました。従来、Web コンテンツは静的な HTML 文書を公開するだけでしたがが、動的なコンテンツに変化しました。さらに、取り扱っているデータもファイルから、データベースへと変化しています。クライアントにはWebブラウザのみを配置します。業務処理は Web サーバーのアプリケーションプログラムで実行されます。IIS (Internet Information Server) は WindowsNT/2000 で動作する Web サーバーです。IIS では、下記のゲートウェイインターフェースを使用して Web アプリケーションを開発できます。

インターフェース	説明
CGI CommonGateway Interface	Perlなどのスクリプト言語を利用します。インタープリタを使用して実行されます。アプリケーションが起動されるたびに、独自プロセスが実行されます。そのため、トラフィックの多いWebサーバーには不向きです
ＩＳＡＰＩ Internet Server Application Program Interface	ISAPI を利用したアプリケーションは、IIS のインプロセス (DLL) として実行されるため、高速に動作します ASP (Active Server Pages) は ISAPI のモジュールとして提供されるスクリプトエンジンです。VBScript や Jscript が使用出来ます。

インターフェース

説明

CGI

CommonGateway Interface

Perlなどのスクリプト言語を利用します。インタープリタを使用して実行されます。アプリケーションが起動されるたびに、独自プロセスが実行されます。そのため、トラフィックの多いWebサーバーには不向きです

ＩＳＡＰＩ

Internet Server Application Program Interface

ISAPI を利用したアプリケーションは、IIS のインプロセス (DLL) として実行されるため、高速に動作します ASP (Active Server Pages) は ISAPI のモジュールとして提供されるスクリプトエンジンです。VBScript や Jscript が使用出来ます。

Web アプリケーションのセッション管理

クライアント／サーバーアプリケーションは、１つのセッション（接続）でデータ交換を行いますが、Web アプリケーションを作成した場合、Web ブラウザと Web サーバー間のやり取りは１度のデータ交換でセッションは切断されます。そのため、連続したデータ交換を行うためには、接続情報を維持しておく必要があります。

12.2. ＸＭＬ

マークアップランゲージは、テキスト文書の中に記号をつけて、文書の構造や表現するための言語です。具体的な言語には HTML、XML があります。ＸＭＬ（eＸtensible Markup Language）は HTML と同様に SGML （Standard Generalized Markup Language）から派生しました。 HTML では、文書のレイアウトや、構造をタグを使用して記述出来ますが、定義済みのタグしか使用することが出来ません。そのため、HTML 文書中のデータに対して意味付けを行うことが出来ません。XML では、文書中のデータに独自のタグ付けを行うため、データに意味付けを行うことが出来ます。

XML は拡張可能なマークアップ言語です。以下の特徴があります。タグの意味を独自に定義できるテキストファイルとして保存できる

XML は、企業間の電子データの交換において重要な言語です。今後広く使用されます。

SQL Server の XML 対応

SQL Server は、XML に対応しています。SELECT ステートメントで FOR XML AUTO 句を使用すると、以下の処理を行います。
```
クエリ結果尾を文字列で返す
データ属性をタグとして返す
```
結果は、すべての列が１つの文字列に結合して返されます。クライアントブラウザはタグを使用して、返されたデータをフォーマットします。
SQL Server と IIS の連携

HTTP プロトコルを使用し、URL を記述することで、SQL Server にアクセスすることができます。テーブルに直接アクセスできます。

SQL Server と IIS の連携を行って HTTP を使用して、SQL Server にアクセスできるようになります。SQL Server と IIS の連携を行うには、IIS の仮想ディレクトリーの設定を行う必要があります。

SQL Server の IIS 仮想ディレクトリー管理ツールを使用します。

図 12. Web ブラウザからの XML の利用

ページのトップへ

プロファイル (個人情報) の管理 | お問い合わせ先 | MSDN Flash ニュースレター | 日本での個人情報の取り扱い | サイトマップ