SQL Server のビューに関する究極のガイド

SQL Server のビューについて、ビューの作成、使用、管理の方法など、知っておくべきことをすべて学びます。この究極のガイドでは、基本的な内容とそれ以降の内容をすべてカバーしています。

導入

複数のデータベース テーブルからデータを取得し、このデータを操作することは、ビジネス アプリケーションの一般的なニーズです。ビューはデータベースでこれを行う方法の 1 つです。ビューを作成し、複数のテーブルからデータを取得できます。ビューはデータベース内に仮想テーブルを作成し、クエリを使用してビューを操作できます。この記事では、SQL Server のビューについて学習します。ここでは、SQL のビューとは何か、ビューの種類、SQL でのビューの作成、SQL Server の SQL コード例を使用したビューの変更などのトピックについて説明します。

SQL Server のビュー

ビューは、1 つ以上のテーブルのデータを保持する仮想テーブルです。データベースに保存されます。ビューにはデータ自体は含まれません。ビューは、データベース内にオブジェクトとして保存されている 1 つ以上のテーブルに適用されるクエリのセットです。ビューはデータベースのセキュリティ目的で使用されます。ビューは、ユーザーが特定の列と行を表示できないように制限します。

つまり、ビューを使用すると、特定のユーザーの特定の行と列へのアクセス制限を適用できます。ビューは、同じデータベースまたは異なるデータベースのテーブルを使用して作成できます。これは、SQL Server にセキュリティ メカニズムを実装するために使用されます。

上の図では、クエリを使用して、2 つのテーブル (テーブル A とテーブル B) の列を含むビューを作成しました。ビューは select ステートメントを使用して作成されます。ビューはオブジェクトとしてデータベースに保存されるため、追加の保存スペースは必要ありません。ビューについての議論を始める前に、ビューについての基本的な知識を持っておく必要があります。

SQL でのビューの使用

ビューは、SQL Server にセキュリティ メカニズムを実装するために使用されます。ビューは通常、ユーザーが特定の列と行を表示できないように制限するために使用されます。ビューにはクエリで指定されたデータのみが表示されるため、ビューの作成時に定義されたクエリによって返されたデータのみが表示されます。残りのデータはエンドユーザーからは完全に抽象的なものです。 

SQL Server のビューの種類

ビューには以下の 2 種類があります。

1. ユーザー定義のビュー
2. システム定義のビュー

まず、ユーザー定義ビューについて説明します。

ユーザー定義ビュー

まず、2 つのテーブルを作成します。まず、従業員の基本情報を示す Employee_Details テーブルを作成します。

CREATE TABLE [dbo].[Employee_Details]
(
    [Emp_Id] [int] IDENTITY(1,1) NOT NULL,
    [Emp_Name] [nvarchar](50) NOT NULL,
    [Emp_City] [nvarchar](50) NOT NULL,
    [Emp_Salary] [int] NOT NULL,
 CONSTRAINT [PK_Employee_Details] PRIMARY KEY CLUSTERED
   (
    [Emp_Id] ASC
   )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
 ON [PRIMARY]

GO

次に、次のようにテーブルにデータを挿入します。

Insert Into Employee_Details Values('Pankaj','Alwar',25000)
Insert Into Employee_Details Values('Rahul','Jaipur',26000)
Insert Into Employee_Details Values('Rajan','Delhi',27000)
Insert Into Employee_Details Values('Sandeep','Alwar',28000)
Insert Into Employee_Details Values('Sanjeev','Jaipur',32000)
Insert Into Employee_Details Values('Narendra','Alwar',34000)
Insert Into Employee_Details Values('Neeraj','Delhi',29000)
Insert Into Employee_Details Values('Div','Jaipur',25000)
Insert Into Employee_Details Values('Tanuj','Alwar',22000)
Insert Into Employee_Details Values('Nitin','Jaipur',20000)

これで、テーブル Employee_Detail は次のようになります。

Select * from Employee_Details

Employee_Contact という名前の別のテーブルを作成します。

CREATE TABLE [dbo].[Employee_Contact]
(
    [Emp_Id] [int] NOT NULL,
    [MobileNo] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employee_Contact]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Contact_Employee_Details] FOREIGN KEY([Emp_Id])
REFERENCES [dbo].[Employee_Details] ([Emp_Id])
GO

ALTER TABLE [dbo].[Employee_Contact] CHECK CONSTRAINT [FK_Employee_Contact_Employee_Details]
GO

次のように、いくつかの値を Employee_Contact テーブルに挿入します。

Insert Into Employee_Contact Values(1,'9813220191')
Insert Into Employee_Contact Values(2,'9813220192')
Insert Into Employee_Contact Values(3,'9813220193')
Insert Into Employee_Contact Values(4,'9813220194')
Insert Into Employee_Contact Values(5,'9813220195')
Insert Into Employee_Contact Values(6,'9813220196')
Insert Into Employee_Contact Values(7,'9813220197')
Insert Into Employee_Contact Values(8,'9813220198')
Insert Into Employee_Contact Values(9,'9813220199')
Insert Into Employee_Contact Values(10,'9813220135')

これで、テーブル Employee_Contact は次のようになります。

select * from Employee_Contact

ここからは、ユーザー定義ビュー (UDV) について詳しく説明します。

SQL Server で SQL VIEW を作成する

1. CREATE VIEW view_name AS  
2. SELECT 列  
3. FROM テーブル  
4. WHERE 条件。  

いくつかのビューを作成してみましょう。

方法 1、 テーブルのすべての列を選択できます。次の例はそれを示しています。

Create View Employee_View1
as
select * from Employee_Details

方法 2、テーブルの特定の列を選択できます。次の例はそれを示しています。

Create View Employee_View2
as
select Emp_Id,Emp_Name,Emp_City from Employee_Details

方法 3、特定の条件でテーブルから列を選択できます。次の例はそれを示しています。

Create View Employee_View3
as
select * from Employee_Details where Emp_Id>3

方法 4、さまざまなテーブルの列を保持するビューを作成できます。次の例はそれを示しています。

Create View Employee_View4
as
select Employee_Details.Emp_Id,Employee_Details.Emp_Name,Employee_Details.Emp_Salary,Employee_Contact.MobileNo from Employee_Details
Left Outer Join
Employee_Contact
on
Employee_Details .Emp_Id= Employee_Contact.Emp_Id
Where Employee_Details.Emp_Id>2

SQL Server のビューからデータを取得する

この SQL CREATE VIEW の例では、select ステートメントの結果セットに基づいて仮想テーブルを作成します。これで、次のようにビューからデータを取得できるようになります。

Select * from Employee_View4

Select Emp_Id,Emp_Name,Emp_Salary from Employee_View4

前述のクエリは、ビューからすべての列または一部の特定の列を選択できることを示しています。

SQL Server でのビューの削除

Drop コマンドを使用してビューを削除できます。たとえば、ビュー Employee_View3 を削除するには、次のステートメントを使用できます。

Drop View Employee_View1

SQL Server でのビューの名前変更

sp_rename システム プロシージャを使用してビューの名前を変更できます。sp_rename コマンドの構文を以下に示します。

Sp_Rename OldViewName , NewViewName

例

Sp_Rename Employee_View4 , Employee_ViewNew

前の例では、ビュー Employee_View1 の名前を Employee_ViewNew に変更します。

ビューに関する情報の取得

Sp_Helptext システム ストアド プロシージャを使用して、ビューのすべての情報を取得できます。例を見てみましょう。

Sp_Helptext Employee_View4

出力

SQL Server でビューを変更する 

ビューのスキーマまたは構造を変更できます。つまり、一部の列を追加または削除したり、事前定義されたビューに適用される一部の条件を変更したりできます。例を見てみましょう。

Alter View Employee_View4
as
select Employee_Details.Emp_Id,Employee_Details.Emp_Name,Employee_Details.Emp_Salary,Employee_Contact.MobileNo from Employee_Details
Left Outer Join
Employee_Contact
on
Employee_Details .Emp_Id= Employee_Contact.Emp_Id
Where Employee_Details.Emp_Id>5 and Employee_Details.Emp_City='Alwar'

SQL Server でのビューの更新

ここで、テーブル Employee_Details に新しい列を追加するシナリオを検討し、その効果を調べてみましょう。まずビューを作成します。

Create View Employee_View1
as
Select * from Employee_Details

次に、Employee_Details テーブルに列を追加します。  

Alter Table Employee_Details Add MY_sal nvarchar(50)

ここでテーブルとビューからデータを取得すると、次の出力が表示されます。

Select * from Employee_Details
Select * from Employee_View1

出力

ビューのスキーマがすでに定義されているため、期待した結果が得られません。したがって、テーブルに新しい列を追加しても、ビューのスキーマは変更されず、ビューには以前のスキーマが含まれます。この問題を解決するには、システム定義のストアド プロシージャ sp_refreshview を使用します。

sp_refreshview は、テーブルのスキーマを編集すると、ビューのメタデータを更新するシステム レベルのストアド プロシージャです。以下を実行してみましょう。

Exec sp_refreshview Employee_View1
Select * from Employee_Details
Select * from Employee_View1

出力

スキーマビューのバインド 

前の例では、テーブルに新しい列を追加した場合、ビューを更新する必要があることがわかりました。

このように、テーブル内の列のデータ型を変更した場合は、ビューを更新する必要があります。ベーステーブル内のいかなる種類の変更も防ぎたい場合は、SCHEMABINDING の概念を使用できます。ビューによって参照されているテーブルをロックし、テーブル スキーマを変更する可能性のあるあらゆる種類の変更を制限します (Alter コマンドは使用しません)。

クエリで「Select * from tablename」を指定することはできません。参照用にすべての列名を指定する必要があります。

Create View Employee_Details3
with SCHEMABINDING
as
select Emp_Id,Emp_Name,Emp_Salary,Emp_City from DBO.Employee_Details

前の例では、Schemabinding を使用してビューを作成します。次に、ベース テーブルで Emp_Salary のデータ型を int から Decimal に変更してみます。

ベース テーブルでのいかなる種類の変更も禁止する SCHEMABINDING を使用したため、データ型を変更できないことがわかります。

SQL Server でのビューの暗号化

「WITH ENCRYPTION」オプションを使用すると、あらゆるビューを暗号化できます。つまり、SP_HELPTEXT 経由では表示されなくなります。このオプションは定義を暗号化します。このオプションはビューの定義を暗号化します。ユーザーは、ビューの作成後にビューの定義を確認することはできません。これがビューを安全にできる主な利点です。

Create View Employee_Details4
with Encryption
as
select Emp_Id,Emp_Name,Emp_Salary,Emp_City from DBO.Employee_Details

次に、ビューの定義を取得してみます。

Exec sp_helptext 'Employee_Details4'

出力

チェックオプション

ビューでチェック オプションを使用すると、すべての更新コマンドと挿入コマンドがビュー定義の条件を満たす必要があります。

例を見てみましょう

GO

Create view [dbo].[Employee_Details7]
as
select * from Employee_Details
where Emp_Salary>30000

GO

前の例では、Emp_Salry > 30000 のすべてのデータを含むビューを作成しますが、次のように 30000 未満の給与のデータを挿入することもできます。

Insert Into Employee_Details7 values ('ram','mumbai',25000,'Pan')

この問題を防ぐために、次のような Check Option プロパティを使用できます。

GO

Create view [dbo].[Employee_Details7]
as
select * from Employee_Details
where Emp_Salary>30000
with Check Option
GO

ここで、前述のクエリを実行しようとすると、次のようなエラーがスローされます。

Insert Into Employee_Details7 values ('ram','mumbai',25000,'Pan')

出力

メッセージ 550、レベル 16、状態 1、行 1

ターゲット ビューが WITH CHECK OPTION を指定しているか、WITH CHECK OPTION を指定しているビューにまたがっており、操作の結果として得られた 1 つ以上の行が CHECK OPTION 制約に適合しなかったため、挿入または更新の試行は失敗しました。

ステートメントは終了されました。

ビュー内の DML クエリ

ビューでは、挿入、更新、削除などのさまざまな種類の DML クエリを実装できます。ただし、DML クエリを正常に実装するには、次のような条件を使用する必要があります。

1. ビューには複数のテーブルを含めないでください
2. ビューには set 関数を含めないでください。
3. ビューでは Distinct キーワードを使用しないでください
4. ビューには Group By 句を含めないでください 
5. ビューにはサブクエリを含めないでください
6. ビューでは集合演算子を使用しないでください 
7. INSERT クエリが機能するには、ベース テーブルのすべての NOT NULL 列がビューに含まれている必要があります。

前述の条件を使用すると、問題なくビューに DML クエリを実装できます。例を見てみましょう。

select * from Employee_Details7

出力

ここで、次のように DML クエリを実装します。

Insert Into Employee_Details7 values ('ram','mumbai',35000)
Update Employee_Details7 set Emp_Name='Raju' where Emp_id=5
delete from Employee_Details7 where Emp_Id=6
select * from Employee_Details7

出力

システム定義ビュー

SQL Server には、Tempdb、Master、temp などのさまざまな定義済みデータベースも含まれています。各データベースには独自の特性と責任があります。マスター データは、他のすべてのユーザー定義データベースのテンプレート データベースです。

Master データベースには、他のデータベースやテーブルのテンプレートとして機能する多くの Predefine_View が含まれています。マスター データベースには、約 230 の事前定義されたビューが含まれています。

これらの事前定義されたビューは、私たちにとって非常に役立ちます。システムビューは主に次の 2 つの部分に分けられます。

1. 情報スキーマ
2. カタログビュー

情報スキーマ

システムには 21 近くの情報スキーマがあります。これらは、テーブルや列など、データベースの最も物理的な情報を表示するために使用されます。情報スキーマは INFORMATION_SCHEMA.[ビュー名] から始まります。

例を見てみましょう。

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where TABLE_NAME='Employee_Details'

出力

この Information_Schema は、テーブル Employee_Details で使用されるすべてのビューの詳細を返します。

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME='Employee_Details'

出力

この Information_Schema は、テーブルの制約に関する情報を返します。

カタログビュー

カタログ ビューもさまざまなグループに分類されています。これらは、データベースの自己記述情報を表示するために使用されます。これらは「sys」で始まります。

select * from sys.all_views

このクエリは、データベースを使用してすべてのタイプのビューに情報を提供します。

select * from sys.databases

このクエリは、ユーザー定義データベースとシステム定義データベースを含む、システムによって定義されたすべてのデータベースに関する情報を提供します。

結論

この記事では、SQL Server のコード例を使用して、SQL Server のさまざまなタイプのビューについて学びました。

#sqlserver #sql