SQL Tutorial - Views

This video will teach you how to create, alter and drop views with working examples, within SQL Server, we also look at the benefits and limitations of views and creating views with schemabinding and encryption.

CREATE VIEW ‘name’ (optional column list)
(optional) WITH (SCHEMABINDING / ENCRYPTION)
AS
SELECT STATEMENT

SQL Statements used below:

IF OBJECT_ID(N'vwSummary', N'V') IS NOT NULL
DROP VIEW vwSummary;

GO

CREATE VIEW vwSummary (Customer_Id, Customer_Name, Order_Id, Order_Date, Title, [Paperback/Hardback])
WITH ENCRYPTION
AS
SELECT C_Id AS Customer_Id, C_First_Name + ' ' + C_Last_Name AS Customer_Name, O_Id AS Order_Id, O_Date AS Order_Date, P_Title AS Title, P_Type AS [Paperback/Hardback] FROM dbo.Customers
INNER JOIN dbo.Orders ON C_Id = O_C_Id
INNER JOIN dbo.Order_Details ON O_Id = OD_O_Id
INNER JOIN dbo.Books ON OD_P_Id = P_Id;

SELECT Customer_Id, Customer_Name, Order_Id, Order_Date, Title, [Paperback/Hardback] FROM vwSummary
WHERE Customer_Id = 1

ALTER TABLE dbo.Customers
ALTER COLUMN C_First_Name NVARCHAR(100) NOT NULL

ALTER TABLE dbo.Customers
ALTER COLUMN C_Middle_Name VARCHAR(50) NULL

Find out View text:
SELECT b.[definition] FROM sys.objects AS A
INNER JOIN sys.sql_modules AS B ON A.object_id = B.object_Id
WHERE a.[type] = N'V':

CREATE VIEW vwLowStock (Title, Stock)
AS
SELECT P_Title, P_Stock
FROM dbo.Books
WHERE P_Stock ‘less than’= 5

DROP VIEW vwLowStock

CREATE VIEW vwTotals
AS
SELECT O_C_Id AS Customer_Id, SUM(O_Total) AS Total FROM Orders
GROUP BY O_C_Id

Views are covered in the Microsoft Exam – Querying SQL Server (70-461) 2012 / 2014.