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

Theoretical Knowledge: Start

I. TRUNCATE

Quickly delete all rows from a stack of tables. It has the same effect as unconditional Delete on each table, but is much faster because it doesn't do a table scan. Most useful on large watches.

II. Select INTO table

Copy data from one table to another.

3. Insert INTO Select

IV. Supplement: provisional table

Temporary tables are stored in the system database tempdb

Temporary tables are implicitly discarded by the system

V. Storage process (**)

I. Introduction:

Stored Procedure is a set of SQL statements to accomplish a specific function. The set is compiled and stored in the database. The user specifies the name of the stored procedure and gives parameters. If the stored procedure has parameters to execute it,

In successive releases of SQL Server, stored procedures fall into two categories: system-provided stored procedures and user-defined stored procedures.

The system SP, which is stored primarily in the master database and prefixed with sp_, and the system stored procedures primarily retrieve information from system tables to manage SQL Server for system administrators. User-defined stored procedures are stored procedures created by users and capable of performing a specific function, such as querying the data information required by users.

Stored procedures have the following advantages:

1. Stored procedures allow standard component programming (modular design)

After a stored procedure is created, it can be invoked multiple times in a program without having to rewrite the SQL statement for the stored procedure, and database professionals can modify the stored procedure at any time without affecting the application source code. Because the application source code contains only stored procedure invocation statements, it greatly improves the portability of the program.

2. Stored procedures enable fast execution speeds

If an operation contains a large amount of Transaction-SQL code, or is executed multiple times, then stored procedures are much faster than batch execution, because stored procedures are precompiled, and when a stored procedure is run for the first time, the query optimizer analyzes and optimizes it, and gives the execution plan that is finally stored in the system table. Batch Transaction-SQL statements are compiled and optimized at each run, so they are relatively slow.

3. Stored procedures reduce network traffic

For the same operation against database objects, such as query modification, if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is invoked on the client computer, only the invocation statement is transmitted in the network, otherwise it will be multiple SQL statements, thus greatly increasing network traffic and reducing network load.

4. Stored procedures can be used as a security mechanism to take full advantage of system administrators by restricting the permissions to execute a stored procedure, thereby enabling restrictions on corresponding data access permissions.

II. Variables

@I

3. Flow control statement (if else)| select case | while )

Select ... CASE Example

DECLARE @iRet INT, @PKDisp VARCHAR(20)

SET @iRet = '1'

Select @iRet =

CASE

WHEN @PKDisp = 'ONE' THEN 1

WHEN @PKDisp = 'TWO' THEN 2

WHEN @PKDisp = 'THEN 3'

WHEN @PKDisp = 'four' THEN 4

WHEN @PKDisp = 'five' THEN 5

ELSE 100

END

IV. Format of stored procedure

create a stored procedure

Create Proc dbo. Stored procedure name

stored procedure parameters

AS

execute statements

RETURN

execute stored procedures

GO

Theoretical Knowledge: End

Syntax Example: Start

--declaration of variables,sql declaration of variables must be preceded by @ symbol

DECLARE @I INT

--variable assignment, variable assignment must be preceded by set

SET @I = 30

--Declare multiple variables

DECLARE @s varchar(10),@a INT

--if statement in Sql

IF CONDITION BEGIN

execute statements

END

ELSE BEGIN

execute statements

END

DECLARE @d INT

set @d = 1

IF @d = 1 BEGIN

--Print

PRINT 'correct'

END

ELSE BEGIN

PRINT 'Error'

END

--Multiple conditional selection statements in Sql.

DECLARE @iRet INT, @PKDisp VARCHAR(20)

SET @iRet = 1

Select @iRet =

CASE

WHEN @PKDisp = 'ONE' THEN 1

WHEN @PKDisp = 'TWO' THEN 2

WHEN @PKDisp = 'THEN 3'

WHEN @PKDisp = 'four' THEN 4

WHEN @PKDisp = 'five' THEN 5

ELSE 100

END

--loop statement

WHILE CONDITION BEGIN

execute statements

END

DECLARE @i INT

SET @i = 1

WHILE @i

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.