View

View is a Database Object.
It contains Select statement.
virtual table whose contents are defined by a Select query. 
its like a table, consists of a set of named columns and rows of data.
It does not hold any data and does not exist physically in the database. 
It can contain database tables from single or multiple databases as well.

we are not using Begin End block in View.

Syntax

Create View ViewName
As
Select Query

Create table Department 

(

DepartmentId int identity(1,1) Primary Key,

Name varchar(50) Not Null

)

GO

Create table Employee

(

Id int identity(1,1) Primary Key,

FirstName varchar(50) Not Null,

LastName varchar(50) Not Null,

City varchar(50) Not Null,

AadharcardNumber BIGINT Not Null,

DepartmentId int null Foreign Key references Department(DepartmentId)

)

Insert Into Department(Name) values ('Account')

Insert Into Department(Name) values ('IT')

Insert Into Department(Name) values ('Finance')

Insert Into Department(Name) values ('Personnel')

Insert Into Department(Name) values ('Admin')

Insert Into Employee (FirstName,LastName,City,AadharcardNumber,DepartmentId) 

values 

('Manish','Sonawane','Pune',121232565545,1),

('Ganesh','Khedkar','Sangli',121212125559,2),

('Pravin','pawar','Pune',653212312123,2),

('Ghanshyam','Kale','Satara',545456454555,3),

('Amol','solanki','Nasik',675675674544,4),

('Nitin','Sonawane','Nagpur',435453453453,4),

('Sagar','Aher','Nasik',675685674544,2),

('Govind','Kumar','Nasik',656887674544,1)

Select * from Department

Select * from Employee

Go

------------Create View------------------

Create View VW_empDept

AS

Select 

E.id,

E.FirstName,

E.LastName,

E.city,

E.AadharcardNumber,

D.Name As DepartmentName

from Employee E left join Department D

on E.DepartmentId=D.DepartmentId

Go

Create View VW_emp

AS

Select E.Id,

E.FirstName,

E.LastName

from Employee E 

Go

Alter View VW_emp

AS

Select E.Id,

E.FirstName,

E.LastName,

E.city,

E.AadharcardNumber As AadharcardNumber

from Employee E 

Go

Drop View VW_emp

Go

List of Views in Database

Select * from sys.views

Select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'

See Definition of view

Sp_helptext VW_empDept

------------------Security

Use Master

Go

Create LOGIN sqllogin WITH PASSWORD = '32487^%^%$#!@hdgfgsdfjh'

Use [practice]

Go

create USER sqllogin FROM LOGIN sqllogin;

Go

Use [practice]

Go

GRANT SELECT ON dbo.VW_empDept TO sqllogin

-----------------insert View-------------------

Insert Into VW_emp(FirstName,LastName,City,AadharcardNumber) 

values ('Suresh','Rahane','Pune',545456456476)--Possible

Insert Into VW_empDept(FirstName,LastName,City,AadharcardNumber) 

values ('Nagesh','Rathore','Pune',545456456476)--Possible

Insert Into VW_empDept(FirstName,LastName,City,AadharcardNumber,DepartmentName) 

values ('Sures2h3','Rahane','Pune',545456456476,'IT')--Not Possible

--Msg 4405, Level 16, State 1, Line 108

--View or function 'VW_empDept' is not updatable because the modification affects multiple base tables.

-----------------Update View-------------------

Update VW_emp Set FirstName='SureshUpdate' Where Id=11--Possible

Update VW_empDept Set FirstName='NageshUpdate' Where Id=12--Possible

Update VW_empDept Set FirstName='NageshUpdate1',[DepartmentName]='IT' Where Id=12--Not Possible

--Msg 4405, Level 16, State 1, Line 113

--View or function 'VW_empDept' is not updatable because the modification affects multiple base tables.

Solution -  Instead of Trigger

-----------------Delete View-------------------

Delete From VW_emp where ID=12--Possible

Delete From VW_empDept where ID=11--Possible

--Msg 4405, Level 16, State 1, Line 118

--View or function 'VW_empDept' is not updatable because the modification affects multiple base tables.

Question: Can we create parameterized VIEW in SQL Server?

No. You can use UDF in which you can pass parameters.

UDF sample- 

Create Function [dbo].[fn_PatientListByCity]

(

@CityId Int

)

Returns Table

As

Return

SELECT [PatientID]

      ,[FirstName]

      ,[MiddleName]

      ,[LastName]

      ,C.Name As CityName

  FROM [HIMS].[dbo].[Patient] P

  Inner Join City C

  On P.CityID=C.CityID

  Where P.CityID=@CityId

Select * from [dbo].[fn_PatientListByCity](1)