Opinions on whether the index view is physically stored in the database and how to use the index View

Whether the index view is physically stored in the database and some opinions on using the index view. Preface I wrote this topic in my article but did not write it casually, but no one else can see it without writing it in the essay, because the younger brother does not have a deep understanding of the view, I hope to write it in the essay to let everyone discuss this topic. At the end of the article, the younger brother will introduce our company

Whether the index view is physically stored in the database and some opinions on using the index view. Preface I wrote this topic in my article but did not write it casually, but no one else can see it without writing it in the essay, because the younger brother does not have a deep understanding of the view, I hope to write it in the essay to let everyone discuss this topic. At the end of the article, the younger brother will introduce our company

Opinions on whether the index view is physically stored in the database and how to use the index View

Preface

I did not write this topic in my articles, but I couldn't write it in my articles because I was not familiar with the view.

I hope you can discuss this topic in your essay.

At the end of the article, I will tell you about the use of the index view in our company's system. I hope you will share your comment on how to apply the index view in the system.

Let the younger brother learn about the index view.

View type

First, let's take a look at the types of visual charts in SQLSERVER.

1. Standard view

2. Index view: New features provided after sql2000. After an index is created on a view, the result set of the view is randomly materialized and stored in the physical storage of the database.

The first index created for the index view must be a unique clustered index. Other non-clustered indexes can be created only after a unique clustered index is created. If a view is deleted, its indexes are also deleted,

If a clustered index is deleted, all non-clustered indexes and automatically created statistics of the view are also deleted. Deleting a clustered index of a view deletes the stored result set, and the index view changes to a standard view.

3. Partition view: partition data in a group member table is horizontally connected between one or more servers to make the data look like a table. Depending on the data server connection,

It is also divided into local partition view and distributed partition view. The distributed partition view is used for database server Federation.

Verify

To distinguish between the standard view and the index view, we create a basic table, an index view, and a standard view. Both views are based on the basic table.

Use the following SQL statement to create a test environment

. (1, 1) not null, (50) NULL ,,,). (,,,),,,,,,,. index_View_Users31 WITH SCHEMABINDINGUserID, UserName, Age, Gender, CreateTime FROM dbo. usersCIX_View_Users ON index_View_Users (UserID) index_View_Users stand_View_UsersUserID, UserName, Age, Gender, CreateTime FROM dbo. users stand_View_Users