Using indexed views in SQL Server

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. 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 (Saleprice) as TotalSales
Fromdbo.saleshistory
GROUP 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 Product = ' 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:

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 build your own view. To make things easier, I built 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 indexes our view:
CREATE UNIQUE CLUSTERED INDEX
Idx_salesview on vw_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.
EXECUTE sp_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.
SELECT
Product, TotalSales, ProductCount
From vw_salesbyproduct_indexed
WHERE Product = ' computer '

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 indexed views in SQL Server