SQL Some issues 2

1. What does a trigger do?

A: A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data,

You can track operations within the database so that unauthorized updates and changes are not allowed. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

2. What is a stored procedure? Using what to invoke?

A: A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If a single operation requires multiple SQL execution,

Using stored procedures is faster than simple SQL statement execution. You can invoke a stored procedure with a command object.

3. The role of the index? And what are the pros and cons of it?

A: The index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book.

Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and also increases the size of the database.

3. What is a memory leak?

A: Generally what we call a memory leak refers to a leak in heap memory. Heap memory is a program that is allocated to it from the heap, arbitrarily sized, and then shown to free memory after it is exhausted.

When an application creates an object with the keyword new and so on, it allocates a piece of memory from the heap, the program calls free or the delete frees the memory after use, or the memory is not used, we say that the memory is compromised.

4. Maintain database integrity and consistency, do you prefer to use triggers or self-write business logic? Why?

A: I do this, as far as possible to use constraints, such as check, primary key, foreign keys, non-empty fields to constrain, so that the most efficient and most convenient. The second is the use of triggers,

This approach guarantees that the data is intact and consistent regardless of the business system access to the database. The final consideration is self-writing business logic, but this is cumbersome, programming complex, inefficient.

5. What is a transaction? What is a lock?

A: A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails then the entire operation is failed and the operation is rolled back to the pre-operation state.

Or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. As with real-life locks, it can make certain data owners,

Some data or structure cannot be used within a certain period of time. Of course, locks are also divided into levels.

6. What does a view mean? What is a cursor?

A: A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables.

Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

Cursor: is a query out of the result set as a unit to effectively handle. A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set.

You can make modifications to the current row of the result set. Cursors are generally not used, but cursors are important when you need to process the data individually.

This article is from "Xiao Liu _blog" blog, please be sure to keep this source http://liuyafang.blog.51cto.com/8837978/1408186