Let’s create a SampleDB database fist with SQL Management Studio. Now we have to create a table in SampleDB.

Let’s Name the table as – Employee

CREATE TABLE [dbo].[tblEmployee](
        [Emp_ID] [int] NOT NULL,
        [Emp_Name] [varchar](max) NOT NULL,
        [Emp_Age] [int] NOT NULL,
        [Emp_Salary] [int] NOT NULL,
        [Emp_City] [varchar](max) NOT NULL,
        [Emp_Gender] [char](1) NOT NULL,
 CONSTRAINT [PK_tblEmployee] 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] TEXTIMAGE_ON [PRIMARY]

Now insert data in new Employee table :

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL
SELECT 2,'Shekhar',     19,     40000,  'Bagerhat',     1 UNION ALL
SELECT 3,'Shamim',      21,     35000,  'Dhaka',        0 UNION ALL
SELECT 4,'Shariar',     20,     40000,  'Rajshahi',     1 UNION ALL
SELECT 5,'Rajib',       22,     35000,  'Rajshahi',     0 UNION ALL
SELECT 6,'Avishek',     21,     35000,  'Barishal',     1 UNION ALL
SELECT 7,'Asif',        23,     40000,  'Dhaka',        0

Now what is View?
Creates a virtual table whose contents (columns and rows) are defined by a query. Used to hide the complexity of the underlying database schema.

Syntax:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH  [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] 
[ ; ]

Let’s create a view based on our newly created table:

-- CREATE VIEW --
CREATE VIEW [EmployeeView]
AS 
(
        SELECT * FROM [dbo].[tblEmployee]
)
GO 

SELECT * FROM [EmployeeView]
GO 

----------------------------------------------------
CREATE VIEW [EmployeeView_Column] ([Emp_ID],[Emp_Name])
AS 
(
        SELECT [Emp_ID],[Emp_Name] FROM [dbo].[tblEmployee]
)
GO 

SELECT * FROM [EmployeeView_Column]
GO

Let’s alter created view:

-- ALTER VIEW --
ALTER VIEW [EmployeeView]
AS 
(
        SELECT * FROM [dbo].[tblEmployee]
)
GO 

SELECT * FROM [EmployeeView]
GO 
----------------------------------------------------

ALTER VIEW [EmployeeView_Column] ([Emp_ID],[Emp_Name])
AS 
(
        SELECT [Emp_ID],[Emp_Name] FROM [dbo].[tblEmployee]
)

GO 

SELECT * FROM [EmployeeView_Column]
GO

Let’s drop altered view:

--DROP VIEW --
DROP VIEW [EmployeeView]
GO

When to use  View?

1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
2. To control access to rows and columns of data.
3. To aggregate data for performance