VIEW 操作に関する スニペット集 です。Viewの基本を理解している方が対象で、詳細な説明はありません。コードをコピーし、用途に合わせて修正して使用します。
CREATE VIEW (作成)
同名のViewがあればエラーUSE SampleDatabase
GO
CREATE VIEW dbo.SampleView
AS
SELECT T1.UserID
,T2.Point
FROM dbo.SampleTable1 T1
INNER JOIN dbo.SampleTable2 T2 ON T2.UserID = T1.UserID
WHERE 1 = 1
;
GO
同名のViewが無ければ作成、あれば変更
USE SampleDatabase
GO
CREATE OR ALTER VIEW dbo.SampleView
AS
SELECT UserID
,UserName
FROM dbo.SampleTable1 T1
WHERE EXISTS (
SELECT *
FROM dbo.SampleTable2 T2
WHERE T2.UserID = T1.UserID
)
;
GO
ALTER VIEW (変更)
USE SampleDatabase
GO
ALTER VIEW dbo.SampleView
AS
SELECT UserID
,SUM(Point) AS PointSummary
FROM dbo.SampleTable2
GROUP BY UserID
;
GO
DROP VIEW (削除)
VIEWが無い時はエラーUSE SampleDatabase
GO
DROP VIEW dbo.SampleView;
GO
VIEWが無い時は何もしない
USE SampleDatabase
GO
DROP VIEW IF EXISTS dbo.SampleView;
GO
sp_rename (名前変更)
USE SampleDatabase
GO
EXEC sp_rename 'dbo.BeforeView', 'AfterView';
GO
検証環境
- Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Linux (Ubuntu 16.04.7 LTS) <X64>
- Docker image: mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-16.04
- Docker Desktop 3.3.0(62916)
- Docker Engine 20.10.5
- Microsoft SQL Server Management Studio v18.8(15.0.18369.0)
- Microsoft Windows 10 Pro Version 20H2 OS Build 19042.928 Experience: Windows Feature Experience Pack 120.2212.551.0