SQL Server indexes and views

Business

Primary key:

Index:

Lock:

=========================================

A page is divided into

Page Header

Page body--store row

End of page

The page has a fixed size, but the data is not necessarily full, it is possible to fragment, that is, row-by-page

=========================================

Sort:

The store is sorted

Index maintenance costs are low: Therefore, the amount of modification is related to index maintenance.

Full table Scan:

Data storage may not be contiguous on the hardware ....

Query optimizer:

Why automatically optimize SQL, because the database stored some statistics, based on these statistics, the optimization of SQL, improve query efficiency. The index is ignored by the query optimizer because the index is sometimes built, but there is a problem with query SQL writing.

Base table: Original table

=========================================

Materialized views:

Partitioned views: Check Constraints

=========================================

1. Data files

2. A database has multiple data files

3. FILE-page-line

4. Positioning: (The process of data row positioning)--- positioning process

　1. In that data file

2. The page on the file

3. The line on the page.

5.

Primary key: is for data unique

Index: In order to query

Attention:

If the primary key: Create a clustered index (without index condition)

Unique constraint: Create a nonclustered index (without index condition)

6. Indexes also need to occupy storage space, so the number of indexes that are seen in different data columns (data types) is also different, of course, the smaller the index occupies the less space the better.

=============================================

Page-Intermediate set-index-table

With Lock:

Include Name:---can use this

=============================================

Use an index on the column you want to query

(index)--saved on the page set, the most detailed data is stored on the underlying table

Indexing is established based on query statements, so you can use the Where condition to build the index ....

==============================================

View:

Virtual table: The data itself is not stored

Temp table:

Temporary storage of data, used in a large number of stored procedures, as a temporary data storage site.

Can be used with triggers

Materialized views:

Curing a view, directly querying, can create a clustered index above the view, similar to the role of materialized views.

7. Partitioned views

　　

=============================================

SQL Server indexes and views