Indexes and views

Clustered indexes and nonclustered indexes

The function of aggregation is to change the physical order of a column (or columns) to be consistent with the logical order, in SQL Server, the storage of the clustered index is stored in the B-tree, and the leaves of the B-tree store the data of the clustered index directly, because the clustered index changes the physical storage order of the table in which it resides. Therefore, each table can have only one clustered index.

A nonclustered index is, in essence, a clustered index. A nonclustered index does not alter the physical structure of the table in which it is located, but rather generates an additional B-tree structure for a clustered index, but the leaf node is a reference to its own table, which is divided into two types, and the row number is referenced if there is no clustered index on the table. If the clustered index is already on the table, the page referencing the clustered index is referenced. Nonclustered indexes require additional space for storage, clustered indexes by indexed columns, and a pointer to the table of the nonclustered index on the leaf node of the B-tree.

It needs to be used reasonably, and when the table is indexed, the data is stored in a B-tree. So when you update the insert Delete, you need to move the page physically to adjust the B-tree. Therefore, when the update is inserted to delete data, it can degrade performance. For clustered indexes, when the table is updated, the nonclustered indexes also need to be updated, which is equivalent to a multiple update of N (n= nonclustered index) tables. Therefore, it also degrades performance.

Transfer from http://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html

View

Views can be thought of as virtual tables that are defined on SQL Server. The view, as its name implies, is another entry for viewing data. The general view itself does not store the actual data, but only a SELECT statement and the metadata of the table involved.

Views are divided into three categories in SQL, normal view, indexed view, and Split view

Benefits of using Views:

• The view hides the underlying table structure, simplifying data access operations
• Because the underlying table structure is hidden, security is greatly enhanced and the user can only see the data provided by the view
• Use views to facilitate rights management, allowing users to view permissions rather than the underlying tables to further enhance security
• The view provides a user-accessible interface that, when the underlying table changes, changes the view's statements to accommodate the client program that has been built on this view without being affected

Experience with views

• Be sure to tune the SELECT statement performance in view to the best (seemingly nonsense, but the truth is nonsense ...)
• View is best not nested, if not nested, a maximum of one layer nested
• Can replace view with stored procedures and custom functions, try not to use view, stored procedures cache execution plan, better performance, less limit
• On the split view, do not use aggregate functions, especially the aggregation function also contains the distinct
• In the view, if the WHERE clause can be added to the view, do not add to the view (because the call view will return all rows and then filter, Performance Killer, if you have added order by ...)

Transfer from http://www.cnblogs.com/CareySon/archive/2011/12/07/2279522.html

　　

Indexes and views