Understanding of the concepts of indexes, views, triggers, stored procedures, cursors, etc. of the database

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 a list of one or more columns in a table with the physical addresses recorded in the table of the elements in the column into a new table. The record for this table is roughly the contents of the column and the physical address of the record where the column resides.

1-2, the advantages and disadvantages of the index www.2cto.com

Advantage: It greatly accelerates the execution speed of the source table, and we can retrieve the source table by retrieving the index table. Where the hell's it going? Example: We query a record in the source table through T-SQL statement, when there is no index table, the computer first load the entire source table from external memory to memory, and then one by one matches, from external memory load to memory time is very large; when there is an index, the computer first loads the index table, then matches, finds, Take out its physical address, at this point, and then load the large table from the external memory in the record, this method, people look at the time of trouble, but the computer may not think so (specifically also depends on the source table record length and quantity)

Disadvantage: The index table needs to occupy the physical space, and when it operates on the source table, it also maintains the index table, which is the task of maintenance is aggravated.

2. View

2-1. Overview of views

A view consists of a table or columns of multiple tables of data, a virtual table, so-called virtual table, that is, the whole table does not exist on the hard disk, in fact, we create a view, actually create a wrapped SELECT statement, here we call a, create a view, we are to the hard disk storage A, When we call this view, the system automatically executes a, and then we see a table. (When the table data changes, the base table will be unchanged, that depends on the situation, it can be explained that we can be virtual expression to change the base table data purposes)

2-2. Advantages and disadvantages of view

Pros: Views can limit our access to some of the data in the table, increase the security of the data, and reduce the repeated write T-SQL statements.

Cons: Complex views cannot modify content, and views that can be modified also need to be changed so that the action is applied to the base table.

Www.2cto.com

3. Cursors

3-1. Overview of Cursors

Database management system in order to manage the data more efficient, the use of the whole block of data for management operations, this management method for the database management system is very good, but this management method is not good for the application, because my application for operations management data is very small, and targeted also strong, Based on these characteristics of the application, we create cursors to make our applications more efficient, and cursors are designed to perform operations on smaller units of data in the dataset, that is, the operation of the data in the recordset is done by a single record, so the cursor is the result set (which can be 0, One or more records retrieved by the relevant selection statement) and a cursor position that points to a particular record in the result set. A cursor is a product that manages multiple data to less data, operates uncertain data to deterministic data, and is weak to the top-to-target of data.

3-2. Advantages and disadvantages of cursors

Advantage: The application can apply cursors to the data set by row-specific operations.

Cons: Improper use will make the operation less efficient.

4. Stored Procedures

4-1. Overview of stored Procedures

The stored procedure is a set of SQL statements, of course, this set of statements can accomplish certain functions, once the stored procedure is established, it will be compiled and then stored on the hard disk.

4-2, the advantages and disadvantages of stored procedures www.2cto.com

Advantages: A compile, multiple executions, so that users do not have to write the same SQL statements, and these SQL statements do not have to be compiled in the execution, saving time.

Disadvantages: Simple application can be, complex time application, is to become complex.

5. Trigger

5-1. Overview of Triggers

A trigger is a special stored procedure, especially when a trigger does not need to be started by an operator (e.g., called in a program), and the trigger is started automatically by the execution of the event (delete, update, and so on).

5-2. Pros and cons of triggers

The advantage: The data is more secure, example: the operation of deleting data can be canceled by a trigger.

Cons: When it comes to maintaining data, it gets a little complicated.

This article is from the "0805" blog, please be sure to keep this source http://zrxwcc.blog.51cto.com/6160351/1903776

Understanding of the concepts of indexes, views, triggers, stored procedures, cursors, etc. of the database