Create SQL View Syntax Example

View in Sql is useful when we want to fetch data from multiple tables with complex join, You also can set permission to view to control who can view the data.

View is the logical set of data; we can create sql view from one or multiple tables, SQL View is read-only data; learn how to create view in SQL

In earlier article you have learned SQL Table, now you learn SQL View

Important Characteristic of SQL View
  • A VIEW is a logical set of data, can be from one table or multiple tables.
  • Views do not contain data of their own.
  • View is designed for restricting direct access to the database, and exposing data as per user role.
  • View is a just a Select statement saved in database in form of an object for better re-usability.

Sql view advantages

Why do we need View in SQL?
View is very useful when you don’t want to give access of all data of any table to particular user group, or you want to give access to some set of data without giving access of all tables.

Sql view example: ceate view in SQL Database

We can create view from one table or joining multiple tables, here is an example

Create view [vwEmpSocialNetwork] 
As
SELECT     
tbEmpSocialNetwork.EmpSocialId, 
tbEmpSocialNetwork.Facebook, 
tbEmpSocialNetwork.LinkedIn, 
tbEmpSocialNetwork.Twitter,  
tbEmpSocialNetwork.PersonalBlog, 
tbEmpSocialNetwork.Updated, 
tbEmployee.FirstName, 
tbEmployee.MiddleName,  
tbEmployee.LastName, 
tbEmployee.EmployeeId 
FROM   tbEmployee left outer JOIN  tbEmpSocialNetwork
ON tbEmployee.EmployeeId = tbEmpSocialNetwork.EmployeeId
Alter view SQL Database

Altering sql view means making any modification in View syntax, that will not have any impact on data, but the output may change

Alter view [vwEmpSocialNetwork]             
As            
SELECT            
// Specify all column names you want in view

Here you can see how to check dependency of any view, Just right click on view and then select "View Dependency"

You also can set permission of any view to particular user or user role,
Right Click => Select Properties

As you can see in the above picture, you can set permission to sql view, give permission to user or some roles to access your view

Drop view in SQL Database

Dropping view means deleting the view from database permanently

drop view [vwEmpSocialNetwork]

Remember once you drop the view, the object will be deleted from database, means all the sql query joins you wrote in view, will be gone permanently, so, be careful, alternately, you can rename it with version number to keep the backup.

 
Hire SQL Developer
SQL View Example
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
MS SQL Examples | Join MS SQL Course