テーマ別の解説テーマ:SQL ServerとDBの基本
SQL Serverの構成:インスタンス・データベース・スキーマの違い
インスタンスは接続先のエンジン、データベースはその中のまとまり、スキーマはDB内の名前空間です。表はスキーマに属します。同じapp.Documentsでも、CorpDocsとTrainingDocsでは別の表です。接続先と現在のDBを含めて、操作対象を特定します。
一つのインスタンスにある2つのDBと、それぞれのスキーマ・表を包含関係として読める。表名だけで対象や権限を決めない。
SQL ServerがDBMS、SSMSが接続するツールであること。図と表は架空の構成で、インストールは不要です。
インスタンスの中にDB、その中にスキーマと表
SSMSが接続するSQL Serverインスタンス。
本例の文書は5行。主キー101〜105。
同名でも、各DB内の別の名前空間。
対象DBのデータを持つ表。
別DBの文書は2行。主キー101と106。
同名でも、各DB内の別の名前空間。
対象DBのデータを持つ表。
同名でも、左と右は別のデータベース内の表です。
図は架空の構成例です。所属を表す枠と、通信や権限を表す線を区別します。
図の枠は、物理フォルダーや通信経路ではなく、データベースの構成上の所属です。スキーマはDB内のオブジェクトをまとめ、所有者を持ちます。ユーザー名とスキーマ名が同じ場合でも、同じ種類のものではありません。
app.Documentsという名前を、現在のDBと読む
| 表記 | 示している範囲 | この例 |
|---|---|---|
| インスタンス | 接続先エンジン | sql-demo-1 |
| データベース | そのインスタンス内のDB | CorpDocs |
| schema.object | 現在のDB内のスキーマと表 | app.Documents |
| database.schema.object | 同じインスタンス内でDBを含めた対象 | CorpDocs.app.Documents |
SELECT DB_NAME() AS current_database;
SELECT document_id
FROM app.Documents
ORDER BY document_id;引数なしのDB_NAME()は、そのクエリの現在のDB名を返します。DB名を知ることと、表に対するSELECT許可があることは別です。既定スキーマに頼る一部だけの名前ではなく、ここではapp.Documentsのようにスキーマを示します。
5行と2行:データの違いと権限の違い
| DB | この例で存在する文書ID | reader_demoの権限 |
|---|---|---|
| CorpDocs | 101・102・103・104・105 | 必要な表へのSELECTを許可 |
| TrainingDocs | 101・106 | DBアクセスとSELECTは未指定 |
両DBでのSELECTを個別に許可し、表がこの定義のままなら、同じSELECTでもCorpDocsでは5行、TrainingDocsでは2行が対象になります。しかし本例のreader_demoにはTrainingDocsの権限を与えていません。「2行の表がある」だけで「2行取得に成功する」とは判断できません。
クエリを読む前に確認する組
| 確認対象 | なぜ必要か |
|---|---|
| 接続先インスタンス | 同じDB名が別インスタンスにもあり得る |
| 現在のDB | 同じschema.objectでも違う表になり得る |
| スキーマ付き表名 | DB内の名前空間を指定する |
| 主体と必要な許可 | 認証だけでは操作の許可は決まらない |
| SQLとデータの時点 | 対象列・条件・確定状態が結果を決める |
次のSSMS教材では、この組を接続画面の項目とクエリタブの現在DBへ対応させます。
包含関係と読取り条件を確認する
選択すると、正誤と理由が表示されます。
確認して、自分の言葉で説明する
- インスタンス → DB → スキーマ → 表を図で指す。
- CorpDocs.app.DocumentsとTrainingDocs.app.Documentsを別物として説明する。
- DB_NAME()で分かる範囲と表の権限を分ける。
- 同名表の行数を予想する前に、接続先と許可を確認する。
理解を確かめる: schema名がreader_demoなら、そのスキーマはユーザーと同じもの?
いいえ。スキーマとユーザーは異なる概念です。
スキーマはDB内の名前空間で、ユーザーはDB内の操作主体です。名前の一致で同一視しません。
出典と確認した条件
説明に用いた一次資料を、確認した内容・条件と本文の参照箇所に分けてまとめています。専門家による監修・実環境での検証は行っていません。
確認した内容・条件schemaはデータベース内に作成され、データベースユーザーと同義ではない。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。
参照した本文インスタンスの中にDB、その中にスキーマと表 app.Documentsという名前を、現在のDBと読む 包含関係と読取り条件を確認する
確認した内容・条件SQL ServerはRDBMSでありT-SQLで通信する。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。
参照した本文インスタンスの中にDB、その中にスキーマと表
確認した内容・条件SQL Serverのprincipalはserver levelとdatabase levelで範囲が違う。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。
確認した内容・条件SELECTは結果集合を返しWHEREで条件を絞れる。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。
確認した内容・条件DB_NAME()で現行DB名を照会できる。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。
確認した内容・条件ORDER BYなしに返却行の順序は保証されない。 説明用のSQL Serverの条件に限定。実DBの動作検証ではない。