SQL Server topic Two: Database Main objects

Stored Procedures

(1) Reduce network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement.

(2) Execution speed is faster. There are two reasons: first, when the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.

(3) Stronger adaptability: Because the stored procedure accesses the database through stored procedures, the database developer can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.

(4) Fabric work: The coding of applications and databases can be done independently, without suppressing each other.

Stored procedures can use temporary tables, functions cannot use temporary tables

The stored procedure can use update, and the function cannot use the update

Stored procedures can use functions such as GETDATE (), which functions cannot use GETDATE ().

One, the use of stored procedures to pay attention to some problems

1. Immediately follow a SET NOCOUNT ON clause after as, which reduces the overhead (relative to the presence of DML operations in the process)

2. When creating and referencing the process, add the schema name

3. Avoid using scalar functions (GETDATE ()) in SELECT statements that return many rows of data. Because scalar functions must be applied to each row.

4. Avoid the use of SELECT *. Instead, you should specify the required column name.

5, avoid processing or return "too much" data, some unnecessary data will not be

6. Use an explicit transaction using Begin/end TRANSACTION and keep the transaction as short as possible. Longer transactions mean longer record locks and higher deadlock risk

7. Avoid using wildcards as leading characters in the LIKE clause, such as '%a% '. Because the first character is indeterminate, the query processor cannot use the available indexes. You should use like ' a% ' instead.

8. Use Transact-SQL TRY ... Error handling in the process of CATCH function

9. When creating/modifying table, give column a default value

10. Use if EXISTS (select TOP 1 from table_name) instead of if EXISTS (SELECT * FROM table_name).

11. Use the UNION ALL operator instead of the union or OR operator

12. When you create a stored procedure, you specify the WITH RECOMPILE option in its definition, indicating that SQL Server will not cache the stored procedure plan, and that the stored procedure will be recompiled each time it executes.

The WITH RECOMPILE option is used whenever a different execution plan is created each time a stored procedure's parameter values are significantly different from one execution to another (a person who feels that it should be a dynamic creation of a stored procedure makes it very likely that sometimes the parameters will vary). This option is not commonly used because it must be recompiled each time a stored procedure is executed, which slows down the execution of the stored procedure.

Second, knowledge points:

1. When SQL Server starts, one or more procedures can be performed automatically. These procedures must be created by the system administrator in the master database and executed as a background process with the sysadmin fixed server role. These procedures cannot have any input or output parameters.

2, the process can be nested, and can nest up to 32 levels.

3. The procedure can refer to a table that does not yet exist. At the time of creation, only syntax checking is performed. The procedure is not compiled until the first time it is executed. All objects referenced in the process are parsed only during compilation.

Encrypting stored Procedures

Create procedure FIRSTP

With encryption

As

SET NOCOUNT ON

SELECT * FROM C

Go

EXEC sp_helptext ' dbo. FIRSTP '

Stored Procedure Implementation recursion

SELECT * FROM TB

Order by case when IsNumeric (col) =1 then "else Left (col,1) end,

Cast (case if IsNumeric (col) =0 then stuff (col,1,1, ') Else Col end as int)

DROP TABLE dbo. Example

CREATE TABLE Example (ID int,parentid int)

Insert Example

Select 0,null Union

Select 1,0 Union

Select 2,0 Union

Select 3,1 Union

Select 4,6 Union

Select 5,2

--Take one of the tree structures whose ID is the parent node as follows:

CREATE PROCEDURE display (@ID int) as

Begin

With Example_table (ID,PARENTID,LV)

As

(

--Take root node into temporary table

SELECT id,parentid,0 from Example WHERE [email protected]

--recursively take data from its byte points according to the data that has been taken

UNION All

SELECT a.id,a.parentid,b.lv+1 from Example A INNER joins example_table B on a.parentid = b.ID

)

SELECT * from Example_table

End

DROP table TB

Go

Custom functions

User-defined custom functions return scalar values like built-in functions, or you can return a result set with a table variable

Types of user-defined functions:

Scalar function: Returns a scalar value

Table-valued Function {Inline table-valued function, multi-table-valued Function}: Returns a rowset (that is, multiple values are returned)

1. Scalar functions

Create function name (parameter)

Returns return Value data type

[With {encryption | Schemabinding}]

[As]

Begin

SQL statement (must have return variable or value)

End

Schemabinding: Binds a function to the object it refers to (note: Once a function is bound, it cannot be deleted, modified, unless the binding is removed)

Create function Avgresult (@scode varchar (10))

Returns Real

As

Begin

Declare @avg Real

Declare @code varchar (11)

Set @[email protected] + '% '

Select @avg =avg (result) from Learnresult_baijiali

Where SCODE like @code

Return @avg

End

Execute user Custom Function

Select User name. Function name as field alias

SELECT dbo. Avgresult (' s0002 ') as result

User-defined function return values can be placed in local variables, with set, Select,exec assignment

Declare @avg1 Real, @avg2 real, @avg3 Real

Select @avg1 = dbo. Avgresult (' s0002 ')

Set @avg2 = dbo. Avgresult (' s0002 ')

EXEC @avg3 = dbo. Avgresult ' s0002 '

Select @avg1 as AVG1, @avg2 as AVG2, @avg3 as Avg3

Function reference

Create function code (@scode varchar (10))

Returns varchar (10)

As

Begin

DECLARE @ccode varchar (10)

Set @scode = @scode + '% '

Select @ccode =ccode from Cmessage

where Ccode like @scode

Return @ccode

End

Select name from class where Ccode = Dbo.code (' c001 ')

2. Table-valued function

A, inline table-valued functions

Format:

Create function name (parameter)

Returns table

[With {encryption | Schemabinding}]

As

return (an SQL statement)

Create function tabcmess (@code varchar (10))

Returns table

As

Return (select Ccode,scode from Cmessage where ccode like @ccode)

b, multiple-sentence table-valued functions

Create function name (parameter)

Returns table variable name table (table variable definition)

[With {encryption | Schemabinding}]

As

Begin

SQL statements

End

A multiple-sentence table-valued function contains multiple SQL statements, at least one of which is populated with data values in a table variable

Table variable format

Returns @ variable name table (column definition | constraint definition [,...])

You can perform select,insert,update,delete on rows in a table variable, but the result set of the SELECT INTO and INSERT statements is inserted from the stored procedure.

Create function Tabcmessalot (@code varchar (10))

Returns @ctable Table (code varchar (null,cname) varchar (+) NULL)

As

Begin

Insert @ctable

Select Ccode,explain from Cmessage

Where SCODE like @code

Return

End

Select * from Tabcmessalot (' s0003 ')

Trigger

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So triggers can be used to implement complex integrity constraints on a table.

Two: SQL Server creates two dedicated tables for each trigger: the inserted table and the deleted table. These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. The two tables associated with the trigger are also deleted after the trigger execution is complete.
The deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement.
The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution.
Three: Instead of and after triggers
SQL Server2000 provides two types of triggers: Instead of and after triggers. The difference between the two triggers is that they are activated in the same way:
Instead of triggers are used to replace T-SQL statements that cause trigger execution. In addition to tables,instead of triggers can also be used for views that extend the update operations that the view can support.
After triggers are executed after a insert,update or deleted statement, and actions such as constraint checking occur before the after trigger is activated. After triggers can only be used for tables.
Each modification action (insert,update and delete) of a table or view can have a instead of trigger, and each modification action of a table can have more than one after trigger.
Four: The execution of the trigger process
If a insert﹑update or DELETE statement violates the constraint, then the after trigger does not execute because the check for the constraint occurs before the after trigger is agitated. So after triggers cannot go beyond constraints.
The Instead of trigger can be executed in place of the action that fired it. It is executed when the inserted table and the deleted table have just been created and no other operations have occurred. Because the instead OF trigger executes before the constraint, it can perform some preprocessing on the constraint.
V: Use T-SQL statements to create triggers
The basic statements are as follows:
Create TRIGGER Trigger_name
on {table_name | view_name}
{for | After | Instead of}
[Insert, Update,delete]
As
Sql_statement
VI: Delete trigger:
The basic statements are as follows:
Drop Trigger Trigger_name
Seven: View the existing triggers in the database:
--View the database already has a trigger
Use Jxcsoftware
Go
SELECT * from sysobjects where xtype= ' TR '
--View a single trigger
EXEC sp_helptext ' trigger name '
Eight: Modify the trigger:
The basic statements are as follows:
Alter TRIGGER Trigger_name
on {table_name | view_name}
{for | After | Instead of}
[Insert, Update,delete]
As
Sql_statement
Nine: Related examples:
1: When a trigger is established in the Orders table, when an order record is inserted into the Orders table, the item status status of the goods table is checked for 1 (being collated), yes, it cannot be added to the Orders table.
Create Trigger OrderInsert
On orders
After insert
As
if (select status from goods,inserted
where Goods.name=inserted.goodsname) =1
Begin
print ' The goods is being processed '
print ' The order cannot be committed '
ROLLBACK TRANSACTION--rollback, avoid joining
End
2: Create an INSERT trigger in the Orders table to reduce inventory in the corresponding item record in the goods table when an order is added.
Create Trigger Orderinsert1
On orders
After insert
As
Update goods Set storage=storage-inserted.quantity
From goods,inserted
where
Goods.name=inserted.goodsname
3: Create a DELETE trigger on the goods table to implement cascading deletions of the goods table and the Orders table.
Create Trigger Goodsdelete
On goods
After delete
As
Delete from Orders
where Goodsname in
(select name from deleted)
4: Create an UPDATE trigger on the Orders table to monitor the order date (OrderDate) column of the Orders table so that it cannot be modified manually.
Create Trigger Orderdateupdate
On orders
After update
As
If Update (OrderDate)
Begin
RAISERROR (' OrderDate cannot be modified ', 10, 1)
ROLLBACK TRANSACTION
End
5: Create an INSERT trigger on the Orders table to ensure that the name of the item to be inserted into the Orders table must exist in the goods table.
Create Trigger Orderinsert3
On orders
After insert
As
if (select COUNT (*) from goods,inserted wheregoods.name=inserted.goodsname) =0
Begin
print ' No entry in goods for this order '
ROLLBACK TRANSACTION
End
The 6:orders table establishes an INSERT trigger that ensures that the item information inserted into the Orders table is added to the order table
Alter TRIGGER AddOrder
On Orders
For insert
As
INSERT INTO Order
Select inserted. Id, inserted.goodname,inserted. Number from inserted

Cursor

Cursors for SQL Server are useful when you need to do a single processing of the recordset.

Select Returns a Recordset, but you want to do a single processing based on the different circumstances of each record, when the usefulness of the cursor appears.

A cursor can be thought of as a pointer to a row in the result set (Aset of rows) (pointer). Cursors can only point to one row per point in time, but may point to other rows in the result set as needed.

For example: select* from Employees where sex= ' M ' will return all sexes as male employees, at the initial time, the cursor is placed in front of the first row in the result set. Causes the cursor to point to the first row to perform a fetch. When the cursor points to a row in the result set, the row of data can be processed, to get the next row of data, to continue the fetch. The fetch operation can be executed repeatedly until all rows in the result set are completed

There are several steps to using cursors in stored procedures:

Declares a cursor, opens a cursor, takes one row at a time, tells the data that the cursor is pointing to the local variable (local variables), closes the cursor at the end

There are five steps to creating a cursor:

CREATE PROCEDURE [dbo]. [Proc_syn_data]

As

BEGIN

SET NOCOUNT on;

DECLARE @ID INT;

TRUNCATE TABLE Table2;

--1. Declaring cursors

DECLARE Cur_monthid CURSOR for

SELECT ID from [Table1]

--2. Open cursor

OPEN Cur_monthid

--3. Find information from a cursor and implement your own data processing.

FETCH Cur_monthid into @ID

While @ @FETCH_STATUS =0

BEGIN

INSERT into [Table2]

SELECT * FROM [Table1] TM WHERE [email protected]

FETCH NEXT from Cur_monthid to @ID

END;

--4. Close cursor

CLOSE Cur_monthid;

--5. Releasing cursors

Deallocate Cur_monthid;

END

SQL Server topic Two: Database Main objects