Answer: SQL Server DBA Eve Five: About the view index

5. Can we build the index on the view, if it can be built, what will be the advantages and disadvantages; What is the difference between the index on the view and the index on the table;

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

The ability to create indexes, the benefit: You can improve query performance. Cons: Increases the cost of maintaining indexes accordingly
Difference:

1) The first index created on a view must be a unique focused index

2) The view must be defined using the SCHEMABINDING option. Schema binding
Binds the view to the schema of the underlying base table.

3) The base table referenced by the view must be in the same database as the view and has the same owner as the view

4) The tables and user-defined functions referenced by the view
The view must be referenced by a two-paragraph name, one-segment, three-segment, and four-segment names are not allowed

A view is a saved T-SQL query that does not occupy any space in the database

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

SET ANSI_NULLS on
GO
SET ansi_padding on
GO
SET ansi_warnings on
GO

SET ARITHABORT on

GO

SET Concat_null_yields_null on
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 '

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

SET ANSI_NULLS on
GO
SET ansi_padding on
GO
SET ansi_warnings on
GO

SET ARITHABORT on

GO
SET Concat_null_yields_null on
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_p2p_invest_indexed
With SCHEMABINDING
As
SELECT
Borrowid,
COUNT_BIG (*) as Investcount,
SUM (ISNULL (investamount,0)) as TotalSales
FROM dbo. P2p_invest
GROUP by Borrowid
GO

Drop View vw_p2p_invest_indexed

Select Top * from vw_p2p_invest_indexed
where borrowid=4872333414374057429

CREATE UNIQUE CLUSTERED INDEX
Idx_vw_p2p_invest_borrowid on vw_p2p_invest_indexed (BORROWID)

--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_p2p_invest_indexed '

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

Creating an index on a view requires three conditions:
One, the view must be bound to the schema.
To do this, you must add the with SCHEMABINDING in the CREATE VIEW statement, and if you are using Enterprise Manager, right-click on the space in the design interface and select bind to schema.
Second, the index must be a unique index.
To do this, you must specify UNIQUE in the CREATE INDEX.
Third, the index must be a clustered index.
To do this, you must specify CLUSTERED in the CREATE INDEX.
Cases:
CREATE VIEW Viewfoo with SCHEMABINDING
As
SELECT ID ...

CREATE UNIQUE CLUSTERED INDEX index_viewfoo on Viewfoo (ID)

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

Views cannot reference any other view, export table, rowset function, or self-query, only the base table

All base tables referenced by the view must be in the same database as the view, and the owner is also the same as the view

You must create a view using the SCHEMABINDING option. SCHEMABINDING the schema that binds the view to the underlying base table.

If the SELECT statement in the view definition specifies a GROUP by clause, the key for a unique clustered index can only refer to the column specified in the GROUP by clause.

The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:
The select list cannot specify columns using the * or table_name.* syntax. You must explicitly give the dequeue name

在创建索引视图的select语句中，不能存在重复的列名

A view cannot contain an external link or a self-link, that is, you can only use inner join when linking a table, and you cannot make the same table before or after the inner join, and you cannot use the left or right OUTER join

Views cannot contain UNION clauses, top clauses, ORDER by clauses, having clauses, rollup clauses, cube clauses, Compute clauses, Compute by clauses, or distinct keywords

Views do not allow certain aggregate functions, such as: Count (*) can be substituted with COUNT_BIG (*), AVG (), Max (), Min (), Stdev (), STDEVP (), Var (), or VARP (), etc.

If the view contains a GROUP BY clause, then he must include COUNT_BIG (*) in the Select column;

All the standard and user-defined functions in the view must be referenced by using the two-segment name, which is the owner. The name of the table or function;

Answer: SQL Server DBA Eve Five: About the view index