Using indexes on Views in SQL Server (reproduced)

Source: Internet
Author: User
Tags sql server query management studio sql server management sql server management studio

In SQL Server, the view is a saved T-SQL query. The view definition is saved by SQL Server so that it can be used as a virtual table to simplify the query and add another layer of security to the base table. However, it does not occupy any space in the database. In fact, the view doesn't do anything until you query it.

Indexed views

In SQL Server 2000 and 2005, you can add indexes to the view. However, if the view is just a query definition that is saved in the database and does not have its own data before it is run, how do you create an index for that definition? Well, it's more troublesome.

An indexed view is a view that has been materialized or saved in a database. When the base table is updated, the indexes established for the view are saved and updated by the database engine. Indexed views are useful when your result set returns many rows and requires a total number of requests. This is because the database engine must maintain the view index when the base table data is updated, which may degrade the performance of the transaction.

To create an index on a view, the view definition must adhere to a set of conditions and session settings, and also require that the base table and view definitions be linked. If you determine that your view meets these criteria, the first index you create for the view must be a unique clustered index, after which you can create multiple nonclustered indexes on the view. The first view that is established must be on a set of columns and be clustered so that the index is materialized.

List A shows how to create an indexed view. The script will build the Saleshistory table and load the data to it.

The following code creates a view that summarizes the data in the table:

CREATE VIEW vw_salesbyproduct  as SELECT Product,count_big (*  as ProductCount,SuM as Totalsalesfromdbo.saleshistoryGROUP by Product

Since it's just a T-SQL query definition, it doesn't take much time to build this view. Once you have a good view, you can query it like a table.

SELECT Product, TotalSales, ProductCount  from vw_salesbyproduct WHERE = ' Computer '

If you set the options in SQL Server Management Studio or Query Analyzer to view the execution plan for the Queries (execution plan), You will notice that the query above uses a table scan to find the total result of the computer product. Table scans are typically used in cases where the data is not indexed, and it scans the entire result set to find the desired results.

With some simple changes, you can modify the view so that you can add an index to it to improve your search performance. First, you must set the following session settings (Note that you must strictly follow the set of the following set statement to successfully turn on indexing on the view):

SETAnsi_nulls onGOSETAnsi_padding onGOSETAnsi_warnings onGOSETConcat_null_yields_null onGOSETQuoted_identifier onGOSETNumeric_roundabortOFFGO--Now you can build your own view. To make things easier, I built a new view. Note A view using an index must use the WITH Schemabinding statement when it is establishedCREATE VIEWdbo.vw_salesbyproduct_indexed withSCHEMABINDING asSELECTProduct,count_big (*) asProductCount,SUM(ISNULL(Saleprice,0)) asTotalSales fromdbo. SaleshistoryGROUP  byProductGO--The following script indexes our view:CREATE UNIQUE CLUSTERED INDEXIdx_salesview onvw_salesbyproduct_indexed (Product)--to show that the view has been indexed, and that it does occupy the space of the database, run the following script to find out how many rows the clustered index has and how much space the view occupies. EXECUTEsp_spaceused'vw_salesbyproduct_indexed'--The following SELECT statement is the same as the previous statement, but this time it performs a clustered index search, which is done very quickly. SELECTProduct, TotalSales, ProductCount fromvw_salesbyproduct_indexedWHEREProduct= 'Computer'


No hit index after adding the view index?

If you execute the query on the view after completing the above steps, it is possible to find the execution plan of the statement or the table scan to scan the underlying saleshistory table instead of using index scan or index seek to use the indexes we added above on the view Idx_ SalesView, this could be a SQL Server version of the problem, according to a foreigner article that if your SQL Server is not an Enterprise Edition even if you set up a view index, but in the query will not use the view index, It is also possible that SQL Server Query Analyzer thinks that scanning the underlying table directly is more efficient than using the index on the view, so your query statement is not using the view index. At this point we can use the WITH (NOEXPAND) statement to tell SQL Server to use the view index in the query, with the following statement:

SELECT Product, TotalSales, ProductCount  from  with (NOEXPAND) WHERE = ' Computer '

With the WITH (NOEXPAND) statement, the above query should use the clustered index Idx_salesview we established on the view vw_salesbyproduct_indexed.

Here is a foreigner's article in detail the SQL Server View index, with (NOEXPAND) statement is also mentioned in this article, have time to see.

Don't forget the performance test

Indexed views are useful if used properly, because they can significantly improve the performance of queries. However, because of the increased performance of the clustered index, the database engine must maintain that index in all transactions of the EOG table. Because of this exchange, creating an indexed view can be beneficial to the system and can also cause damage to the system. The best way to determine whether this is beneficial or harmful is to perform a comprehensive performance test.

Using indexes on Views in SQL Server (reproduced)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.