Views in SQL Server

Introduction

A view in SQL Server is a virtual table-like structure based on the result-set of an SQL statement. On the surface, a view is similar to a table with the signature structure of rows and columns. However, these rows and columns come from tables referenced in the query, which defines the View.

We use Views to focus on the concrete columns for the purposes they are created for. Views may also serve for security reasons. They filter out columns in the underlying tables which one does not want to make visible to certain users. Views filter columns like a WHERE clause filters rows.

Another reason for Views is simplicity. They aggregate columns from several different tables and create a general appearance that looks like a single table.

Types of Views

Basic user-defined views are easy to create. The process is similar to writing queries that reference one or more tables.

• Indexed Views are those that have been materialized or stored like a table. Indexed Views can improve the performance of queries that aggregate many rows. However, they aren’t suitable if underlying tables are frequently updated.
• Partitioned Views join horizontally partitioned data from tables locally (within the same instance) or across many, using Linked Servers.
• System Views are the common structures that SQL Server uses to expose the catalog metadata. System Views are most of those structures one queries for troubleshooting performance or investigating a SQL Server instance.

Creating a View from One Table

Have a look at the example in Listing 1. The first statement returns ALL records in the table Purchasing.PurchaseOrders (1a), while the second query returns only a few columns (1b).

Using the second query, we can create a View that returns the same result set as (1b). When we do this, we can query a View to get the desired output. Thus, we simplify the query for an end-user.

-- Listing 1: Creating a Basic User-Defined View
-- 1a

SELECT * FROM
  Purchasing.PurchaseOrders;

-- 1b
SELECT 
  PurchaseOrderID
  , SupplierID
  , OrderDate
  , ExpectedDeliveryDate
  FROM Purchasing.PurchaseOrders;

-- 1c
CREATE VIEW Purchasing.QuickOrders 
  AS
SELECT 
  PurchaseOrderID
  , SupplierID
  , OrderDate
  , ExpectedDeliveryDate
  FROM Purchasing.PurchaseOrders;

-- 1d
SELECT * FROM Purchasing.QuickOrders ;

Creating a View from Two Tables

Using JOINs, we can retrieve data from two or more tables that have a relationship. Using Views, we can simplify accessing such data.

Listing 2 (2a) shows a JOIN between Purchasing.PurchaseOrders and Purchasing.PurchaseOrderLines. We can create a View from this JOIN, and it will allow us to retrieve the same data using a query, as shown in (2c).

-- Listing 2: Creating a View from Two Tables
-- 2a
  SELECT 
  po.PurchaseOrderID
  , po.SupplierID
  , po.OrderDate
  , po.ExpectedDeliveryDate
    , pol.Description
    , pol.ExpectedUnitPricePerOuter
  FROM Purchasing.PurchaseOrders po
  INNER JOIN Purchasing.PurchaseOrderLines pol
  ON po.PurchaseOrderID=pol.PurchaseOrderID;

-- 2b
  CREATE VIEW Purchasing.DetailedOrders
    AS
  SELECT 
  po.PurchaseOrderID
  , po.SupplierID
  , po.OrderDate
  , po.ExpectedDeliveryDate
    , pol.Description
    , pol.ExpectedUnitPricePerOuter
  FROM Purchasing.PurchaseOrders po
  INNER JOIN Purchasing.PurchaseOrderLines pol
  ON po.PurchaseOrderID=pol.PurchaseOrderID;

-- 2c
  SELECT * FROM Purchasing.DetailedOrders;

Creating a View Across Databases

Using multi-part naming, we can reference tables in a different database. Therefore, we can do JOINs across the databases and create Views that span databases. It is helpful for certain applications that spread their data across databases in the same SQL Server instance.

Listing 3 shows a similar case as Listing 2, but with a difference: we add a third table to the JOIN query from a different database. Notice that we have to use a LEFT OUTER JOIN since no real relationship exists between the tables in both databases. Here, we use it only to illustrate creating a VIEW that spans different databases.

We’ve introduced an alias in the CREATE VIEW statement, as we have columns from two different tables with the same name. We must distinguish those columns in such cases.

  -- Listing 3: Creating a View Across Databases
  -- 3a
  SELECT 
  po.PurchaseOrderID
  ,po.SupplierID
  ,po.OrderDate
  ,po.ExpectedDeliveryDate
    ,pol.Description
    ,pol.ExpectedUnitPricePerOuter
    ,so.orderid
    ,so.custid
    ,so.orderdate
  FROM Purchasing.PurchaseOrders po
  INNER JOIN Purchasing.PurchaseOrderLines pol
  ON po.PurchaseOrderID=pol.PurchaseOrderID
  LEFT OUTER JOIN TSQLV4.Sales.Orders so
  ON po.PurchaseOrderID=so.orderid;

-- 3b
  CREATE VIEW Purchasing.DetailedOrdersDistributed
    AS
  SELECT 
  po.PurchaseOrderID
  ,po.SupplierID
  ,po.OrderDate
  ,po.ExpectedDeliveryDate
    ,pol.Description
    ,pol.ExpectedUnitPricePerOuter
    ,so.orderid
    ,so.custid
    ,so.orderdate AS OrdersOrderDate
  FROM Purchasing.PurchaseOrders po
  INNER JOIN Purchasing.PurchaseOrderLines pol
  ON po.PurchaseOrderID=pol.PurchaseOrderID
  LEFT OUTER JOIN TSQLV4.Sales.Orders so
  ON po.PurchaseOrderID=so.orderid;

-- 3c
  SELECT * FROM Purchasing.DetailedOrdersDistributed;

Have a look at Figure 1. It shows the result of executing the Listing 3(3c). Note that the last three columns are empty, as the TSQLV4.Sales.Orders table does not have any rows matching the JOIN condition.

Creating a View Across Instances

We can extend the last statement by introducing a table that lives in another instance entirely.

To achieve this, we must first create a Linked Server. We do it with the code similar to that shown in Listing 4.

-- Listing 4: Linked Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'IGIRI01\SQLEXPRESS', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IGIRI01\SQLEXPRESS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

Notice how we address the external table using a four-part name:

-- Listing 5: Creating a View Across Instances
-- 5a

CREATE VIEW Purchasing.DetailedOrdersExternal
    AS
  SELECT 
  po.PurchaseOrderID
  ,po.SupplierID
  ,po.OrderDate
  ,po.ExpectedDeliveryDate
    ,pol.Description
    ,pol.ExpectedUnitPricePerOuter
    ,ipol.StockItemID
    ,ipol.LastEditedWhen
  FROM Purchasing.PurchaseOrders po
  INNER JOIN Purchasing.PurchaseOrderLines pol
  ON po.PurchaseOrderID=pol.PurchaseOrderID
  INNER JOIN [IGIRI01\SQLEXPRESS].[WWI].[Purchasing].[PurchaseOrderLines] ipol
  ON po.PurchaseOrderID=ipol.PurchaseOrderID;

-- 5b
SELECT * FROM Purchasing.DetailedOrdersExternal;

Including Functions in Views

Since Views are essentially queries, we can apply to them almost anything we do with regular queries. We can include functions, WHERE clauses, CASE expressions, aliases, etc.

However, the ORDER BY clause is not allowed, except you use the “TOP 100 hack”. Listings 6 to 9 illustrate the usage of those clauses in Views.

-- Listing 6: Creating a View with a Function
CREATE VIEW Purchasing.DetailedOrdersComplex
    AS
  SELECT 
  ipol.PurchaseOrderID
  ,ipol.Description
  ,ipol.ExpectedUnitPricePerOuter
  ,ipol.StockItemID
  ,CONVERT(VARCHAR, LastEditedWhen, 113) AS LastEditedLongDate
  FROM [IGIRI01\SQLEXPRESS].[WWI].[Purchasing].[PurchaseOrderLines] ipol

-- Listing 7: Creating a View with a WHERE Clause
  CREATE VIEW Purchasing.DetailedOrdersComplexFilt
    AS
  SELECT 
  ipol.PurchaseOrderID
  ,ipol.Description
  ,ipol.ExpectedUnitPricePerOuter
  ,ipol.StockItemID
  ,CONVERT(VARCHAR, LastEditedWhen, 113) AS LastEditedLongDate
  FROM [IGIRI01\SQLEXPRESS].[WWI].[Purchasing].[PurchaseOrderLines] ipol
    WHERE   ipol.PurchaseOrderID<10;

  -- Listing 8: Creating a View a TOP Clause
  CREATE VIEW Purchasing.DetailedOrdersComplexTop
    AS
  SELECT TOP 10
  ipol.PurchaseOrderID
  ,ipol.Description
  ,ipol.ExpectedUnitPricePerOuter
  ,ipol.StockItemID
  ,CONVERT(VARCHAR, LastEditedWhen, 113) AS LastEditedLongDate
  FROM [IGIRI01\SQLEXPRESS].[WWI].[Purchasing].[PurchaseOrderLines] ipol

      -- Listing 9: Creating a View with a CASE Expression
  CREATE VIEW Purchasing.DetailedOrdersComplexTop
    AS
  SELECT TOP 10
    CASE 
  ipol.PurchaseOrderID
    WHEN 1 THEN 'First Order'
    WHEN 2 THEN 'Second Order'
    END PurchaseOrder
  ,ipol.Description
  ,ipol.ExpectedUnitPricePerOuter
  ,ipol.StockItemID
  ,CONVERT(VARCHAR, LastEditedWhen, 113) AS LastEditedLongDate
  FROM [IGIRI01\SQLEXPRESS].[WWI].[Purchasing].[PurchaseOrderLines] ipol

Indexed Views

We referred to Indexed Views earlier in the article. Indexed Views can improve performance, except for the cases where underlying tables are write-intensive. SQL Server requires certain SET options enabled before creating Indexed Views or performing certain operations on them.

The WITH SCHEMABINDING clause should be used when creating a View for putting an index on it. This clause associates the View with the underlying objects strictly. Thus, such objects cannot be dropped.

-- Listing 10: Creating an Indexed View
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;

  CREATE VIEW Purchasing.DetailedOrdersIndexed
    WITH SCHEMABINDING
    AS
  SELECT 
  po.PurchaseOrderID
  ,po.SupplierID
  ,po.OrderDate
  ,po.ExpectedDeliveryDate
  FROM Purchasing.PurchaseOrders po;

CREATE UNIQUE CLUSTERED INDEX IX_ID
   ON Purchasing.DetailedOrdersIndexed (PurchaseOrderID);

Conclusion

In this article, we have examined views at some level of detail. We briefly covered types of Views and gave several examples of user defined views and how we used JOINs to realize views that depend on many tables. We also covered complex views that include functions as well as indexed views.

References

1. Views
2. Indexed Views
3. Create Indexed Views in SQL Server