Shulou(Shulou.com)06/01 Report--

Understanding of indexes, views, cursors, stored procedures and triggers

Understanding of indexes, views, cursors, stored procedures and triggers

1. Index

1-1. Overview of the index

We combine one or more columns in a table and the physical addresses recorded in the table where the elements in the column reside into a new table. The record of this table is roughly the contents of the column and the physical address of the record in which the column is located.

1-2. Advantages and disadvantages of index.

Advantages: greatly speed up the execution of the source table, we can retrieve the index table to achieve the retrieval of the source table. Where on earth is it? For example: we query a record in the source table through the T-SQL statement. When there is no index table, the computer first loads the whole source table from external memory to memory, and then matches one by one. It takes a lot of time to load from external memory to memory. When there is an index, the computer first loads the index table, then matches it, and then takes out its physical address. At this time, it loads the record in the large table from external memory. This method is troublesome for people to read. But the computer may not think so (depending on the length and number of records in the source table).

Disadvantages: the index table needs to take up physical space; when operating on the source table, it is also necessary to maintain the index table, which increases the task of maintenance.

2. View

2-1. Overview of the view

A view is a virtual table composed of column data of one or more tables. The so-called virtual table means that the whole table does not exist on the hard disk. In fact, when we create a view, we actually create a packaged Select statement. Here we call it A, and to create a view, we store An on the hard disk. When we call the view, the system will automatically execute A, and then the system will execute An again. All we see is a table. (when the data of this table changes, the base table remains the same, which depends on the specific situation. It can be explained that we can change the base table data through the virtual table.)

2-2. Advantages and disadvantages of views.

Advantages: views can restrict our access to some data in the table, increase the security of the data, and reduce the repeated writing of T-SQL statements.

Disadvantages: complex views generally cannot be modified; views that can be modified also need to be changed to apply the operation to the base table.

3. Vernier

3-1. Overview of cursors

In order to manage the data more efficiently, the database management system uses the whole block of data to manage the operation, which is very good for the database management system. However, this management mode is not good for the application, because my application has little data for operation management, and it is highly targeted, according to these characteristics of the application. In order to make the application more efficient, we have created cursors, which are used to complete operations on smaller units of data in the dataset, that is, operations on the data in the recordset are done on a record-by-record basis, so cursors consist of a result set (which can be zero, one, or multiple records retrieved by related selection statements) and cursor locations that point to specific records in the result set. Cursor is a product from managing more data to less data, operating uncertain data to deterministic data, and being weak to pertinent to the top of the data.

3-2. Advantages and disadvantages of cursors.

Advantage: an application can apply cursors to specify rows on a dataset.

Disadvantages: improper use will make the operation less efficient.

4. Stored procedure

4-1. Overview of stored procedures

A stored procedure is a set of SQL statements. Of course, this set of statements can perform specific functions. After the stored procedure is established, it will be compiled and then stored on the hard disk.

4-2. Advantages and disadvantages of stored procedures

Advantages: one compilation, multiple execution, so that users do not have to write the same SQL statements many times, and these SQL statements do not have to be compiled and executed all the time, saving time.

Disadvantages: simple application can be changed, when the application is complex, it becomes more complex.

5. Trigger

5-1. Overview of triggers

Trigger is a special stored procedure, especially, trigger does not need to be started by human operation (for example, called in the program), trigger is started automatically by the execution of events (delete, update, etc.).

5-2. Advantages and disadvantages of flip-flops

Advantages: the security of the data is stronger, for example: the operation of deleting data can be cancelled through triggers.

Cons: it becomes a bit complicated to maintain data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.