Usage of the index view in SQL Server

 

InArticleThe SQL Server View is actually an SQL statement. When the view is called, it dynamically executes the defined SQL statement and returns the result set.

After SQL server2005, Microsoft launched the index view. The index view is essentially different from the previous normal view, that is, the result set in the index view is stored in the database, instead of the results of each dynamic execution. In this way, the query performance can be improved much like querying the basic table. We can also create indexes on the index view to further improve the performance of the index view.

How can we create an index view? We can create it by using the keyword schemabinding, for example:

Create view v_salary with schemabinding as select name, salary from DBO. Salary

When creating an index view, we should pay attention to the following points: 1. In the index view, the * number cannot be used, and the column names in all result sets must be specified. We try to use the * number in fact:

Create view v_ss_salary with schemabinding as select * From DBO. Salary

Error: syntax '*' is not allowed in objects bound to the architecture '*'.

2. In the index view, the architecture DBO of the table must be added before all used tables. We try not to add DBO before the table:

Create view v_salary with schemabinding as select name, salary from salary

Error: The view 'v _ salary 'cannot be bound to the schema because the 'salary' name is invalid for the schema binding. The name must be composed of two parts, and the object cannot reference itself.

3. Duplicate columns are not allowed in the index view (in fact, this restriction exists in the general view). We try to create duplicate columns in the View:

Create view v_salary with schemabinding as select name, name from DBO. Salary

Error: The column names in each view or function must be unique. The column name 'name' is specified multiple times in the view or function 'v _ salary '.

4. You can only create unique clustered indexes in the index view. We try to create a clustered index on the index View:

Create clustered index ix_name on v_salary (name)

Error: you cannot create an index for the view 'v _ salary '. It does not have a unique clustered index.

The correct creation statement is as follows:

Create unique clustered index ix_name on v_salary (name)

Suppose v_salary is a normal view. If we create an index for it, the following error will be reported: an index cannot be created for the view v_salary, because the view is not bound to the architecture

Delete An index view: · The method for deleting an index view is the same as that for deleting a common view. in SQL Server Management studio, select the view to be deleted, right-click the "delete" command in the menu. · You can delete an SQL statement as follows:

Drop view name