SQL Server tutorial part 5 - View

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Sample table

Create Table customer(
  CustomerId int not null Identity(1,1),
  CustomerName varchar(100),
  Primary Key(CustomerId)
)
 
Create Table CustomerDetails(
  DetailsId int not null identity(1,1),
  CustomerId int foreign key references Customer(CustomerId),
  sort int,
  refCode int
)

SampleTable.sql - Snippet hosted by

RAW

Sample Data

Insert into Customer(CustomerName)
Values ('sam'), ('jim'), ('john')
 
Insert into CustomerDetails(CustomerId, sort, refCode)
Values (1, 1, 4216), (2, 2, 5211), (3, 3, 8546)

SampleDate.sql - Snippet hosted by

RAW

Create View

 
select *
from VCustomer

UseView.sql - Snippet hosted by

RAW

CREATE VIEW [VCustomer] 
AS
Select cus.CustomerId, sort, refCode, CustomerName
From Customer as cus
Inner join CustomerDetails as dei on dei.CustomerId = cus.CustomerId
 
 

CreateView.sql - Snippet hosted by

RAW

Update View

select *
from VCustomer

SelectFromView.sql - Snippet hosted by

RAW

-- Remove sort in the select statement
 
Alter VIEW [VCustomer] 
AS
Select cus.CustomerId, refCode, CustomerName
From Customer as cus
Inner join CustomerDetails as dei on dei.CustomerId = cus.CustomerId

AlterView.sql - Snippet hosted by

RAW

Drop View

Drop View VCustomer

DropView.sql - Snippet hosted by

RAW

Please donate and support at https://www.paypal.me/Rajcoomar