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:

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:
    • 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:

Creating view in SSMS


Creating view in ssms Step 1


Creating view in ssms Step 2


Creating view in ssms Step 3


Creating view in ssms Step 4


Creating view in ssms Step 5


Creating view in ssms Step 6


Creating view in ssms Step 7


Creating view in ssms Step 8

Creating view using T-SQL

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


SELECT * FROM hiredate_view