Use index view in SQL Server to view performance

In SQL Server, a 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 queries and add another layer of security to the base table. However, it does not occupy any space in the database. In fact, the view does not do anything before you query it.

 

 

Index View

 

 

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

 

 

The index view is a view that has been materialized or saved in the database. When a basic table is updated, the indexes created for the view are saved and updated by the database engine. The index view is useful when your result set returns many rows and requires a total number. This is because the database engine must maintain the view index when updating the base table data, which may reduce the transaction performance.

 

 

To create an index for a view, the view definition must comply with a group of conditions and session settings, and the base table and
View definition. If you are sure that your view meets these criteria (I will discuss this later), the first index you create for the view must be a unique clustered index. The first view created must be
The index is clustered on a group of columns so that the index is materialized.

 

 

The following code describes how to create an index view. The script creates a saleshistory table and loads data to it.

 

CREATE TABLE SalesHistory ( SaleID INT IDENTITY(1,1), Product VARCHAR(30), SaleDate SMALLDATETIME, SalePrice MONEY ) DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=10000) BEGIN INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/10/2006'), DATEPART(ms, GETDATE()) + (@i + 57) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('BigScreen', DATEADD(mm, @i, '3/2/2006'), DATEPART(ms, GETDATE()) + (@i + 13) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('PoolTable', DATEADD(mm, @i, '2/11/2007'), DATEPART(ms, GETDATE()) + (@i + 29) ) SET @i = @i + 1 END GO

 

 

 

 

 

The following code creates a view to summarize the data in the table:

CREATE VIEW vw_salesbyproduct AS SELECT Product, COUNT_BIG(*) as ProductCount, SuM(SalePrice) as TotalSales FROM dbo.SalesHistory GROUP BY Product

 

 

 

 

 

Since it is just a T-SQL query definition, creating this view does not take much time. After creating a view, you can query it like a table.

 

SELECT Product, TotalSales, ProductCount FROM vw_SalesByProduct WHERE product = 'Computer'

 

 

 

 

 

If you are in SQL Server Management studio or query
Set options in analyzer to view the queried "Execution Plan" (Execution
Plan), you will notice that the above query uses a table scan to find the total results of computer products. Table scanning is generally used to scan the entire result set with no index
Find the expected result.

 

 

After some simple changes, you can modify the view so that you can add an index to it to improve search performance. First, you must set the following session settings:

 

SET ANSI_NULLS ON GO SET ANSI_PADDING ON GO SET ANSI_WARNINGS ON GO SET CONCAT_NULL_YIELDS_NULL ON GO SET QUOTED_IDENTIFIER ON GO SET NUMERIC_ROUNDABORT OFF GO

 

 

Now you can create your own view. To make things easier, I create a new view.

CREATE VIEW dbo.vw_SalesByProduct_Indexed WITH SCHEMABINDING AS SELECT Product, COUNT_BIG(*) AS ProductCount, SUM(ISNULL(SalePrice,0)) AS TotalSales FROM dbo.SalesHistory GROUP BY Product GO

 

 

 

 

 

The following script creates an index for our view:

CREATE UNIQUE CLUSTERED INDEX idx_SalesView ON vw_SalesByProduct_Indexed(Product)

 

 

 

 

 

To indicate that an index has been created for the view, and it actually occupies the database space, run the following script to find out how many rows of the clustered index and how much space the view occupies.

EXECUTE sp_spaceused 'vw_SalesByProduct_Indexed'

 

 

 

 

 

The following SELECT statement is the same as the previous statement, but this time it executes a clustered index search, this process is completed very quickly.

 

SELECT Product, TotalSales, ProductCount FROM vw_SalesByProduct_Indexed WHERE Product = 'Computer'

 

 

 

 

 

Do not forget Performance Testing

 

 

If used properly, index views are useful because they can significantly improve query performance. However
To improve the performance of clustered indexes, the database engine must maintain that index during all transactions in the view base table. Because of this exchange, creating an index view may be beneficial to the system and cause damage to the system.
The best way to determine whether this is beneficial or harmful is to conduct a comprehensive performance test.