View - SQL

·         A view is the result set of a stored pre-established which stored in the database dictionary.

·         It is a virtual table.

·         When the view is requested, it computed data dynamically from the database.

·         A view really doesn't do anything until you query it.

·         Views can hide the complexity of data.

·         Using view, we can display customized data as a virtual table from various physical tables.

·         Views take very little space to store, the database contains only the definition of a view, not a copy of all the data that it presents.

Limitations

·         Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.

·         The select statement on the view cannot contain ORDER BY or INTO TEMP.

·         When a table or view is dropped, any views in the same database are also dropped.

Operation with View

Data can be insert/update/delete using views if it is not read-only.

E.G. Updating view.

Update V_Employee Set Salary = 90000 WHERE EmpId = 9

To make any view readonly in sql we have to append some constant value in the view

E.G.

CREATE VIEW V_Employee

AS

SELECT * FROM Employee

UNION

SELECT 0,'',0,'' FROM Employee WHERE 1 <> 1 -- constant values

Index VIEW

Similarly, as adding index in table, we can also add index in the view. But the performance of maintaining the index in view is much higher than maintaining the index in the table.

We should need to add the index on the table or view which are not frequently inserted/updated (means non transactional). They are more commonly used in scenarios when data modification is less.

Basically we use indexes on reporting purpose.

The response time to a query is fast at the cost of space consumption.

To Create index view please follow below steps-

Session Options that must be on

·         ANSI_NULLS 

·         ANSI_PADDING

·         ANSI_WARNINGS

·         ARITHABORT

·         CONCAT_NULL_YEILDS_NULL

·         QUOTED_IDENTIFIERS

Session options that must be off

·         NUMERIC_ROUNDABORT

--Create a deterministic view with new SCHEMABINDING syntax.

CREATE VIEW Sales.vOrders  

WITH SCHEMABINDING  

AS  

    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  

        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  

    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  

    WHERE od.SalesOrderID = o.SalesOrderID  

    GROUP BY OrderDate, ProductID;  

GO  

--Create an unique Clustered index on the view.  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   

    ON Sales.vOrders (OrderDate, ProductID);  

GO  

Partitioned views

Partitioned views allow data in a large table to be split into smaller tables.

These small tables are called as member tables. The split is done based on range of data values in one of the columns.

E.G.

--- History

CREATE TABLE FACT.sales_history (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_history

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current year

CREATE TABLE FACT.sales_thisyear (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thisyear

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current month

CREATE TABLE FACT.sales_thismonth (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thismonth

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

CREATE VIEW FACT.sales

WITH SCHEMABINDING

AS

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_history

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thisyear

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thismonth

Distributed Portioned View

In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.