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

Blog catalogue

I. Index

II. View

III. Stored procedures

IV. System stored procedure

Flip-flop

VI. Affairs

7. Lock

I. Index

The index provides pointers to the data values stored in the specified column in the table, then arranges the pointers in the specified order, and then follows the pointer to the column that contains the value.

1. What is an index

The index in the database is similar to the catalog in the book. In a book, you do not need to read the whole book, you can use the catalogue to quickly find the information you need. In a database, an index enables a database program to find the data it needs without scanning the entire table. The catalogue in the book is a list of words with the page number containing each word. An index in a database is a collection of one or more column values in a table, as well as a list of logical pointers that physically represent these worthy data industries.

Indexing is an internal method for SQL Server to orchestrate data, which provides a way for SQL Server to route query data.

An index page is a data page that stores an index in a database. The index page stores the keyword page that retrieves the data row and the address pointer to the data row. By using the index, the retrieval speed of the database can be greatly improved and the performance of the database can be improved.

2. Index classification 1) unique index

A unique index does not allow two rows to have the same index value.

If there are duplicate key values in existing data, most databases generally do not allow unique indexes to be created. When the new data duplicates the key values in the table, the database also refuses to receive the data. When a unique constraint is created, a unique index is automatically created. Although unique indexes help you find information, it is recommended that you use primary key constraints for best performance.

2) Primary key index

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index.

The primary index requires that each value in the primary key be unique. It also allows quick access to data when a query uses a primary key index.

3) clustered index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. Only one clustered index can be contained in a table.

4) Nonclustered index

The nonclustered index is built on the index page, and the location of the record can be found in the index when querying the data.

A nonclustered index makes the physical order of the rows of data in the table mismatch with the logical order of the key values. Clustered indexes have faster data access than nonclustered indexes. In SQL Server, only one clustered index can be created for a table, but there can be multiple nonclustered indexes. Set a column primary key, and the column defaults to the clustered index.

5) compound index

When you create an index, you can not only create an index on one of the columns, but like a primary key, you can combine multiple columns as an index, which is called a composite index.

It is important to note that the composite index is used only when the first column or the entire composite index column is used as a condition to complete the data query.

6) full-text indexing

Full-text index is a special type of tag-based functional index, which is created and maintained by the full-text engine service in SQL Server.

Full-text indexing is mainly used to search for strings in a large amount of text, and the efficiency of using full-text indexing will be much higher than that of using T-SQL 's LIKE keyword. Because the process of creating a full-text index is very different from other types of indexes.

3. Create and use indexes

There are two ways to create an index: using SSMS and T-SQL statements.

Create an index using SSMS, as follows:

1) create an index by the name column in the products table

2) right-click the name and click the index / key

3) add an index with the name IX_name, and click close to save the table.

4) use index to create query select * from products with (index=IX_name) where name = 'cucumber'

Although you can specify which index SQL Server will query for data, you generally do not need to specify it manually. SQL Server will automatically optimize the query based on the index created.

Using indexes can speed up data retrieval, but it is not necessary to index each column. Because the index itself needs to be maintained and takes up some resources, the indexed columns can be selected according to the following criteria.

Frequently searched columns

Columns that are often used for query selection

Frequently sorted, grouped columns

Columns that are often used to join (primary / foreign keys)

Do not create an index using the following columns:

Columns that contain only a few different values

The table contains only a few rows; second, views

A view is an select query saved in a database. Therefore, most of the operations specified on the query can also be done on the view. There are two reasons for using the view, one is for security considerations, users do not have to see the entire database structure, but hide part of the data; the other is in line with the user's daily business logic, making it easier to understand the data.

1. What is a view

A view is another way to view data in one or more tables in a database. A view is a virtual table, usually created as a subset of rows or columns from one or more tables. Of course, the view can also contain all rows and columns. However, a view is not a collection of data values stored in the database, its rows and columns come from the tables referenced in the query. At execution time, the view displays data directly from the table.

The view acts as a filter for the table specified in the query. Queries that define views can be based on one or more tables, other views, the current database, or other databases.

As shown in the following figure, take table T and table T1 as an example, the view can contain all or selected columns in these tables. The following figure shows a view created with columns An and B of table T and B1, C1, and D1 columns of table T1:

Views are typically used for the following three operations:

Filter rows in a table

Prevent unauthorized users from accessing sensitive information

Abstract multiple physical data tables into a logical data table; 1) the benefits of using views

Benefits for end users:

The results are easier to understand; it is easier to obtain data.

Benefits for developers:

It is easier to restrict data retrieval; it is more convenient to maintain applications; 2. Create and use views 1) create views

In SQL Server, there are two ways to create views: using SSMS and using T-SQL statements.

① expand the database test, as shown in the figure, right-click "View" and select "New View" from the pop-up shortcut menu (create multiple tables to insert data)

② adds three tables A, B, C

③ selects the columns you want to see: student name, student age, score, and then automatically generates a T-SQL statement at the bottom of the middle, and then press the "crtl+R" shortcut to execute the statement.

④ select the T-SQL statement and press the "crtl+R" shortcut key to execute it.

2) considerations for using views

Multiple tables can be used per view

Similar to a query, one view can nest another, preferably no more than three layers

The select statement in a view definition cannot contain the following:

Order by clause, except for sub-ah, there is also a TOP clause in the select list of select statements

Into keyword

Reference temporary tables or table variables; III. Stored procedures

SQL Server uses stored procedures to avoid the security risks of sending and executing SQL code remotely.

1. Why do you need stored procedures

Today's software is mostly used in the network, while the data used by general applications is saved in the database. In database applications that do not use stored procedures, most users send requests written by SQL code to the server from the local extreme and the client through the network. The server compiles the received SQL code and executes it, and sends the specified results back to the client, which is then processed by the application software of the client and output. If the developer does not consider the security of the server comprehensively, it will provide the illegal person with the opportunity to steal data. As shown in the following figure:

The unauthorized illegal person intercepts the SQL code sent by the user to the server in the network, and the rewritten malicious SQL code is submitted to the server for compilation and execution. finally, the illegal person can easily get the data he needs.

From the figure, we can see that the process performed by the application is not secure, mainly in the following aspects:

Data insecurity: SQL code is transmitted over the network and is easily intercepted by unauthorized users.

Every time the SQL code is submitted, it is compiled by syntax and then executed, which affects the running performance of the application.

The network traffic is heavy. For the same SQL code that is executed repeatedly, it will be transmitted many times on the network, increasing the network traffic.

In order to solve these problems, we can use stored procedures to precompile the SQL code for database operations and save them on the server. Users only need to input the name of the stored procedure to be executed and the necessary data on the local computer to directly call the stored procedure to complete the operation of the row management. Like this. It not only reduces the network traffic, but also ensures the running performance of the application, but also prevents unauthorized people from trying to intercept SQL code.

2. What is a stored procedure

Stored procedures are precompiled collections of SQL statements and control statements, saved in the database, can be called and executed by applications, and allow users to declare variables, logical control statements and other powerful programming functions.

Stored procedures can contain logical control statements and data operation statements that can receive parameters, output parameters, return single or multiple result sets, and return values.

1) advantages of using stored procedures:

The advantages of stored procedures are:

Modular programming

Fast execution speed and high efficiency

Reduce network traffic

Has good security; 2) stored procedures are divided into the following two types of system stored procedures; user-defined stored procedures; fourth, system stored procedures

SQL Server provides system stored procedures, which are a set of precompiled T-SQL statements. System stored procedures provide a mechanism for managing databases and updating tables and act as a shortcut to retrieve information from system tables.

By configuring SQL Server, you can generate information and definitions about objects, users, and permissions, which are stored in system tables. Each database has a set of system tables containing configuration information. The system tables of the user database are created automatically when the database is created, and users can access and update the system tables through the system stored procedure.

1. Commonly used system stored procedures

The name of the system stored procedure for SQL Server begins with "sp-" and is stored in the Resource database. The system administrator has permission to use these stored procedures. You can run system stored procedures in any database, but the results of execution are reflected in the current database.

Examples are as follows:

Exec sp_databases;exec sp_help A _ Exec sp_helpdb;exec sp_renamedb 'xsh','benet';exec sp_helpconstraint products;exec sp_helpindex products

There are many output result sets in the above example, so do not enumerate them one by one here. Please run them one by one to see the corresponding output results.

2. Commonly used extended stored procedures

According to the different functions of system stored procedures, system stored procedures can be divided into different categories. Extended stored procedure is one of the various system stored procedures provided by SQL Server.

Extended stored procedures allow external stored procedures to be created in other programming languages, such as the client # language, to provide database users with an interface from SQL Server instances to external programs for various maintenance activities. It usually starts with "xp_" and exists alone in the form of DLL.

A commonly used extended stored procedure is xp_cmdshell, which can perform some operations under the DOS command, such as creating folders, listing folders, and so on. The syntax is as follows:

Enable the cmdshell function of the system

Exec sp_configure 'show advanced options',1reconfigureexec sp_configure' xp_cmdshell',1reconfigure;exec xp_cmdshell' mkdir D:\ test', no_output

Exec xp_cmdshell 'dir d:\'

Flip-flop

A trigger is a special type of stored procedure that is automatically called when the data in the table is updated in response to INSERT, UPDATE, or DELETE statements.

1. What is a trigger? 1) the concept of a trigger

Triggers are stored procedures that are executed automatically when inserting, updating, or deleting a table. Triggers, which are often used to enforce business rules, are advanced constraints that can define more complex constraints than CHECK constraints, can line miscellaneous SQL statements (such as IF/WHILE/CASE), and reference columns in other tables. Triggers are mainly triggered by events, while stored procedures can be called directly through the name of the stored procedure. When a table is modified, such as UPDATE, INSERT, and DELETE, SQL Server automatically executes the SQL statements defined by the trigger, ensuring that the data must be processed in accordance with the rules defined by these SQL statements. This trigger can be divided into the following categories:

INSERT trigger: triggered when data is inserted into the table and automatically executes the SQL statement defined by the trigger; UPDATE trigger: triggered when updating a column or multiple columns in the table, automatically executes the SQL statement defined by the trigger; DELETE trigger: triggered when the record in the table is deleted, automatically executes the SQL statement defined by the trigger. 2) deleted table and insertd table

2. The function of trigger

The main function of triggers is to achieve complex referential integrity and data consistency that cannot be guaranteed by primary and foreign keys.

3, the function of the trigger strengthens the constraint; track the change; cascade operation; 4. How to create the trigger

The syntax for creating a trigger using the T-SQL statement is as follows:

CREATE TRIGGER trigger_nameON table_name [WITH ENCRYPTION] FOR {[DELETE, INSERT, UPDATE]} AS SQL statement

The following issues need to be noted when creating triggers:

CREATE TRIGGER must be the first statement in the batch and can only be applied to one table

Triggers can only be created in the current database, but triggers can refer to external objects in the current database

In the same CREATE TRIGGER statement, you can define the same trigger operation for multiple user operations (such as INSERT and UPDATE). Transaction 1. What is a transaction?

A transaction is a mechanism, an operation sequence, which contains a set of database operation commands, and all commands are submitted or revoked to the system as a whole, that is, this set of database commands are either executed or not executed. Therefore, the transaction is an inseparable working logic unit, and when performing concurrent operations on the database system, the transaction is used as the smallest control unit, which is especially suitable for multi-user simultaneous operation of the database system.

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four attributes, namely atomicity, consistency, isolation, and persistence, which are often referred to as ACID for short.

1) atomicity

A transaction is a complete operation. The elements of a transaction are indivisible (atomic). All elements in the transaction must be committed or rolled back as a whole. If any element in the transaction fails, the entire transaction will fail.

2) consistency

When the transaction completes, the data must be in a consistent state. That is, the data stored in the database is in a consistent state before the transaction begins. In an ongoing transaction, the data may be in an inconsistent state, such as partial modification of the data. However, when the transaction completes successfully, the data must return to its known consistent state again. Changes made to the data through the transaction cannot damage the data, or the transaction cannot make the data storage unstable.

3) isolation

All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not depend on or affect other transactions in any way. A transaction that modifies data can access the data before another transaction that uses the same data starts, or uses the same data in another transaction, and the modification to the data will not take effect until the transaction is successfully committed.

4) persistence

The persistence of a transaction means that the result of a transaction is permanent regardless of whether the system fails or not.

2. Syntax for executing transactions 1) start transaction syntax: BEGIN TRANSACTION2) commit transaction syntax is as follows: COMMIT TRANSACTION3) rollback (undo) transaction syntax is as follows: ROLLBACK TRANSACTION

Updates to the database by the SQL statement following the BEGIN TRANSACTION statement are recorded in the transaction log until an ROLLBACK TRANSACTION statement or a COMMIT TRANSACTION statement is encountered. If an operation in a transaction fails and the ROLLBACK TRANSACTION statement is executed, all updated data after the BEGIN TRANSACTION statement can be rolled back to the state it was before the transaction started. If all the operations in the transaction are completed correctly and the updated data is submitted to the database using the COMMIT TRANSACTION statement, then the data is in a new consistent state.

Lock 1. What is a lock

Multiple users can manipulate the data in the same database at the same time, which will lead to data inconsistency. That is, if there is no lock and multiple users access a database at the same time, problems may occur when the transaction uses the same data at the same time. These problems include loss, update, dirty reading, unrepeatable reading and hallucination reading. Database locking is to solve the above problems.

2. The mode of lock

Shared lock (S lock): used to read the lock added by the resource

Exclusive lock (X lock): incompatible with any other lock, including other exclusive locks, exclusive locks for data modification

Update lock (U lock): U lock can be regarded as a combination of S lock and X lock, which is used to update data. When updating data, you first need to find the updated data, which can be understood as S lock on the found data. When you find the data that needs to be modified, you need to put an X lock on the modified resource. SQL Server uses U locks to avoid deadlock problems. 3. Ways to view locks

Use sys.dm_tran_locks to dynamically manage views

Use profiler to capture lock information; 4. Conditions for deadlock formation

The essence of deadlock is a kind of deadlock, which is caused by multiple agents' contention for resources. To understand deadlocks in SQL Server, you can refer to the following figure:

The following four necessary conditions are required for deadlock to occur:

Mutually exclusive condition

Request and wait conditions

Do not deprive conditions

Loop waiting conditions; 5. Prevention of deadlock

To prevent deadlock is to destroy one or more of the four necessary conditions so that it cannot form a deadlock. The common methods are as follows:

Break the mutually exclusive condition

Break request and wait conditions

Destroy the non-deprivation condition

-this is the end of this article. Thank you for reading-

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.