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

one。 Trigger

Triggers are special stored procedures that implement complex data integrity and automatically trigger execution when update, insert, or delete statements are executed against a table or view to prevent incorrect, unauthorized, or inconsistent parameters on the data.

/ * create a trigger * / creat [temp | temporary] trigger name [before | after] [insert | update | delete of columns] on tablebeginSQL statement end

Triggers maintain referential integrity between two tables, just like foreign keys. Foreign keys perform this task more efficiently because they are tested before the data changes, unlike triggers that trigger after the data changes.

two。 Indexes

(1) definition:

An index is one of the objects in a database. An index is a decentralized storage structure created to accelerate the retrieval of data rows in a table.

An index is built on a table and is made up of index pages other than data pages

Create unique clustered index book_id_index-- uniqueness clustering index on book (book_id asc) with fillfactor=50 / * fill factor 50% percent /

(2) Classification of indexes

Clustering index

The physical order of the data table is the same as that of the index table, which arranges records according to the combination of one or more column values in the table

Create unique clustered index book_id_index-- uniqueness clustering index on book (book_id asc) with fillfactor=50 / * fill factor 50% percent /

b. Non-clustered index

Create nonclustered index student_course_indexon student_course (student_id asc,course_id asc) with fillfactor=50

three。 Business

(1) definition:

A transaction refers to a sequence of operations, which are either executed or not executed. It is an indivisible unit of work.

(2) grammatical steps:

Transaction start: BEGIN TRANSACTION

Transaction commit: COMMIT TRANSACTION

Transaction rollback: ROLLBACK TRANSACTION

Determine whether there is an error in the execution of a statement: global variable @ @ ERROR

@ @ ERROR can only judge whether there are errors in the execution of a current T-SQL statement. In order to determine whether there are errors in all T-SQL statements in a transaction, we need to accumulate the errors; for example: SET @ errorSum=@errorSum+@@error

four。 Stored procedure

(1) definition:

A stored procedure is a series of pre-edited SQL code sets that can perform specific data manipulation functions. It is associated with a specific database and stored on the SQL Server server.

(2) benefits of stored procedures:

Reuse. Stored procedures can be reused to reduce the workload of database developers

Improve performance. Stored procedures are compiled when they are created and need not be compiled when they are used in the future. General SQL statements are compiled once at a time, so using stored procedures improves efficiency

Reduce network traffic. The stored procedure is located on the server, and you only need to pass the name and parameters of the stored procedure when calling, thus reducing the traffic of network transmission.

Security. Parameterized stored procedures prevent SQL injection from * *, and can apply Grant, Deny, and Revoke permissions to stored procedures.

Define a stored procedure:

Create proc spAddStudents @ name nvarchar (50) = null as begin transaction-- transaction insert into [StudentInfo]. [dbo]. [Students] (Name) values (@ name) if @ @ ERROR0 begin rollback tran return end

Create a stored procedure that implements the addition calculation and takes the operation result as the output parameter

Create proc spAdd @ value1 int, @ value2 int, @ result int output as select @ result=@value1+@value2 go

Execute spAdd stored procedures

Declare @ value1 int declare @ value2 int declare @ result int set @ value2=1 exec spAdd @ value1,@value2,@result output print convert (char (5), @ value1) +'+'+ convert (char (5), @ value2) +'='+ convert (char (5), @ result)

Reference: http://www.xuebuyuan.com/2041347.html

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.