Database notes (1)

Indexes and constraints can constrain the data that can be placed in a table.
The index on the table provides a way to quickly search for individual records. When creating a table index, you can select which or which fields to index.
The index can be unique or non-unique. A uniqueness index is used to constrain the data that can be placed in a table.
Indexes can also be clustered or non-clustered. This term indicates the physical storage sequence of database tables. Although a table can have multiple indexes, it can have only one cluster index.
SQL Server provides another type of ownership, called "full-text ownership ". Different from general, full-text indexes are stored in a special object called "Cataloguing", while general indexes are stored together with the tables they index. Full-text indexes are not updated automatically. You need to do something to update them. For example, you can run a special job on the server to update them, or configure them to automatically update when the table changes. However, full-text indexes provide special types of searches, but these search types are not as accurate as those supported by common indexes. When searching for a record in a common index, you must precisely provide the values included in the index. When using full-text indexes, you can search in a more natural way.
A constraint is a rule applied to table data.

 

Databases that allow multiple users to modify data must have a mechanism to ensure that those changes maintain consistency. Most databases use the locking mechanism for this purpose.
The basic concept of locking: Users sometimes need to exclusively access a table, so the server locks the table for the user. When the user finishes using that table, the lock is released, so that the data in that table is available to other users again.
Locks are usually classified into symmetric locks and open locks. If you use symmetric locking, the lock is immediately added when the user modifies the data. When the user completely modifies the data, the lock is released. This ensures that other users cannot modify the data when the first user modifies the data. On the other hand, if open locks are used, the locks are added only when all the modifications have been completed and the database is ready to write them into the table. The open locking time is usually much shorter than that of the protected lock.
Open locks may cause write conflicts. However, most databases can detect and allow users or application developers to decide whether their modifications should overwrite those made by other users.

Stored Procedures can accept input values or simply call by name (if they do not require any input values ). Stored Procedures can return a single value without returning information, or multiple values in output parameters. Stored Procedures can even return the entire virtual table to make it more like a view. In fact, you can write a stored procedure to execute any SQL statement previously used for the view.
SQL Server performs syntax analysis of stored procedures when saving stored procedures, and saves stored procedures in an optimal form. Therefore, stored procedures provide a more effective way to execute SQL code (compared to executing the SQL code sent from the customer ). In addition, stored procedures can also be called by name, thus avoiding the need for customers to send all relevant SQL statements to the server.
Stored procedures are created using the T-SQL create procedure statement. Stored procedures are more flexible than views. They can display records in a specific order, return multiple record sets, and even execute database operations (such as starting a backup) that are completely unrelated to the records ).

A trigger is a special type of stored procedure. They are not performed by users, but by the database server when some operations occur on the table:
When a new record is inserted into a table, the insert trigger runs;
When an existing record is deleted from the table, the delete trigger runs;
The update trigger runs whenever the existing records in the table are modified.
Triggers allow the database to automatically respond to user operations. It can also be used as a more complex and flexible form of constraints. The constraints are limited to the information in a single table, and the trigger can access the entire database. SQL Server2005 is beyond the range of traditional triggers and expands them to include other operations, such as modifications to the database or database server architecture.

Event Notification is a new feature in SQL Server2005. It is similar to a trigger, but the actual notification does not execute any code. Instead, event information is sent to the SQL Server Service Broker (BBS) Service and placed in the message queue that another process can read from.

Transactions can be understood as an inseparable modification unit in the database. Each transaction must be either completed or abandoned. Transactions are supported through two mechanisms called commit and rollback. SQL Server also supports distributed transactions. Different operations of distributed transactions are performed on different database servers, but these losses must still be committed or rolled back as a unit.