Hello, Guys In this tutorial we are going to learn what are Views in SQL Server. Views are a virtual table in SQL Server that does not contain its own data.

After Completing this tutorial you will be able to understand in SQL Server.

  • What are Views in SQL Server?
  • Why should we use View?
  • How to Create, Modify and Drop a View in SQL Server.

What are Views in SQL Server?

Views are database object that does not contain the data of itself It contains data from one or more tables. Views are a temporary object that is stored in the database. It is a virtual table that contains the select statements only. The purpose of using Views is to restrict the user from viewing certain columns from the tables. It provides the security mechanism because it hides the table name and certain fields from the user by providing the virtual table.

Why Should we use Views?

The purpose of using views in SQL Server is to provide security to the schema of the table in data because it encapsulates our table name and the fields that we don’t want to show to the user. The data inside the views are not stored permanently in the database. One more reason behind using a View in SQL Server is that if we have some complex type of join query from more than one table then instead of writing join query, again and again, we can create a View and use it anywhere.

The syntax of a View.

CREATE VIEW [SchemaName.]ViewName AS

[ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

SELECT expressions

FROM Tables

[WHERE Conditions];

Where,

  • SchemaName specifies the name of Schema that owns the View.
  • ViewName specifies the name of View.
  • ENCRYPTION It will encrypt text of the ALTER VIEW statement in sys.syscomments.
  • SCHEMABINDING It ensures that the underlying table definitions can not be modified so as to affect the VIEW.
  • VIEW_METADATA It will ensure that SQL Server has metadata about the VIEW.
  • The Expression specifies the ColumnNames or Calculative values.
  • Tables species the name of Table in the database.
  • Where Condition This is Optional that specifies the condition on the basis of which we want to get records.

Example using View:

Let’s take a look at how to create a view in SQL Server. Before creating a view we need to create a table and insert few records in that table

Create a table called Employee:


CREATE TABLE [dbo].[Employee](

[EmployeeID] [int] NOT NULL,

[Name] [varchar](100) NULL,

[Address] [varchar](150) NULL,

[Salary] [float] NULL,

PRIMARY KEY CLUSTERED

(

[EmployeeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

Insert few records in Employee Table.

INSERT [dbo].[Employee] ([EmployeeID], [Name], [Address], [Salary]) VALUES (1, N'Arvind', N'New Delhi', 20000)

INSERT [dbo].[Employee] ([EmployeeID], [Name], [Address], [Salary]) VALUES (2, N'Vikas', N'New Delhi', 10000)

INSERT [dbo].[Employee] ([EmployeeID], [Name], [Address], [Salary]) VALUES (3, N'Ritwik', N'Mumbai', 30000)

INSERT [dbo].[Employee] ([EmployeeID], [Name], [Address], [Salary]) VALUES (4, N'Karan', N'Noida', 50000)

INSERT [dbo].[Employee] ([EmployeeID], [Name], [Address], [Salary]) VALUES (5, N'Akhilesh', N'Noida', 40000)

GO

Create View.

Suppose I want to create a view to get records for those Employees who has salary less than 40000.

CREATE VIEW [dbo].[vwGetEmployee]

AS

SELECT [EmployeeID], [Name], [Address], [Salary] FROM [dbo].[Employee] WHERE [Salary]<40000

Run the Above Script.

Test the View.

To test the vwGetEmployee View run the following query.

SELECT * FROM [dbo].[vwGetEmployee]

The result will be:

Views

Alter a View.

You can alter a view using the Alter View Statement.

Syntax to Alter a View.

ALTER VIEW [SchemaName.]ViewName AS

[ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

SELECT expressions

FROM Tables

[WHERE Conditions];

The example of Alter View:

Suppose I want don’t want to get EmployeeID from the View. Our view would like this.

ALTER VIEW [dbo].[vwGetEmployee]
AS
SELECT  [Name], [Address], [Salary] FROM [dbo].[Employee] WHERE [Salary]<40000

Run the following query to test the result

SELECT * FROM [dbo].[vwGetEmployee]

You can see in the above snapshot there is no EmployeeID in the result set.

Views

How to Drop a View?

You can simply drop a View using the Drop Statement.

Syntax:

Drop View ViewName.

Where ViewName is the name of the View.

The example of Drop View:

DROP VIEW vwGetEmployee.

Conclusion:

Hope you understand the concept of Views in SQL Server. I would appreciate your feedback, comments and suggestions.

View More:

Thank You.

 

Leave a Comment