VIEW is a virtual table, defined by a query,that does not exist until it is invoked by name in an SQL statement.

The definition of view is stored in syscomments system table.

A VIEW has no physical existence in the database until it is invoked. You cannot put constraints on a VIEW for that reason.

The name of the VIEW must be unique within the entire database schema, like a base table name. The VIEW definition cannot reference itself, since it does not exist yet. Nor can the definition reference only other VIEWs; the nesting of VIEWs must eventually resolve to underlying base tables. This only makes sense; if no base tables were involved, what would you be VIEWing?

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[WITH <view_attribute> [ ,...n ] ]

AS select_statement

[WITH CHECK OPTION ] [ ; ]

<view_attribute>::=

{

[ENCRYPTION]

[SCHEMABINDING]

[VIEW_METADATA] }

Schema_name

Is the name of the schema to which the view belongs.

view_name

Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.

Column

Is the name to be used for a column in a view.

A column name is required only when a column is derived from an arithmetic expression, a function, or a constant;

create table test(id int ,name varchar(20))

create table test2(id int ,name varchar(20))

create view test_view as

select t.id*10

from test t inner join test2 t2 on t.id=t2.id

Msg 4511, Level 16, State 1, Procedure test_view, Line 2

Create View or Function failed because no column name was specified for column 1.

when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived.

create table test(id int ,name varchar(20))

create table test2(id int ,name varchar(20))

create view test_view as

select t.id, t2.id

from test t inner join test2 t2 on t.id=t2.id

Msg 4506, Level 16, State 1, Procedure test_view, Line 2

Column names in each view or function must be unique. Column name 'id' in view or

function 'test_view' is specified more than once.

Column names can also be assigned in the SELECT statement.

If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

Note: In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the SalesOrderID column in a CREATE VIEW statement, an ALTER VIEW statement can name the SalesOrderID column with a different column name, such as OrderRef, and still have the permissions associated with the view using SalesOrderID.

AS:

Specifies the actions the view is to perform.

Select_statement:

Is the SELECT statement that defines the view. The statement can use more than one table and other views. Appropriate permissions are required to select from the objects referenced in the SELECT clause of the view that is created.

A view does not have to be a simple subset of the rows and columns of one particular table.

A view can be created that uses more than one table or other views with a SELECT clause of any complexity.

The SELECT clauses in a view definition cannot include the following:

Order By:

Order By clause does not work in View. I agree with all of you who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

Here is the quick example for the same. I have used sample database AdventureWorks for the example.

USE AdventureWorks

-- First Run regular query and observe

SELECT *

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderDetailID DESC

-- Create view with same T-SQL Script

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID (N'[dbo].[vw_ViewLimit1]'))

DROP VIEW [dbo].[vw_ViewLimit1] GO

CREATE VIEW vw_ViewLimit1

SELECT *

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderDetailID DESC

Above Query will throw following error

Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions,

unless TOP or FOR XML is also specified.

-- Create view with same T-SQL Script without ORDER BY

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))

DROP VIEW [dbo].[vw_ViewLimit1] GO

CREATE VIEW vw_ViewLimit1

SELECT *

FROM Sales.SalesOrderDetail

-- Use Order by clause outside of the views it will return data in sorted order

-- Create view with same T-SQL Script without ORDER BY

SELECT *

FROM vw_ViewLimit1

ORDER BY SalesOrderDetailID DESC

The above error itself explains how one can use ORDER BY in view. It suggests that if we use ORDER BY with TOP, we can surely use ORDER BY. If we want all the rows of the table, we can use TOP with 100 PERCENT. Let us try to modify our view with the usage of TOP 100 PERCENT and ORDER BY. This does not throw any error.

-- Create view with TOP 100 PERECENT and ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
CREATE VIEW vw_ViewLimit1
AS
SELECT TOP 100 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Select from view
SELECT *
FROM vw_ViewLimit1
GO

However, when you observe the result set, you will notice that table is not ordered DESC, which is specified by SalesOrderDetailID column, as it should be. Let us examine the execution plan. You will not notice that there is no SORT operation at all.

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE:([AdventureWorks2008].[Sales].[SalesOrderDetail].[LineTotal]=[AdventureWorks2008].[Sales].[SalesOrderDetail].[LineTotal]))

|--Compute Scalar(DEFINE:([AdventureWorks2008].[Sales].[SalesOrderDetail].[LineTotal]=isnull((CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2008].[Sales].[SalesOrderDetail].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2008].[S

|--Clustered Index Scan(OBJECT:([AdventureWorks2008].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]))

I have heard many people talking about workaround, where they use some other number less than 100 in the TOP clause. Let us do a small test with 99.99 PERCENT and see the type of result we get.

-- Create view with TOP 99.99 PERECENT and ORDER BY

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))

DROP VIEW [dbo].[vw_ViewLimit1] GO

CREATE VIEW vw_ViewLimit1

SELECT TOP 99.99 PERCENT *

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderDetailID DESC

-- Select from view

SELECT *

FROM vw_ViewLimit1

However, as we are using TOP and 99.99, there is very little chance that we may not get all the rows from the table. Let us check the count of the rows in original table and Views.

-- Match the counts

SELECT COUNT(*) ViewCount

FROM vw_ViewLimit1

SELECT COUNT(*) OriginalCount

FROM Sales.SalesOrderDetail

From the count, it is clear that View has not returned all the rows because of the TOP specified. If table was a small table with less than 10,000 rows, this view might have not missed any rows, but in this case, where there are lots of rows, the View has missed rows.

Summary: It is not advisable to use ORDER BY in Views. Use ORDER BY outside the views. In fact, the correct design will imply the same. If you use TOP along with Views, there is a good chance that View will not return all the rows of the table or will ignore ORDER BY completely.

However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.

I am using sql server 2008. I know that we do not use ORDER BY clause when we create views but i want to know the axact reason behind it??? why we cant??

Because it makes no sense to define a ORDER BY clause in a view. Always the "caller" of a table / view defines the order how he likes to retrieve the result; if a view would have an ORDER BY clause and the caller would like to have the result in a different order, SQL Server would have to order the result twice; and one would be redundant and so it would be inperformat; therefore it makes really no sense.

Because it is absurd. A VIEW is a table. Tables are sets. Sets have no ordering. This concept is covered on the first day of any RDBMS class. Not understanding this like being in a geography class and thinking the Earth is flat.

A reference to a temporary table or a table variable.

select * into #test from test

create view test_view as

select * from #test

Msg 4508, Level 16, State 1, Procedure test_view, Line 2

Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.

The INTO keyword

A view is defined by a SELECT statement that must return a rowset.

The OPTION clause

COMPUTE or COMPUTE BY clauses

Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause.

Functions and multiple SELECT statements separated by UNION or UNION ALL can be used in select_statement.

CHECK OPTION:

Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

Base Table:

select * from test

id name

----------- --------------------

10 sql

20 server

Creating View on table “test”

create view test_view as

select * from test

where id between 5 and 100

with check option;

insert into test_view values(30, 'sql server 2000')

(1 row(s) affected)

insert into test_view values(150, 'sql server 2008')

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

select * from test_view

id name

----------- --------------------

10 sql

20 server

30 sql server 2000

update test_view

set id =350

where id = 30

Msg 550, Level 16, State 1, Line 1

The statement has been terminated.

delete from test_view

where ID=30

(1 row(s) affected)

select * from test_view

id name

----------- --------------------

10 sql

20 server

Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

Update test

set id=250

where Id=20

(1 row(s) affected)

select * from test

id name

----------- --------------------

10 sql

250 server

select * from test_view

id name

----------- --------------------

10 sql

Insert into test values(650,'ms sql 2012')

(1 row(s) affected)

select * from test

id name

----------- --------------------

10 sql

250 server

650 ms sql 2012

select * from test_view

id name

----------- --------------------

10 sql

ENCRYPTION:

Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

CREATE VIEW test

with encryption

SELECT name

FROM sysobjects

The above object have been created and can be seen in SSMS and also can be verified from table sys.sysobjects.

If you try to generate a CREATE or ALTER script for objects that were encrypted using the WITH ENCRYPTION option then you will get the following error, because this task involves viewing and using the object code.

Error Message:

“Property TextHeader is not available for View '[dbo].[test]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)”

Also, if sp_helptext is used to view the definition of any object encrypted using the WITH ENCRYPTION clause then the following error will be generated

“The text for object 'dbo.USP_RedProducts' is encrypted.”

Although using the WITH ENCRYPTION option seems straight forward to hide the definition of any view, stored procedure or function there are two main problems with this option:

1. The definition/code of object will not be available to anyone regardless of their permissions or access level. You can not customize the permissions for code/definition visibility.

2. Once an object has been created on the server using the WITH ENCRYPTION option, there is no standard method provided to get the definition/code back out of the encrypted object. You will need to keep a copy of the code outside of SQL Server.

Keeping in mind the above mentioned complications, using the WITH ENCRYPTION option becomes risky. Code for these objects may be required at a later time for consultation or alteration. So it would be imperative to save the code in some other place where it can safely be accessed later.

Scenario:

Let’s take a scenario up where the database development team has completed development of Tables, Views, Stored Procedures, UDFs, etc. and ready to deploy it to the production environment along with the front-end application. The dev team runs the DB script in production environment and entire production database is ready and up.

But, what if someone, who is having access to the production server, logs in to SQL Server Management Studio, gets into desired database and right click you view and opens up the definition – that is, ALTER VIEW… window. He/she makes modification, which is incorrect and gives false data, and then executes that script. Now, this view is going to give us wrong information, just because someone has tampered with it. So, how to overcome this problem? What’s the remedy for it?

Here comes WITH ENCRYPTION option in CREATE or ALTER VIEW statement. Using WITH ENCRYPTION option, we can encrypt the definition of a view. Once encrypted, no body can retrieve the definition and see it, not even the person who created it or SQL Server Administrator himself. Once encrypted, it is encrypted for everyone and forever.

ALTERNATE TO USING WITH ENCRYPTION CLAUSE

Using WITH ENCRYPTION is not a recommended best practice to hide the definition/code of an object. Luckily, there is an alternative approach for SQL Server.

If it is required to hide the definition/code of any object from a user then standard permissions can be used for this purpose. This can be done by granting or revoking View Definition rights.

If permission View Definition is denied for an object to any user then the user would not be able to view the object in SSMS or view its code through the system stored procedure sp_helptext.

The View Definition permission may also be used for other objects in SQL Server like tables, synonyms etc. View Definition permissions can be granted or denied using both T-SQL or SSMS

Script to deny VIEW DEFINITION permission

-- Syntax to use VIEW DEFINITION Permission

DENY/GRANT/REVOKE VIEW DEFINITION ON OBJECTNAME TO USERNAME

--To deny VIEW DEFINITION permission to User1 on HumanResources.vEmployee

USE AdventureWorks

DENY VIEW DEFINITION ON [HumanResources].[vEmployee] TO User1

Other permissions like SELECT, INSERT etc will remain intact.

The permissions are flexible and can be implemented on the following four levels:

· Server level. At server level this permission will be listed as View Any Definition

· Database level

· Schema level

· Individual entity level

· When taking over any new database make sure that you have backup code for all encrypted objects. The following script can be used to identify encrypted objects in a database.

Script to get list of encrypted objects in SQL Server database

--Get list of encrypted objects in a database

Use DatabaseName

GO SELECT OBJECT_NAME(id) as ObjectName

FROM sys.syscomments WHERE encrypted = 1

SCHEMABINDING:

Binds the view to the schema of the underlying table or tables.

When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified to remove dependencies(Schemabinding) on the table that is to be modified or view must be dropped.

On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.

--View creation with schemabinding

CREATE VIEW test

with schemabinding

SELECT name

FROM Sales.SalesReason

--Traying to change the length of coulmn ‘name’, it will impact view definition

Alter table sales.salesreason alter column name nvarchar(60)

Msg 5074, Level 16, State 1, Line 1

The object 'test' is dependent on column 'name'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN name failed because one or more objects access this column.

--Traying to change the length of coulmn ‘relationtype’, it won’t impact view definition because this olumn not included in view definition.

alter table sales.salesreason alter column reasontype nvarchar(60)

Command(s) completed successfully.

TO ALTER BASE TABLE:

The view definition itself must first be modified to remove dependencies(Schemabinding) on the table that is to be modified or view must be dropped.

--The view definition modified to remove schemabinding

Alter VIEW test

SELECT name

FROM Sales.SalesReason

Command(s) completed successfully.

alter table sales.salesreason alter column name nvarchar(60)

Command(s) completed successfully.

--The view must be dropped

drop view test

Command(s) completed successfully.

alter table sales.salesreason alter column name nvarchar(60)

Command(s) completed successfully.

When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

SCHEMABINDING Scenario 1: From MS sqltips (SQL Server Schema Binding and Indexed Views)

Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".

Solution

A VIEW has no physical existence in the database until it is invoked. You cannot put constraints on a VIEW for that reason.

The error message "Cannot create index on view '*' because the view is not schema bound.(Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.

So below shows what happened when I tried to create an index on the view I was using.

--View creation without schemabinding

CREATE VIEW test

SELECT name

FROM Sales.SalesReason

create nonclustered index ix_name on test(name)

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'test' because the view is not schema bound.

In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.

So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.

Alter VIEW test

with schemabinding

SELECT name

FROM Sales.SalesReason

Be aware that if you do not use the schema name, (sales) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'sales.salesreason' because name 'salesreason' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."

This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.

Now we will check that we are able to create an index on the view.

create nonclustered index ix_name on test(name)

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view 'test'. It does not have a unique clustered index.

An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes.

create unique clustered index cluix_name on test(name)

Command(s) completed successfully.

create nonclustered index ix_name on test(name)

Command(s) completed successfully.

Updatable and Read-Only VIEWs:

Unlike base tables, VIEWs are either updatable or read-only, but not both. INSERT, UPDATE, and DELETE operations are allowed on updatable VIEWs and base tables, subject to other constraints. INSERT, UPDATE, and DELETE are not allowed on read-only VIEWs, but you can change their base tables, as you would expect.

An updatable VIEW is one that can have each of its rows associated with exactly one row in an underlying base table. When the VIEW is changed, the changes pass through the VIEW to that underlying base table unambiguously. Updatable VIEWs in Standard SQL are defined only for queries that meet these criteria.

They are built on only one table

No GROUP BY clause

No HAVING clause

No aggregate functions

No calculated columns

No UNION,UNION ALL, INTERSECT or EXCEPT

No SELECT DISTINCT clause

Any columns excluded from the VIEW must be NULL-able or have a DEFAULT clause in the base table, so that a whole row can be constructed for insertion.

By implication, the VIEW must also contain a key of the table. In short, we are absolutely sure that each row in the VIEW maps back to one and only one row in the base table. The major advantage of this limited definition is that it is based on syntax and not semantics. For example, these VIEWs are logically identical:

CREATE VIEW Foo1 (a, b, ..) -- updatable, has a key!

AS SELECT (a, b, ..)

FROM Foobar

WHERE x IN (1,2);

CREATE VIEW Foo2 (a, b, ..)-- not updateable!

AS SELECT (a, b, ..)

FROM Foobar

WHERE x = 1

UNION ALL

SELECT (a, b, ..)

FROM Foobar

WHERE x = 2;

But Foo1 is updateable and Foo2 is not. While I know of no formal proof, I suspect that determining if a complex query resolves to an updatable query for allowed sets of data values possible in the table is an NP-complete problem.

The INSTEAD OF trigger was the ANSI Standards Committee letting the Data Modeler decide on how to resolve the VIEW updating problem. These triggers are added to a VIEW and are executed on base tables that make up the VIEW. The user never sees them fire and work their magic.

As an example, consider a VIEW that builds the total compensation for each employee by joining the personnel, employee stock holdings, bonuses and salary_amt tables in one VIEW. An INSTEAD OF trigger can update the total compensation using a hidden formula and complex business rules that the user never sees.

The use of INSTEAD OF triggers gives the user the effect of a single table, but there can still be surprises. Think about three tables; A, B and C. Table C is disjoint from the other two. Tables A and B overlap. So I can always insert into C and may or may not be able to insert into A and B if I hit overlapping rows.

Going back to my Y2K consulting days, I ran into a version of such a partition by calendar periods. Their Table C was set up on Fiscal quarters and got leap year wrong because one of the fiscal quarters ended on the last day of February.

Uses:

To restrict access of data from few users

One useful feature of a view is that the end user sees only those columns and

rows to which you wish to allow access.

For example, you want to allow queries against the Authors table but disallow the Contract Status column, you could create a view as shown below.

This view defines which columns to include in the view and keeps the data protected from the user.

Example of Authors view.

CREATE VIEW view_Authors

SELECT au_id,

au_lname,

au_fname,

address,

city,

state,

zip

FROM authors

By allowing users to query the view and not the table, you have in essence implemented a form of column-level security. While Microsoft SQL Server supports column-level security at the table level, using views to enforce access is much more efficient and consumes less overhead and system resources.

Column level permission:

Problem

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data?

Solution:

We can solve this in two ways:

Create a view with only required columns

Define a column level permission

CREATE ROLE HR_Employee;

CREATE ROLE HR_Intern;

CREATE USER SalaryPerson WITHOUT LOGIN;

EXEC sp_addrolemember @membername = 'SalaryPerson', @rolename = 'HR_Employee';

CREATE USER SummerIntern WITHOUT LOGIN;

EXEC sp_addrolemember @membername = 'SummerIntern', @rolename = 'HR_Intern';

This sets up two levels of users: HR Employees (role HR_Employee, of which SalaryPerson is one) and HR Interns (role HR_Intern, played by SummerIntern).

Now, when we normally grant permissions, we do so against the whole object or schema. For instance, this grants SELECT permission against the dbo.Employee table to HR_Employee role members:

GRANT SELECT ON dbo.Employee TO HR_Employee;

Now, we don't want interns to have this level of permissions. We only want them to have access to specific columns. There's a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:

GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;

Therefore, the HR_Intern role cannot query these columns. They can find out that they are there, but they can't retrieve data. If you want to see these permissions in action, execute the following snippets. This should work just fine, because HR_Employees can SELECT against the whole table:

EXECUTE AS USER = 'SalaryPerson';

SELECT * FROM dbo.Employee;

REVERT;

This will fail with a couple of access denied errors, listing the columns the user cannot access:

EXECUTE AS USER = 'SummerIntern';

SELECT * FROM dbo.Employee;

REVERT;

The errors you should see:

Msg 230, Level 14, State 1, Line 2

The SELECT permission was denied on the column 'SSN' of the

object 'Employee", database 'MSSQLTips', schema 'dbo'.

Msg 230, Level 14, State 1, Line 2

The SELECT permission was denied on the column 'Salary' of the

object 'Employee", database 'MSSQLTips', schema 'dbo'.

This will work, because the columns in the query are accessible to HR_Intern:

EXECUTE AS USER = 'SummerIntern';

SELECT EmployeeID, FirstName, SurName FROM dbo.Employee;

REVERT;

And that's how to restrict using column permissions. Incidentally, you can do the same for DENY. Therefore, if a group of users already have access to columns they shouldn't, and you can't rework security in this manner, you could use DENY if you had to, like so:

DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;

Since DENY trumps any other permissions, this will effectively block access to those columns. This should be used as a last resort, obviously, because the use of DENY is not intuitive. And DENY at the column level is another step removed from what we're used to when looking at permissions.

To Focus on Specific Data

Views let users focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary or sensitive data can be left out of the view.

For example, a view vBikes in the AdventureWorks2008R2 sample database would let a user see the names of all bicycles that are currently in stock. The view filters out all fields from the Product table except Name, and returns only names of finished bicycles instead of bicycle components.

To Simplify Data Manipulation

Views can simplify how users work with data. You can define frequently used joins, projections, UNION queries, and SELECT queries as views so that users do not have to specify all the conditions and qualifications every time an additional operation is performed on that data.

For example, a complex query that is used for reporting purposes and performs subqueries, outer joins, and aggregation to retrieve data from a group of tables can be created as a view. The view simplifies access to the data because the underlying query does not have to be written or submitted every time the report is generated; the view is queried instead. For more information about manipulating data, see Query Fundamentals.

To Provide Backward Compatibility

Views enable you to create a backward compatible interface for a table when its schema changes. For example, an application may have referenced a nonnormalized table that has the following schema:

Employee(Name, BirthDate, Salary, Department, BuildingName)

To avoid redundantly storing data in the database, you could decide to normalize the table by splitting it into the following two tables:

Employee2(Name, BirthDate, Salary, DeptId)

Department(DeptId, BuildingName)

To provide a backward-compatible interface that still references data from Employee, you can drop the old Employee table and replace it by the following view:

CREATE VIEW Employee AS

SELECT Name, BirthDate, Salary, BuildingName

FROM Employee2 e, Department d

WHERE e.DeptId = d.DeptId

Applications that used to query the Employee table can now to obtain their data from the Employee view. The application does not have to be changed if it only reads from Employee. Applications that update Employee can sometimes also be supported by adding INSTEAD OF triggers to the new view to map INSERT, DELETE, and UPDATE operations on the view to the underlying tables. For more information, see Designing INSTEAD OF Triggers.

To Customize Data:

Views let different users to see data in different ways, even when they are using the same data at the same time. This is especially useful when users who have many different interests and skill levels share the same database. For example, a view can be created that retrieves only the data for the customers with whom an account manager deals. The view can determine which data to retrieve based on the login ID of the account manager who uses the view.

To Export and Import Data:

Views can be used to export data to other applications. For example, you may want to use the Customer and SalesOrderHeader tables in the AdventureWorks2008R2 database to analyze sales data using Microsoft Excel. To do this, you can create a view based on the Customer and SalesOrderHeader tables. You can then use the bcp utility to export the data defined by the view. Data can also be imported into certain views from data files by using the bcp utility or BULK INSERT statement providing that rows can be inserted into the view using the INSERT statement. For more information about the restrictions for copying data into views, see INSERT (Transact-SQL).

Remarks

A view can be created only in the current database.

The CREATE VIEW must be the first statement in a query batch.

A view can have a maximum of 1,024 columns.

When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table or tables.

create view tes2 as

select * from tablenotexists

Msg 208, Level 16, State 1, Procedure tes2, Line 2

Invalid object name 'tablenotexists'.

If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed.

Indexed Views:

For many years, Microsoft SQL Server has supported the ability to create virtual tables known as views. Historically, these views served these main purposes:

To provide a security mechanism that restricts users to a certain subset of data in one or more base tables.
To provide a mechanism that allows developers to customize how users can logically view the data stored in base tables.

With SQL Server 2000, the functionality of SQL Server views was expanded to provide system performance benefits. It is possible to create a unique clustered index on a view, as well as non clustered indexes, to improve data access performance on the most complex queries by precomputing and materializing the view. This is often particularly effective for aggregate views in decision support or data warehouse environments. In SQL Server, a view that has a unique clustered index is referred to as an indexed view.

The discussion in this paper applies to SQL Server 2005 and SQL Server 2008. In SQL Server 2005, certain operations on partitioned tables with indexed views required dropping the index and then re-creating the index on the view. In SQL Server 2008, the need to drop an indexed view on a partitioned table during common maintenance operations is greatly reduced, so indexed views are more easily maintained over large partitioned tables.

From the database management system (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for executing the query. The query optimizer searches though a set of possible execution plans for a query, and it chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

In the case of a non indexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view [Note1] . After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

Note: Indexed views are a feature of all versions of SQL Server 2000 and SQL Server 2005. In the Developer and Enterprise editions of SQL Server 2000 and SQL Server 2005, the query processor can use an indexed view to solve queries that structurally match the view, even if they don't refer to the view by name. In other versions, you must reference the view by name and use the NOEXPAND hint on the view reference to query the contents of an indexed view.

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:

Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
Tables can be pre joined and the resulting data set stored.
Combinations of joins or aggregations can be stored.

Secondary, nonclustered indexes on views can provide additional query performance. Similar to nonclustered indexes on tables, nonclustered indexes on views may provide more options for the query optimizer to choose from during the compilation process. For example, if the query includes columns not covered by the clustered index, the optimizer can choose one or more secondary indexes in the plan and avoid a time-consuming full scan of the indexed view or base tables.

Adding indexes to the schema increases the overhead on the database because the indexes will require ongoing maintenance. Careful consideration should be given to finding the right balance of indexes and maintenance overhead.

Analyze your database workload before implementing indexed views. Use your knowledge of the queries as well as various tools (for example, SQL Server Profiler) to identify the queries that can benefit from indexed views. Frequently occurring aggregations and joins are the best candidates for indexed views. Whether or not a query is asked frequently, it may be a candidate for an indexed view if it takes significant time to answer, and the value of getting the answer quickly is high. For example, some developers find it useful to create indexed views that precompute and store the answers to queries for reports run at the end of each month by senior executives.

Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. However, when indexed views are used, they can provide significant improvements (by orders of magnitude) in data access. This is because the query optimizer uses the precomputed results stored in the indexed view, which substantially reduces the cost of the query execution.

The query optimizer considers indexed views only for queries with nontrivial cost. This avoids situations where trying to match various indexed views during the query optimization costs more than the savings achieved by the indexed view usage. Indexed views are rarely used in queries with a cost of less than 1.

Applications that benefit from the implementation of indexed views include:

Decision support workloads.
Data marts.
Data warehouses.
Online analytical processing (OLAP) stores and sources.
Data mining workloads.

From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

Joins and aggregations of large tables.
Repeated patterns of queries.
Repeated aggregations on the same or overlapping sets of columns.
Repeated joins of the same tables on the same keys.
Combinations of the above.

On the contrary, online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.

The query optimizer considers several conditions to determine if an indexed view can cover the entire query or a portion of it. These conditions correspond to a single FROM clause in the query and consist of the following:

The tables in the query FROM clause must be a superset of the tables in the indexed view FROM clause.
The join conditions in the query must be a superset of the join conditions in the view.
The aggregate columns in the query must be derivable from a subset of the aggregate columns in the view.
All expressions in the query select list must be derivable from the view select list or from the tables not included in the view definition.
One predicate subsumes another if it matches a superset of the rows matched by the other. For example, "T.a=10" subsumes "T.a=10 and T.b=20." Any predicate subsumes itself. The part of the predicate of the view that restricts values of one table must subsume the part of the predicate of the query that restricts the same table. Furthermore, it must do so in a way that SQL Server can verify.

All columns in the query search condition predicates that belong to tables in the view definition must appear in one or more of the following in the view definition:

A GROUP BY list.
The view select list if there is no GROUP BY.
The same or equivalent predicate in the view definition.

Cases (1) and (2) allow SQL Server to apply a query predicate to rows from the view to further restrict the rows of the view. Number (3) is a special case where no filtering is needed on the column, so the column needn't appear in the view.

If the query contains more than one FROM clause (subqueries, derived tables, UNION), the optimizer may select several indexed views to process the query, and apply them to different FROM clauses. [Note2]

Example queries demonstrating these conditions are presented at the end of this document. Allowing the query optimizer to determine which indexes, if any, to use in the query execution plan is the recommended best practice.

Using the NOEXPAND View Hint

When SQL Server processes queries that refer to views by name, the definitions of the views normally are expanded until they refer only to base tables. This process is called view expansion. It's a form of macro expansion.

The NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index. It prevents view expansion. The NOEXPAND hint can only be applied if the indexed view is referenced directly in the FROM clause. For example, the following statement includes the indexed view View1 in the FROM clause.

Transact-SQL

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...

Use NOEXPAND if you want to be sure to have SQL Server process a query by reading the view itself instead of reading data from the base tables. If for some reason SQL Server chooses a query plan that processes the query against base tables when you'd prefer that it use the view, consider using NOEXPAND. You must use NOEXPAND in all versions of SQL Server other than Developer and Enterprise editions to have SQL Server process a query against an indexed view directly. You can see a graphical representation of the plan SQL Server chooses for a statement using the SQL Server Management Studio tool Display Estimated Execution Plan feature. Alternatively, you can see different nongraphical representations using SHOWPLAN_ALL, SHOWPLAN_TEXT, or SHOWPLAN_XML. See SQL Server Books Online for a discussion of the different versions of SHOWPLAN.

When processing a query that refers to a view by name, SQL Server always expands the views, unless you add the NOEXPAND hint to the view reference. It attempts to match indexed views to the expanded query, unless you specify the EXPAND VIEWS query hint in an OPTION clause at the end of the query. For example, suppose there is an indexed view View1 in the database. In the following query, View1 is expanded based on its logical definition (its CREATE VIEW statement), and then the EXPAND VIEWS option prevents the indexed view for View1 from being used in the plan to solve the query.

Transact-SQL

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...

OPTION (EXPAND VIEWS)

Use EXPAND VIEWS if you want to be sure to have SQL Server process a query by accessing data directly from the base tables referenced by the query, instead of possibly accessing indexed views. EXPAND views may in some cases help eliminate lock contention that could be experienced with an indexed view. Both NOEXPAND and EXPAND VIEWS can help you evaluate performance with and without use of indexed views when you test your application.

SQL Server 2005 introduced many improvements for indexed views. Starting with SQL Server 2005, the set of indexable views includes those based on:

Scalar aggregates, including SUM and COUNT_BIG without GROUP BY.
Scalar expressions and user-defined functions. For example, given a table T(a int, b int, c int) and a scalar user-defined function dbo.MyUDF(@x int), an indexed view defined on T can contain a computed column such as a+b or dbo.MyUDF(a).
Persisted imprecise columns. An imprecise column is one whose type is float or real, or a computed column that is derived from a float or real column. In SQL Server 2000, an imprecise column could be used in the select list of an indexed view if it was not part of the index key. An imprecise column could not be used elsewhere inside the view definition either, such as in the WHERE or FROM clauses. SQL Server allows an imprecise column to participate in the key or inside the view definition if the column is persisted in the base table.

Persisted columns include regular columns and computed columns marked PERSISTED. The fundamental reason that imprecise, nonpersisted columns can't participate in indexes or indexed views is that it is necessary to be able to detach a database from one computer and attach it to another. After the move, all computed column values stored in indexes or indexed views must be derivable in exactly the same way on the new hardware as on the old hardware, down to the individual bit. Otherwise, these indexed views are logically corrupted with respect to the new hardware. Because of this corruption, on the new hardware, queries to the indexed views could return different answers depending on whether the plan used the indexed view or the base tables to derive the view data. Furthermore, the indexed views couldn't be maintained correctly on the new computer.

Unfortunately, floating point hardware on different computers (even with the same processor architecture from the same manufacturer) does not always stay 100% the same from version to version of the processor. A firmware upgrade might mean that (a*b) on the new hardware is not equal to (a*b) on the old hardware, for some floating point values a and b. For example, the results might be very close, but differ in the least significant bit. Persisting the imprecise computed values before indexing them solves this detach/attach inconsistency problem since all expressions are evaluated on the same computer during database update and maintenance of indexes and indexed views.

Consider the following points when you plan indexed views:

Additional storage is required in the database for the indexed view. The result set of an indexed view is physically persisted in the database in a manner similar to that of typical table storage.
SQL Server maintains views automatically; therefore, any changes to a base table on which a view is defined may initiate one or more changes in the view indexes. Thus, additional maintenance overhead is incurred.

The net performance improvement achieved by a view is the difference of the total query execution savings offered by the view and the cost to store and maintain the view.

It is relatively easy to approximate the required storage the view will consume. Evaluate the SELECT statement encapsulated by the view definition with the SQL Server Management Studio execution plan option Display Estimated Execution Plan. This graphical display will yield an approximation of the number of rows returned by the query and the size of the row. By multiplying these two values together, it is possible to approximate the potential size of the view; however, this is only an approximation. The actual size of the index on the view can be accurately determined only by executing the query in the view definition, or by creating the index on the view.

From the standpoint of automated maintenance considerations performed by SQL Server, the Display Estimated Execution Plan option may give some insight on the impact of this overhead. If a statement that modifies the view (UPDATE on the view, INSERT into a base table) is evaluated with SQL Server Management Studio, an execution plan displayed for the statement will include the maintenance operation for that statement. Taking this cost into consideration along with an idea of how many times this operation will occur in the production environment may indicate the potential cost of view maintenance.

As a general recommendation, any modifications or updates to the view or the base tables underlying the view should be performed in batches if possible, rather than singleton operations. This may reduce some overhead in the view maintenance.

The steps required to create an indexed view are critical to the successful implementation of the view:

Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view.
Verify ANSI_NULLS is set correctly for the current session while creating any new tables.

Base tables referenced by the view must have the correct value of the SET option ANSI_NULLS set at the time the table is created. The OBJECTPROPERTY function can be used to check the value of ANSI_NULLS on an existing table.

set ansi_nulls off

create table test3(id float)

Command(s) completed successfully.

set ansi_nulls on

set quoted_identifier on

create view v_test3

with schemabinding

select id from dbo.test3

Command(s) completed successfully.

SET NOCOUNT ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

create unique clustered index v_test3_ind on v_test3(id)

Msg 1935, Level 16, State 1, Line 10

Cannot create index. Object 'test3' was created with the following SET options off: 'ANSI_NULLS'.

Create the view using the WITH SCHEMABINDING option.

When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified to remove dependencies (Schema binding) on the table that is to be modified or view must be dropped.

Must include the two-part names.

Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the view.

The SET options ANSI_NULLS and QUOTED_IDENTIFIER of the current session must both be set to ON at the time a view on which you wish to build an index is created. This is because these two options are stored with the view definition in the system catalogs.

Use the OBJECTPROPERTY function to check the value of ANSI_NULLS and QUOTED_IDENTIFIER on an existing table or view.

When Settings are off while creating view:

set ansi_nulls off

set quoted_identifier off

alter view v_test3

with schemabinding

select id from dbo.test3

select sv.name,sm. uses_ansi_nulls,uses_quoted_identifier

from sys.views sv

inner join sys.sql_modules sm on sv.object_id = sm.object_id

where sv.name = 'v_test3'

name uses_ansi_nulls uses_quoted_identifier

v_test3 0 0

SET NOCOUNT ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

create unique clustered index v_test3_ind on v_test3(id)

Msg 1935, Level 16, State 1, Line 9

Cannot create index. Object 'v_test3' was created with the following SET options off: 'ANSI_NULLS, QUOTED_IDENTIFIER'.

When Settings are off while creating view:

set ansi_nulls on

set quoted_identifier on

alter view v_test3

with schemabinding

select id from dbo.test3

select sv.name,sm. uses_ansi_nulls,uses_quoted_identifier

from sys.views sv

inner join sys.sql_modules sm on sv.object_id = sm.object_id

where sv.name = 'v_test3'

name uses_ansi_nulls uses_quoted_identifier

v_test3 1 1

SET NOCOUNT ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

create unique clustered index v_test3_ind on v_test3(id)

Command(s) completed successfully.

Verify the view definition is deterministic.

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed. For more information, see “Deterministic and Nondeterministic Functions” in SQL Server Books Online.

set ansi_nulls on

set quoted_identifier on

create view v_test3

with schemabinding

select id , getdate() as dates from dbo.test3

SET NOCOUNT ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

create unique clustered index v_test3_ind on v_test3(id)

Msg 1949, Level 16, State 1, Line 9

Cannot create index on view 'AdventureWorks2008.dbo.v_test3'. The function 'getdate' yields nondeterministic results.

Use a deterministic system function, or modify the user-defined function to return deterministic results.

Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity in SQL Server when moving a database from one computer to another, such expressions can participate only as non-key columns of indexed views. Deterministic expressions that do not contain float expressions are called precise. Only deterministic expressions that are persisted and/or precise may participate in key columns and WHERE or GROUP BY clauses of indexed views. Persisted expressions are references to stored columns, including regular columns and computed columns marked PERSISTED.

Use the COLUMNPROPERTY function and Is Deterministic property to determine if a view column is deterministic. Use the COLUMNPROPERTY function and IsPrecise property to determine if a deterministic column in a view with SCHEMABINDING is precise. COLUMNPROPERTY returns 1 if the property is TRUE, 0 if FALSE, and NULL for invalid input. For example, in this script the SELECT returns 0 for IsPrecise because the b column is of type real.

Transact-SQL

CREATE TABLE T(a int, b real, c as getdate(), d as a+b)

CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b, c, d FROM dbo.T

SELECT object_id ('VT'), COLUMNPROPERTY(object_id('VT'),'b','IsPrecise')

create unique clustered index vt_ind on VT(d) or

create unique clustered index vt_ind on VT(b)

Msg 1901, Level 16, State 1, Line 10

Cannot create index or statistics 'vt_ind' on view 'VT' because key column 'd' is imprecise, computed and not persisted.

Consider removing reference to column in view index or statistics key or changing column to be precise.

If column is computed in base table consider marking it PERSISTED there.

Verify your session's SET options are set correctly as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the unique clustered index on the view.

Evaluating the same expression can produce different results in SQL Server if different SET options are enabled for the current session when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. But after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'. Indexed views require fixed values for several SET options for the current session and for objects referenced by the view to ensure that the views can be maintained correctly and return consistent results.

The ARITHABORT option does have to be ON for the current session to create an indexed view, but it is implicitly ON in SQL Server once ANSI_WARNINGS is ON, so it does not need to be set explicitly. If you are using a .NET SqlClient, OLE DB, or ODBC server connection, you do not have to modify any SET options from their defaults to create, use, and maintain indexed views. All DB LIB values must be set correctly either at the server level using sp_configure or from the application using the SET command. For more information about SET options, see “Using Options in SQL Server” in SQL Server Books Online.

Create the unique clustered index on the view.

The SET options of the current session must be set to the values shown in the required value column for the current session whenever these operations occur:

An index is created on a view.
There is any INSERT, UPDATE, or DELETE operation performed on any table participating in the indexed view.
The indexed view is used by the query optimizer to produce the query plan.

If GROUP BY is present, the VIEW definition:

Must contain COUNT_BIG(*).

Must not contain HAVING, CUBE, ROLLUP, or GROUPING().

These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))

DROP VIEW [dbo].[SampleView] GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[mySampleTable] GO

-- Create SampleTable

CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))

INSERT INTO mySampleTable (ID1,ID2,SomeData)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),

ROW_NUMBER() OVER (ORDER BY o2.name),

o2.name

FROM sys.all_objects o1

CROSS JOIN sys.all_objects o2

-- Create View

CREATE VIEW SampleView

WITH SCHEMABINDING

SELECT COUNT(*) TableCount, ID2

FROM dbo.mySampleTable

GROUP BY ID2

-- Create Index on View

CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView] (

ID2 ASC

)

/* Above statement will thrown an error

Msg 10136, Level 16, State 1, Line 1

Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.

-- Aleter View to replace COUNT with BIG_COUNT

ALTER VIEW SampleView

WITH SCHEMABINDING

SELECT COUNT_BIG(*) TableCount, ID2

FROM dbo.mySampleTable

GROUP BY ID2

-- Now let us create Index again - this time successfully

CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView] (

ID2 ASC

)

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

View Over the View Not Possible with Index View

The reason for this is that another view over a view is difficult to maintain. The workaround is very simple as explained in the error messages itself. Instead of creating nesting View, just bring over the code of the inner view to the outer view. After this, it will work just fine, letting you create an index

SELF JOIN Not Allowed in Indexed View

The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.

Outer Join Not Allowed in Indexed Views

Rows can logically disappear from an Indexed View based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement. In addition, the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

Cross Database Queries Not Allowed in Indexed View

One of the requirements of Indexed View is that it has to be created ‘WITH SCHEMABINDING’. If the View is not created with that clause, it would not let you create an index on that View. Moreover, if you try to create a View with schemabinding, it would not allow you to create the database.

-- Create DB

USE MASTER

GO

CREATE DATABASE TEST1

CREATE DATABASE TEST2

GO

-- Table1

USE Test1

GO

CREATE TABLE TABLE1 (ID INT)

GO

USE Test2

GO

-- Table2

CREATE TABLE TABLE2 (ID INT)

GO

USE Test1

GO

-- Create View

CREATE VIEW CrossDBView

WITH SCHEMABINDING

AS

SELECT t1.ID AS t1id, t2.ID AS t2id

FROM Test1.dbo.Table1 t1

INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.ID

GO

/*

Error:

Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4

Cannot schema bind view 'CrossDBView' because name 'Test1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

*/

UNION Not Allowed but OR Allowed in Index View

SELECT * and Adding Column Issue in View

Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed

The resolutions of these issues are as follows:

Refresh the views using sp_refreshview stored procedure

Do not use SELECT * but use SELECT column names

Create view with SCHEMABINDING; this way, the underlying table will not get modified.

Adding Column is Expensive by Joining Table Outside View

Let’s see another reason why I do not like Views. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, we will have to modify them first. This means any query that does not need this column will start having the column’s data additionally. This will lead to added network traffic as well as it will reduce the performance of the part where the View is used. This further leads to a conclusion: it may not be good idea to alter the View in order to add an additional column if the View happens to be used at multiple places. An alternative solution would be adding the column outside the View. However, this solution can be very expensive.

USE AdventureWorks

GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))

DROP VIEW [dbo].[vw_ViewLimit1] GO

-- Create View on sample tables

CREATE VIEW vw_ViewLimit1

AS

SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod

INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID

GO

Let us assume that we have to retrieve one more row from the table used in the View. As explained in the first paragraph, altering the View by adding a column to that View may actually lead to unnecessary data for the query using the View, but not looking for that column. The natural alternative solution to this is to use JOIN and add the column to the Views. In the case of T-SQL, we would not have to do the same since we will just go ahead and add the column to the statement.

/* Now let us try to retrieve the column which is not in View */

/* When you compare the performance you will notice View is more expensive*/

-- View with extra column

SELECT v1.*

,th.[Quantity] FROM vw_ViewLimit1 v1

INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID

WHERE SalesOrderDetailID > 111111

-- Select statement with extra column

SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] ,th.[Quantity] FROM Sales.SalesOrderDetail sod

INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID

WHERE SalesOrderDetailID > 111111

We can now check the performance of both queries using the execution plan. This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

The following table lists the SET options.

SET OPTIONS	REQUIRED VALUE	DEFAULT SERVER VALUE	DB LIB VALUE
ANSI_NULLS	ON	OFF	OFF
ANSI_PADDING	ON	OFF	OFF
ANSI_WARNINGS	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	OFF	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	OFF	OFF

User-defined functions referenced by the view must be created using the WITH SCHEMABINDING option.

The view must meet the following requirements:

The view must be created using the WITH SCHEMABINDING option.

Tables must be referenced by the view using two-part names (schemaname.tablename).

User-defined functions must be referenced by the view using two-part names (schemaname.functionname).

SET options ANSI_NULLS and QUOTED_IDENTIFIER must be set correctly.

To create an index on a view in SQL Server, the view definition must not contain any of the following:

ANY, NOT ANY	OPENROWSET, OPENQUERY, OPENDATASOURCE
Arithmetic on imprecise (float, real) values	OPENXML
COMPUTE, COMPUTE BY	ORDER BY
CONVERT producing an imprecise result	OUTER join
COUNT(*)	References to a base table with a disabled clustered index
GROUP BY ALL	References to a table or function in a different database
Derived tables (subquery in FROM list)	References to another view
DISTINCT	ROWSET functions
EXISTS, NOT EXISTS	Self-joins
Expressions on aggregate results (for example, SUM(x)+SUM(x))	STDEV, STDEVP, VAR, VARP, AVG
Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)	Subqueries
Imprecise constants (for example, 2.34e5)	SUM on nullable expressions
Inline or table-valued functions	Table hints (for example, NOLOCK)
MIN, MAX	text, ntext, image, file stream, or xml columns
Nondeterministic expressions	TOP
Non-Unicode collations	UNION
Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and ...)

The index must meet the following requirements:

The user executing the CREATE INDEX statement must be the view owner.

If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

The index must not be created with the IGNORE_DUP_KEY option enabled.

The examples in this section illustrate the use of indexed views with two major groups of queries: aggregations and joins. They also demonstrate the conditions used by the query optimizer when determining if an indexed view is applicable. For information, including a complete list of conditions, see "How the Query Optimizer Uses Indexed Views."

The queries are based on tables in AdventureWorks, the sample database provided in SQL Server 2005, and AdventureWorksDW2008. For SQL Server 2008, the sample databases AdventureWorks and AdventureWorksDWare available as separate downloads from CodePlex at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040. The queries can be executed as written.

You may want to use the Display Estimated Execution Plan execution plan option in SQL Server Management Studio to view the plans selected by the query optimizer before and after the views are created. Although the examples demonstrate how the optimizer chooses the lower cost execution plan, the sample databases are too small to show performance gains.

Before you begin working on these examples, make sure your session has the correct options set by running these commands.

Jes Borland:

Nobody sets out to write overly complex queries. Unfortunately, however, applications grow more complex as the users demand new features, and so the accompanying queries grow more complex also. We don’t always have time to rewrite a query completely, or may not even know a better way to write it.

Standard SQL Server views can help. When we encapsulate complex multi-table query logic in a view, any application that needs that data is then able to issue a much simpler query against the view, rather than a complex multi-JOIN query against the underlying tables. Views bring other advantages too. We can grant users SELECT permissions on the view, rather than the underlying tables, and use the view to restrict the columns and rows that are accessible to the user. We can use views to aggregate data in a meaningful way.

Let’s say we need to run various queries against the AdventureWorks2012 database to return information regarding items that customers have purchased. The query in Listing 1 joins five tables to get information such as the client name, the order number and date, the products and quantities ordered.

SELECT CUST.CustomerID ,

PER.FirstName ,

PER.LastName ,

SOH.SalesOrderID ,

SOH.OrderDate ,

SOH.[Status] ,

SOD.ProductID ,

PROD.Name ,

SOD.OrderQty

FROM Sales.SalesOrderHeader SOH

INNER JOIN Sales.SalesOrderDetail SOD

ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product PROD

ON PROD.ProductID = SOD.ProductID

INNER JOIN Sales.Customer CUST

ON SOH.CustomerID = CUST.CustomerID

INNER JOIN Person.Person PER

ON PER.BusinessEntityID = CUST.PersonID;

Listing: 1

Notice that we use two-part naming for all tables. Not only is this a good practice, it’s also a requirement when creating an indexed view (we’ll discuss further requirements as we progress). Let’s assume that many applications need to run queries like this, joining the same tables, and referencing the same columns in various combinations. To make it easier for our application to consume this data, we can create a view.

Create a View

Listing 2 creates a view based on our query definition, as shown in Listing 2.

CREATE VIEW Sales.vCustomerOrders

WITH SCHEMABINDING

Listing 2

Note that the WITH SCHEMABINDING option is included here and is a requirement for creating an index on the view, which we’ll want to do shortly. This option stipulates that we cannot delete any of the base tables for the view, or ALTER any of the columns in those tables. In order to make one of these changes, we would have to drop the view, change the table, and then recreate the view (and any indexes on the view).

Now, each application simply has to run a much simpler query referencing the view, as shown in Listing 3.

SELECT CustomerID ,

FirstName ,

LastName ,

SalesOrderID ,

OrderDate ,

Status ,

ProductID ,

Name ,

OrderQty

FROM Sales.vCustomerOrders CO;

Listing 3

However, when looking at the execution plan (Figure 1) we can see that SQL Server still performs index scans against each of the five underlying tables

Figure 1

Likewise, the STATISTICS IO output (Figure 2) shows that SQL Server performed 2,172 logical reads against the five base tables.

Figure 2

The execution plan reports the query cost as 6.01323, as shown in Figure 3.

1938-3b%20-%20query%20view%20cost-d74f3b

Figure 3

We see the exact same execution plan, STATISTICS IO output, and query cost if we run the query in Listing 1 again.

Although the use of the view made writing the query easier, it had no impact on query performance. A simple view is just a virtual table, generated from a saved query. It does not have its own physical page structure to use, so it reads the pages of its underlying tables. In other words, when we query a simple view, the optimizer still has to access all of the underlying tables and perform the necessary JOINs and aggregations. It derives cardinality estimations, and hence the query plan, from statistics associated with those tables.

Let’s see what happens, however, if we turn our standard view into an indexed view.

Create a Unique, Clustered Index

Before we start, I should mention that there are a host of requirements attached to the creation of indexed views, in any SQL Server Edition. We’ll discuss these in more detail in the Indexed View Requirements section, but if you have trouble creating an index on a view, it’s likely you’re breaking one of the rules.

In order to turn our normal Sales.vCustomerOrders view into an indexed view, we need to add a unique clustered index, as shown in Listing 4.

CREATE UNIQUE CLUSTERED INDEX CIX_vCustomerOrders

ON Sales.vCustomerOrders(CustomerID, SalesOrderID, ProductID);

Listing 4

When we add a unique clustered index to a view, we ‘materialize’ it. In other words, the ‘virtual table’ persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre-computed, and any joins pre-joined, so the engine no longer has to do this work at execution time. SQL Server creates statistics for the indexed view, different from those of the underlying tables, to optimize cardinality estimations.

A well-crafted indexed view can write fewer pages to disk than the underlying tables, meaning fewer pages queries need to read fewer pages to return results. This means faster, more efficient queries. Use the techniques and tips in this article to ensure your views are optimal!

Let’s see the impact of our indexed view on query performance. These examples assume you’re running SQL Server Enterprise Edition, which will automatically consider indexes on a view when creating a query execution plan, whereas SQL Server Standard Edition won’t; you’ll need to use the WITH (NOEXPAND) table hint directly in the FROM clause of any query you wish to use the view (more on this shortly).

When we re-run the query from Listing 3, we get the same result set, but the execution plan, shown in Figure 4, looks very different. Rather than several index scans with joins, the optimizer now determines that the optimal way to satisfy the query is to scan the clustered index of our view.

Figure 4

The optimizer now reads all the pages required from one index, rather than five, and STATISTICS IO output reveals that this results in a 27% reduction in the number of logical reads the engine must perform in order to return the data, from 2,172 to 1,590.

Figure 5

The overall query cost falls to 1.30858, as seen in Figure 6.

1938-7%20-%20query%20view%20cost-60fb50e

Figure 6

It’s not only queries that reference the view directly that will benefit in this way. Any query that the Optimizer determines the view could satisfy can use the indexed view rather than underlying tables, a process termed view matching. Try re-running Listing 1, which references the base tables rather than our indexed view. The Optimizer determines that the view’s index is the optimal way to retrieve the data and the execution plan will be identical to that in Figure 4.

In Listing 5, we access the same base tables but also perform some aggregations.

SELECT CUST.CustomerID ,

SOH.SalesOrderID ,

SOH.OrderDate ,

SOD.ProductID ,

PROD.Name ,

SUM(SOD.OrderQty) AS TotalSpent

FROM Sales.SalesOrderHeader SOH

INNER JOIN Sales.SalesOrderDetail SOD

ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product PROD

ON PROD.ProductID = SOD.ProductID

INNER JOIN Sales.Customer CUST

ON SOH.CustomerID = CUST.CustomerID

INNER JOIN Person.Person PER

ON PER.BusinessEntityID = CUST.PersonID

GROUP BY CUST.CustomerID ,

SOH.SalesOrderID ,

SOH.OrderDate ,

SOD.ProductID ,

PROD.Name;

Listing 5

Here, the execution plan shows that the Optimizer chose to use the clustered index on the view, rather than indexes on the base tables.

Figure 7

This execution plan shows a yellow exclamation point over the clustered index scan, which is warning us of “Columns with no statistics”. We’ll discuss this in more detail shortly.

1938-1-98ce757e-71ba-42ab-b4f3-f91812469

Figure 8

Aggregating Data with Indexed Views

Indexed views can really come into their own when we have many applications that need to perform complex aggregations, and other calculations, on the same set of base tables. Rather than force SQL Server to perform these aggregations and calculations every time, upon query execution, we can encapsulate them in an indexed view. This can significantly reduce the amount of IO SQL Server must perform to retrieve the necessary data, and CPU time required to perform the calculations, and so can provide tremendous performance boosts.

Before we dive into another example, it’s worth mentioning again that, despite the potential performance benefits, caution is required when implementing an indexed view unless the base tables are relatively static. We’ll discuss this in more detail shortly.

Consider the query in Listing 6.

SELECT CUST.CustomerID ,

SOH.SalesOrderID ,

SOD.ProductID ,

SUM(SOD.OrderQty) AS TotalOrderQty ,

SUM(LineTotal) AS TotalValue

FROM Sales.SalesOrderHeader SOH

INNER JOIN Sales.SalesOrderDetail SOD

ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product PROD ON PROD.ProductID = SOD.ProductID

INNER JOIN Sales.Customer CUST ON SOH.CustomerID = CUST.CustomerID

INNER JOIN Person.Person PER ON PER.BusinessEntityID = CUST.PersonID

GROUP BY CUST.CustomerID ,

SOH.SalesOrderID ,

SOD.ProductID;

Listing 6

This query produces an execution plan with several index scans and joins, shown in Figure 9. It also requires aggregation. Its execution plan is similar in nature to the one we saw in Figure 4, but with additional operations and a higher cost, of 7.62038.

1938-9%20-%20query%20with%20aggregation-

Figure 9

The logical reads are high as well, spanning several tables, as seen in Figure 10.

(121317 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 58, physical reads 1, read-ahead reads 63, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customer'. Scan count 1, logical reads 123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 67, physical reads 1, read-ahead reads 65, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Figure 10

Listing 7 creates an indexed view, vSalesSummaryCustomerProduct, to help reduce the cost of this and similar queries.

CREATE VIEW Sales.vSalesSummaryCustomerProduct

WITH SCHEMABINDING

SELECT CUST.CustomerID ,

SOH.SalesOrderID ,

SOD.ProductID ,

SUM(SOD.OrderQty) AS TotalOrderQty ,

SUM(LineTotal) AS TotalValue ,

COUNT_BIG(*) AS CountLines

FROM Sales.SalesOrderHeader SOH

INNER JOIN Sales.SalesOrderDetail SOD

ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product PROD

ON PROD.ProductID = SOD.ProductID

INNER JOIN Sales.Customer CUST

ON SOH.CustomerID = CUST.CustomerID

INNER JOIN Person.Person PER

ON PER.BusinessEntityID = CUST.PersonID

GROUP BY CUST.CustomerID ,

SOH.SalesOrderID ,

SOD.ProductID;

CREATE UNIQUE CLUSTERED INDEX CX_vSalesSummaryCustomerProduct

ON Sales.vSalesSummaryCustomerProduct(CustomerID, SalesOrderID, ProductID);

Listing 7

Note the use of COUNT_ BIG( *) in this view, a requirement for indexed views that have a GROUP BY. It is there for the internal maintenance of indexed views – it maintains a count of the rows per group in the indexed view.

Now we can return the same result set by running the simple query in Listing 8.

SELECT CustomerID ,

SalesOrderID ,

TotalOrderQty ,

TotalValue

FROM Sales.vSalesSummaryCustomerProduct;

Listing 8

Figure 11 shows that we’ve reduced the query cost from 7.62038 to 0.694508. If we check the STATISTICS IO output, we’ll also find a substantial reduction in the number logical reads, from 1,498 across five indexes to 758.

1938-10%20-%20view%20aggregated-1400d952

Figure 11

Again, notice the yellow exclamation mark; hovering over the index scan icon reveals that it is a “Columns with no statistics” warning on the SalesOrderID column, the second column in the clustered index key. We can see that SQL Server has created a statistics object for this clustered index, as shown in Figure 12.

1938-1-744e48f0-8481-4e81-87b5-94ac60314

Figure 12

However, if we run the query using the WITH (NOEXPAND) hint, as shown in Figure 14, we will no longer see the warning.

SELECT CustomerID ,

SalesOrderID ,

TotalOrderQty ,

TotalValue

FROM Sales.vSalesSummaryCustomerProduct WITH ( NOEXPAND );

Listing 9

What is going on? The difference lies in when and how SQL Server creates automatic statistics, and when it uses them. Simply put, if we do not use the WITH (NOEXPAND) hint when querying an indexed view, the query optimizer will not use statistics created on the indexed view and neither will it create or update statistics automatically (i.e. those statistics objects that begin with _WA_SYS).

Without automatically created or updated statistics, there can be a slight or even drastic difference between the numbers of rows the optimizer estimates a query will return, and the actual number of rows returned. Pay attention to statistics warnings if you see them!

What is the lesson to be learned here? Using the WITH (NOEXPAND) hint when writing queries that reference indexed views is the best way to ensure optimal query plans.

For a more in-depth review of statistics, try Managing SQL Server Statistics, by Erin Stellato. For a thorough review of indexed views and statistics, reference Paul White’s article Indexed Views and Statistics.

Wait, SQL Server didn’t use my index!

Unfortunately, there may still be occasions when the query optimizer decides not to use an indexed view, even though it seems that it could satisfy a query. In fact, SQL Server may refuse to use the clustered index (or any non-clustered indexes) on a view, even if we reference the view directly in the query.

Let’s return to our vCustomerOrders example. Let’s say we want to query the view for the total number of orders a customer has placed, along with the total value of those orders, and we want to search by CustomerID.

SELECT CustomerID ,

COUNT(SalesOrderID) AS OrderCount ,

SUM(TotalValue) AS OrderValue

FROM Sales.vSalesSummaryCustomerProduct

WHERE CustomerID = 30103

GROUP BY CustomerID;

Listing 10

The execution plan, in Figure 13, shows that the plan references the underlying tables and ignores our view and its index. The query cost is .072399.

1938-1-729fe531-3aab-4982-b7e6-78bdcafec

Figure 13

To make the query optimizer use the unique clustered index I created on vSalesSummaryCustomerProduct, we can use the NOEXPAND hint.

SELECT CustomerID ,

COUNT(SalesOrderID) AS OrderCount ,

SUM(TotalValue) AS OrderValue

FROM Sales.vSalesSummaryCustomerProduct WITH ( NOEXPAND )

WHERE CustomerID = 30103

GROUP BY CustomerID;

Listing 11

Now, the execution plan shows a clustered index seek, as shown in Figure 14, and the query cost is .003522.

Figure 14

Adding Non-clustered Indexes to an Indexed View

Once we’ve created an indexed view we can then treat it in much the same way as a normal table. We can add non-clustered indexes to boost query performance. Once again, exercise care. SQL Server has to maintain every index we add to a view, every time someone updates one of the contributing base tables. Indexed views work better for relatively static base tables.

Let’s say we want to query our Sales.vCustomerOrders view by product name.

DECLARE @ProductName VARCHAR(50)

SET @ProductName = 'LL Mountain Frame - Black, 44'

SELECT CustomerID ,

SalesOrderID ,

OrderQty ,

Name

FROM Sales.vCustomerOrders

WHERE Name = @ProductName;

Listing 12

We get a clustered index scan on the view and the query cost is 1.30858.

1938-1-09a7841b-c248-44b1-a1c8-b026b1c3c

Figure 15

It’s great that SQL Server is using the clustered index on the view; but a scan isn’t what we want; a seek would be better. However, once the clustered index exists, we can easily add useful non-clustered indexes, just as we can for any normal table.

CREATE NONCLUSTERED INDEX IX_vCustomerOrders_Name

ON Sales.vCustomerOrders(Name);

Listing 13

When we run the query in Listing 12 again, the execution plan is as shown in Figure 16. The query cost has gone down, to 1.27252.

1938-1-24305b30-d1e7-4e3e-91bb-7b0e63d8c

Figure 16

This time the optimizer chose a seek operation on the new non-clustered index, which is what we wanted. However, it also needed to perform a key lookup to return the additional columns contained in the SELECT clause but not included in the non-clustered index.

To make this index more effective, we can make it a covering index for this query by including all of the columns the query references, as shown in Listing 14.

DROP INDEX IX_vCustomerOrders_Name ON Sales.vCustomerOrders;

CREATE NONCLUSTERED INDEX IX_vCustomerOrders_Name

ON Sales.vCustomerOrders(Name)

INCLUDE (SalesOrderID, CustomerID, OrderQty);

Listing 14

When we run the query again, we see an optimized index seek on the non-clustered index. We also see a significantly reduced query cost, down to .0059714.

Indexed View Requirements

An underlying assumption of all previous query examples was use of SQL Server Enterprise Edition. In this edition, SQL Server’s query optimizer will automatically consider indexes on a view when creating a query execution plan. In SQL Server Standard Edition, we can still create indexed views, but the optimizer will not automatically consider its indexes when formulating an execution plan for a query; it will simply access all of the underlying tables. We have to use the WITH (NOEXPAND) table hint, directly in the FROM clause of each query, to force SQL Server to use the indexed view.

SELECT <column-list>

FROM Sales.vCustomerOrders CO WITH ( NOEXPAND );

Listing 15

We also noted occasions, even when using SQL Server Enterprise Edition, when we may need to use this hint to get the plan we expect. However, it can be dangerous to boss the query optimizer around, telling it what it can or can’t do. Bear in mind also that if you write queries in stored procedures, your applications, or reports that use WITH (NOEXPAND) and then drop the index on the view at a later point in time, the queries that reference that index will fail. In short, the same proviso applies here as applies to the use of any table (index), join, or query hints: use them cautiously and sparingly, and document them.

As well as this requirement when using views on Standard Edition, there is a lengthy list of ‘requirements’ attached to the creation of indexed views, in any SQL Server Edition. We’ve encountered several already, in the need to create them with theSCHEMABINDING option, use fully qualified table references, and use COUNT_ BIG ( *) if the view definition contains aGROUP BY clause. Another, implied but not discussed directly, is that the indexed view definition can only reference tables, not other views.

The Microsoft documentation (http://msdn.microsoft.com/en-us/library/ms191432.aspx) provides a full list of limitations and requirements, so I’ll just briefly summarize some of the more significant here:

Certain database SET options have required values if we wish to create any indexed views in that database – for example, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, andQUOTED_IDENTIFIER must be ON; NUMERIC_ROUNDABORT must be OFF.

All columns referenced in the view must be deterministic – that is, they must return the same value each time. As an example, GETDATE( ) is non-deterministic. DATEADD and DATEDIFF are deterministic.

We cannot include certain common functions in an indexed view – COUNT, DISTINCT, MIN, MAX, TOP, and more.

You can’t have a self-join or an outer join, an OUTER APPLY or a CROSS APPLY.

If you are having difficulty creating an index on a view, reference the Microsoft documentation, as you’ve broken one of the ‘rules’.

Impact of Updating the Base Tables

A few times, I’ve mentioned the impact of modifying data, i.e. inserting into, updating or deleting from, the base tables of an indexed view, and it’s now time to discuss this issue in more detail.

SQL Server has to guarantee that it can return a consistent result set regardless of whether a query accesses a view or the underlying tables, so it will automatically maintain indexes in response to data modifications on the base tables. We can see this in action if we update one of the base tables that make up our vSalesSummaryCustomerProduct view.

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 71803

AND ProductID = 917;

Listing 16

Table 'vCustomerOrders'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customer'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 3, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'vSalesSummaryCustomerProduct'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Figure 17

The execution plan includes many operators, including an update of the vSalesSummaryCustomerProduct clustered index.

1938-1-a1690b4a-7e1d-4f2d-af5e-d300352a4

Figure 18

SQL Server must always ensure that the data in the index and base tables is synchronized, so we need to be careful when adding indexes to views. Every time an underlying column has a new row added or deleted, or is updated, SQL Server must maintain every clustered and non-clustered index, whether on the base table or the referenced indexed view. This will lead to additional writes, which can decrease performance.

Another side effect of indexed views is increased potential for blocking on the base tables during inserts, updates, and deletes, due to increased lock contention on the view’s index. Let’s say we want to insert two records into theSalesOrderHeader table. With no indexed view referenced by the table, both inserts will succeed. However, add an indexed view and the behavior will change. The first insert will have to modify a row in the table, and it will have to modify the index. It will hold locks on both objects. Until that has completed, the second operation will not be able to complete because it also needs locks on both objects.

A great demo of this is available from Alex Kuznetsov in his article, Be ready to drop your indexed view.

Pages

Friday, 26 September 2014

Views in SQL Server

Using the NOEXPAND View Hint

Outer Join Not Allowed in Indexed Views

Cross Database Queries Not Allowed in Indexed View

One of the requirements of Indexed View is that it has to be created ‘WITH SCHEMABINDING’. If the View is not created with that clause, it would not let you create an index on that View. Moreover, if you try to create a View with schemabinding, it would not allow you to create the database.

-- Create DB

USE MASTER

GO

CREATE DATABASE TEST1

CREATE DATABASE TEST2

GO

-- Table1

USE Test1

GO

CREATE TABLE TABLE1 (ID INT)

GO

USE Test2

GO

-- Table2

CREATE TABLE TABLE2 (ID INT)

GO

USE Test1

GO

-- Create View

CREATE VIEW CrossDBView

WITH SCHEMABINDING

AS

SELECT t1.ID AS t1id, t2.ID AS t2id

FROM Test1.dbo.Table1 t1

INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.ID

GO

/*

Error:

Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4

Cannot schema bind view 'CrossDBView' because name 'Test1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

*/

UNION Not Allowed but OR Allowed in Index View

SELECT * and Adding Column Issue in View

Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed

The resolutions of these issues are as follows:

Refresh the views using sp_refreshview stored procedure

Do not use SELECT * but use SELECT column names

Create view with SCHEMABINDING; this way, the underlying table will not get modified.

Adding Column is Expensive by Joining Table Outside View

USE AdventureWorks

GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))

DROP VIEW [dbo].[vw_ViewLimit1] GO

-- Create View on sample tables

CREATE VIEW vw_ViewLimit1

AS

SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod

INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID

GO

We can now check the performance of both queries using the execution plan. This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

However, when looking at the execution plan (Figure 1) we can see that SQL Server still performs index scans against each of the five underlying tables

Figure 1

Create a Unique, Clustered Index

In Listing 5, we access the same base tables but also perform some aggregations.

Wait, SQL Server didn’t use my index!

No comments:

Post a Comment