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

Indexes, stored procedures and triggers can process and query some advanced data, so as to better realize the operation, diagnosis and optimization of the database.

one。 Indexes

The index provides a pointer to the specified data value stored in the table, and the index of the database is similar to the catalog in a book. You can quickly find the required information without reading the whole book. In a database, an index enables a database program to find the data it needs without scanning the entire table. By using the index, the query speed of the database can be greatly improved.

(1) Index classification

1. Unique index

Unique index does not allow two rows to have the same index value

two。 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

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

4. Nonclustered index

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

5. Composite index

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

(2) create and use indexes

1. First, create a table with a large amount of data, called "Student Table", with three columns, student number, name and class, as shown in the following figure, the student number is automatically numbered, and the class is the default value "Class one".

2. Insert a large amount of data into the table, the more data, the better the effect of verifying the index.

Complete with sentence: While 1 > 0 Insert into t388 (name) values ('Xiao Ming')

The above statement is an endless loop, unless forced to end, if 1 is greater than 0, the name will always be inserted into the table

As shown in the following figure:

3. Wait for about 5 minutes. Open the properties of the table and view the number of rows of the table. The current number is 1, as shown in the following figure: 713179

4. Use the statement to query the data of 600000 rows, Select * from t388 Where student number = 600000

5. Open the "sql server profiler" tool to track, as shown in the following figure:

Open the "sql server profiler" tool to view the tracking information and find that the query time is very long. Cpu worked 188ms, reads: 6614 times, writes: written 9 times, duration: a total of 2977 milliseconds to complete the query.

6, start creating the index (unique index)

Click OK to complete the creation, and then use the statement to query the data of row 600020 again. Select * from t388 Where student number = 600020

This is the only index creation. The primary key index does not need to be created. The column is set as the primary key of the table and the primary key index is generated automatically.

Second, storage type

(1) stored procedures are precompiled collections of sql statements and control statements, which are stored in the database and can be executed by applications, and allow users to declare variables, logical control statements and powerful programming functions.

Benefits of using stored procedures:

1. Modular programming

two。 Fast execution speed and high efficiency

3. Reduce network traffic

4. Has good security.

(2) system stored procedure

Sql-server provides many system stored procedures, which are a set of precompiled T-SQL statements. System stored procedures provide a mechanism to manage databases and update tables, and act as a shortcut to retrieve messages from system tables.

Commonly used system stored procedures

Sp_database lists information about all databases on the server, including database name and data size

Sp_helpdb reports information about the specified database or all databases

Sp_renamedb changes the name of the database

Sp_tables returns information about tables and views that can be queried in the current environment

Sp_columns returns column information for a table and view, including the data type and length of the column, etc.

Sp_help looks at information about a database object, such as column name, primary key, constraint, foreign key, index, etc.

Sp_helpconstraint looks at the index of a table

Sp_stored_procedures displays a list of stored procedures

Sp_password adds or modifies the password of the login account

Sp_helptext displays the actual text of default values, unencrypted stored procedures, user-defined stored procedures, triggers, or views

The syntax for invoking the execution stored procedure using the T-SQL statement is as follows:

EXEC stored procedure name [parameter value]

The abbreviation of execute in exec

(3) use system stored procedures to query the database size of sp_database as an example, all of the above are executed in the following format

(4) extended stored procedure

Syntax: EXEC xp_cmdshell DOS command {no_output}

If xp_cmdshell is turned off as part of the server security configuration, turn it on with the following statement:

Exec sp_configure 'show advanced options',1-displays advanced configuration information

Go

Reconfigure-reconfiguration

Go

Exec sp_configure 'xp_cmdshell',1

Go

Reconfigure-reconfiguration

Go

Take creating a bene folder on disk C as an example

Exec xp_cmdshell 'mkdir c:\ bene',no_output

(5) Custom stored procedure

In sql-server, the sql statement used to create ubiquitous procedures is create procedure, and all stored procedures are stored in the current database. A complete stored procedure consists of the following three parts

1. Input and output parameters

two。 T-SQL statements executed in stored procedures

3. The return value of the stored procedure

Custom stored procedure

Taking the programming of network management average score storage procedure as an example

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name = 'usp_GetAverageResult')

DROP PROCEDURE usp_GetAverageResult

GO

CREATE PROCEDURE usp_GetAverageResult

AS

DECLARE @ subJectID nvarchar (4)

SELECT @ subJectID=subJectID FROM dbo.TSubject WHERE subJectName=' Network Management'

DECLARE @ avg decimal (18pd2)

SELECT @ avg=avg (mark) from dbo.TScore where subJectID=@subJectID

The average score of PRINT 'network management major is:' + CONVERT (VARCHAR (5), @ avg)

Go

The above code mainly understands the syntax of creating stored procedures, and the variables and judgment statements involved do not need to be studied in depth, as long as they can be understood according to the sentences. If you are interested, you can view the materials and self-study.

Third, trigger

A trigger is a special stored procedure that is automatically called when the data in the table is updated in response to the INSERT,UPDATE,DELETE statement

(1) trigger type

INSERT trigger: automatically executes the defined statement when inserting data into the table is triggered

UPDATE trigger: automatically executes the defined statement when updating a column in a table with multiple columns

DELETE trigger: triggered when a table is deleted, automatically executes the defined statement

(2) create a trigger

Create using the T-SQL statement

Statement: CREATE TRIGGER [trigger name]

ON [tables that need to create triggers]

FOR ([DELETE,INSERT,UPDATE)

AS SQL statement

For example, when someone tries to change data in a table, a message is prompted and the operation is blocked, the following statement can be implemented:

Create trigger reminder-defines the trigger name as "reminder"

On dbo.TScore-in which table is executed, this example is in the "dbo.Tscore" table

For UPDATE-specifies that triggers are activated when those data modification statements are executed in the table. Multiple, delimited, can be specified. This example is "UPDATE"

As

Print 'forbids modification, please contact DBA'-the text displayed when triggered

ROLLBACK TRANSACTION

GO

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.