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