140912 ● indexes, views, and cursors

Index

The storage of a table is composed of two parts. One part is used to store the data page of the table, and the other part stores the index. The page index is stored on the index page. Generally, the index page is much smaller than the data page. When you search for data, the system first searches for the index page, finds the required data pointer, and then directly reads data from the data page through the pointer.

To some extent, we can regard the database as a book, and the index as the directory of the book.

In SQL Server databases, indexes are divided into two types based on different storage structures.

Cluster Index clusteredindex

Non-Cluster Index nonclustered Index

The Cluster Index sorts the data on the physical data page of the table by column and stores the data on the disk again. That is, the Cluster Index and data are mixed, so it is very fast to search for data using the cluster index. However, because the Cluster Index completely sorts all the data in the table, it requires a very large space. The data rows of a table can only be stored on the disk in one sort mode. Therefore, a table can only have one cluster index.

Non-clustered indexes have a structure completely separated from table data. Non-clustered indexes sort row locators by keyword values in a certain way. This order does not match the sorting of table rows on the data page.

The cluster index can be viewed as the pinyin query of the dictionary, and the non-cluster index can be viewed as the first part of the dictionary.

Create an index using a statement:

Create [unique] [clustered | nonclustered]

Index index_name on {table | view} (column [ASC | DESC] [,... n])

Create a cluster index for the table Products

Create unique clustered index pk_p_id

On products (p_id)

Create a composite index for table Products

Create index pk_p_main

On products (p_id, p_name, sumvalue)

-- Sumvalue is a calculated column expression of price * quantity.

Create an index using the interface. The method is similar to the one-click creation. Open the table design and then follow the operation

 

Delete the index and use drop

 

View

A view is a table exported from one or more tables or views. Its structure and data are based on table queries.

A view is not a basic table that actually exists, but a virtual table view. The corresponding data is not stored in the database in the view structure, but in the table referenced by the view. The data displayed in the view is only the data stored in the basic table.

 

Advantages of a view

Viewpoint concentration

A view set allows users to focus only on certain data that they are interested in and the specific tasks they are responsible.

Simplified operations

If the view itself is a result set of a complex query, you do not have to re-write these complex query statements every time you execute the same query. You only need a simple query view statement.

Merge split data

Table design often splits tables horizontally or vertically, but changes in the table structure have adverse effects on applications.

Security

Users can only view and modify the data they can see. Other databases or tables are neither visible nor accessible.

 

Create View

Create [<owner>] view view_name [(column [,... n])] [with encryption] -- The underlined part indicates encryption, which is generally unavailable

As

Select_statement

Example:

Create view student_info (SNO, sname, birthday, Class)

As

Select SnO, sname, sbirthday, class from student

Select * From student_info -- View

 

Create a view on the page:

 

Delete View

Drop view name

Manage data through views

A view has a similar structure to a table. When data is inserted or updated to a view, data is inserted and updated to the table referenced by the view. However, inserting and updating data through the view has some limitations compared with the table.

When updating and deleting data through views, You need to note two issues:

When executing update Delete, the deleted and updated data must be included in the view result set.

If a view references multiple tables, the DELETE command cannot be used to delete data. If update is used, columns updated like insert must belong to the same table.

 

Cursor

The relational database management system is essentially set-oriented. In ms SQL Server, there is no way to describe a single record in a table.

If you want to read a record from a certain result set one by one, how can this problem be solved? Cursors provide us with an excellent solution.

A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records.

Each cursor must have four components, which must conform to the following sequence:

1. Declare cursor

2. Open cursor

3. Fetch information from a cursor

4. Close or deallocate cursor

Syntax format:

Declare cursor_name [scroll] cursor

For select_statement

[For {read only | update [of column_name [,... n]}]

 

Scroll

Indicates that all the extraction operations, such as first last prior next relative absolute, are available. If this reserved word is not used, only the next extraction operation can be performed.

 

Standard cursor

Declare cur_authors cursor

For

Select au_id, au_lname, au_fname, phone, address, city, state, contract

From authors

Reads data from the cursor. Syntax format:

Fetch

[[Next | prior | first | last | absolute {n | @ nvar} | relative {n | @ nvar}]

From]

{Cursor_name}

[Into @ variable_name [,... n]

 

140912 ● indexes, views, and cursors