Tuesday, June 6, 2017

Creating Horizontal Views and Vertical Views

Everyone has either used or created views for getting or creating a dynamic result using one or multiple tables. And everyone knows the usage of views and purpose of creating them. But many do not how they have been categorized; such as Horizontal and Vertical. Here is a simple explanation on them.

Horizontal Views

If you think about the standard definition of a view, it is a virtual relation (table) that is result of one or more relational operations on one or more relations (tables). In simple terms, is a logical table defined with a SELECT statement based on one or more tables. Now, what is a Horizontal View? It is a view that limits the number of records to be produced. We create Horizontal Views by adding WHERE clause.

USE AdventureWorks2014;
GO

CREATE VIEW dbo.Sales_2012
AS
SELECT * FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2012;
GO

Vertical Views

This view results set of selected columns from one or more tables. We implement this by selecting required columns, optionally adding the WHERE clause.

USE AdventureWorks2014;
GO

CREATE VIEW dbo.Sales_2012_Summary
AS
SELECT SalesOrderNumber, OrderDate, SubTotal 
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2012;
GO

In addition to that, there is another type called Grouped and joined views that is actually either a Horizontal View or Vertical View with aggregations and multiple joins, hence I do not think that it has to be taken as a major type.


No comments:

Post a Comment