SQL Server creates an index view and displays the performance

InSQLServer, the view is a stored T-SQLQuery. View DefinitionSQLServer saves, so that it can be used as a virtual table to simplify queries, and adds 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

InSQLIn 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 must be associated. 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 on a group of columns and clustered so that the index can be 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, '2014/1/123 '),

DATEPART (MS, GETDATE () + (@ I + 57 ))
Insert into SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Bigint', DATEADD (mm, @ I, '2017/123 '),
DATEPART (MS, GETDATE () + (@ I + 13 ))
Insert into SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Pooltable', DATEADD (mm, @ I, '2014/1/123 '),
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

FROMdbo. SalesHistory

Group by Product

Because it is just a T-SQLQuery definition. It does not take much time to create this view. After creating a view, you can query it like a table.

SELECT Product, TotalSales, ProductCount

FROM vw_SalesByProduct

 

WHERE product = 'computer'

If you areSQLSet options in Server Management Studio or Query Analyzer to view the Query Execution Plan. You will notice that the preceding Query uses a table scan to find the total results of the Computer product. Table scanning is generally used to scan the entire result set to find the expected results when no data is indexed.

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 ONGO

 

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, due to the increased 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.