What is view

• A view is a virtual table whose contents are defined by a query
• A searchable object in a database that is defined by a query
• Like a table, a view consists of a set of named columns and rows of data
• Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced
• A view acts as a filter on the underlying tables referenced in the view
• A view can be built on top of a single table or multiple tables or on top of another view
• If data is changed in the underlying table, the same change is reflected in the view
• Details: https://docs.microsoft.com/en-us/sql/relational-databases/views/views

Types of Views

https://docs.microsoft.com/en-us/sql/relational-databases/views/views#types-of-views

Advantages of view

• Security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view
• Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed
• Courtesy of following 3 points: https://www.1keydata.com/sql/sql-view.html
  • Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables
  • Space savings: Views takes very little space to store, since they do not store actual data (indexed view does take space to store data)
  • Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes
• More: https://www.essentialsql.com/what-is-a-relational-database-view/

Creating view in SSMS

Step-1

Step-2

Step-3

Step-4

Step-5

Step-6

Step-7

Step-8

Creating view using T-SQL

CREATE VIEW hiredate_view  
AS   
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate  
FROM HumanResources.Employee e   
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;  
GO

Usage

SELECT * FROM hiredate_view

More: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql