Author : HASSAN MD TAREQ

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

Creating view in ssms Step 1

Step-2

Creating view in ssms Step 2

Step-3

Creating view in ssms Step 3

Step-4

Creating view in ssms Step 4

Step-5

Creating view in ssms Step 5

Step-6

Creating view in ssms Step 6

Step-7

Creating view in ssms Step 7

Step-8

Creating view in ssms 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