Tutorials
Search…
Lesson 35. Views
Views are T-SQL statements that are actually stored on SQL Server and can be called repeatedly without having to write the same code over and over.
Views can be used just like a table. As such, they have numerous use cases such as simplifying code, or creating a denormalized dataset that can be read by BI tools like Power BI or Tableau.
Views are just SQL statements and do not allow any of the programming aspects of T-SQL such as variables or looping.
To see views in SSMS object explorer, expand the database you are working in and then expand the views folder. If you do this for the AdventureWorks database, you can see numerous views.

Examples

Creating A View
In [ ]:
USE AdventureWorks2016
GO
DROP VIEW IF EXISTS SalesReport
GO
CREATE VIEW SalesReport AS
SELECT
CONCAT(p.FirstName, ' ',p.LastName) AS SalesPerson,
DATENAME(MONTH,soh.OrderDate) AS MonthOfSale,
DATEPART(YEAR,soh.OrderDate) AS YearOfSale,
SUM(sod.LineTotal) AS TotalSales
FROM Person.Person p
JOIN Sales.SalesPerson sp
ON p.BusinessEntityID = sp.BusinessEntityID
JOIN Sales.SalesOrderHeader soh
ON sp.BusinessEntityID = soh.SalesPersonID
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderDetailID
GROUP BY p.LastName, p.FirstName, DATENAME(month,soh.OrderDate), DATEPART(YEAR,soh.OrderDate)
Using A View
In [ ]:
USE AdventureWorks2016
SELECT *
FROM SalesReport
ORDER BY SalesPerson
Dropping A View
In [ ]:
USE AdventureWorks2016
DROP VIEW IF EXISTS SalesReport
Copy link