Database-related SQL Server (i)

server| Data | Database 1. 1. Advanced query Statement
1, TRUNCATE TABLE [table_name]

Deletes all rows in a table without recording a single row delete operation.

TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.

The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data pages used to store the table data, and only records the release of the page in the transaction log.

The count value used for the new line identification is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead.

For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, but you should use a DELETE statement without a WHERE clause. Because the TRUNCATE TABLE is not logged in the log, it cannot activate the trigger.

2, Sp_databases

Returns the database listed in the sysdatabases system table.

Sp_databases There is no equivalent procedure in open Database Connectivity (ODBC).

3, Sp_datatype_info

Sp_datatype_info is equivalent to SQLGetTypeInfo in ODBC. The results are sorted by data_type, and then by data type mapping to the degree of tightness of the corresponding ODBC SQL data type.

4, Sp_columns

Returns the column information for the specified table or view that can be queried in the current environment.

SP_COLUMNS [table_name]

Or:sp_columns @table_name = ' [table_name] ', @column_name = ' [column_name] '

5, Sp_tables

Usage ditto, no longer Reshu.
1. 2. Stored Procedure
All well-designed microsoft®sql server™2000 applications should use stored procedures. This should be true regardless of whether the application's business logic is written to the stored procedure. Even standard Transact-SQL statements without business logic components can gain performance gains after being packaged into stored procedures with parameters. Transact-SQL statements compiled into stored procedures can save a lot of processing when executed.
1. 2. 1 design rules for stored procedures
The L CREATE PROCEDURE definition itself can include any number and type of SQL statements other than the following CREATE statements, and the following statements cannot be used anywhere in the stored procedure:

CREATE DEFAULT

CREATE TRIGGER

CREATE PROCEDURE

CREATE VIEW

CREATE rule



L The maximum number of parameters in a stored procedure is 2100.

L can refer to a temporary table within a stored procedure.

L If you create a local temporary table within a stored procedure, the temporary table exists only for that stored procedure, and the temporary table disappears when you exit the stored procedure.
1. 2. 2 Creating a Stored procedure
Example:

CREATE PROCEDURE [Pr_insert_casebrief_new]

@p_Casebrief_code as varchar, @p_Object_id As Integer, @p_Spy_starttime as DateTime,

@p_Spy_endtime as DateTime, @p_Casebrief_id as Integer out

As

Insert into case_brief_telecom (Casebrief_code,object_id,spy_starttime,spy_endtime,)

VALUES (@p_Casebrief_code, @p_Object_id, @p_Spy_starttime, @p_Spy_endtime)

Select @p_Casebrief_id = @ @identity

Go

P_CASEBRIEF_ID is automatically grown for the primary key of the table.

L microsoft®sql server™2000 stored procedures return data in four ways:

1, output parameters, can return data (integer value or character value, etc.), you can return the cursor variable (the cursor is a result set that can be retrieved line by row).

2, return code, always an integer value.

3, the result set of the SELECT statement, which is contained within the stored procedure or any other stored procedure that is invoked by the stored procedure.

4. A global cursor that can be referenced from outside the stored procedure.

l When a stored procedure invokes another stored procedure, the stored procedure is nested. Stored procedures can be nested up to 32 levels. When the called stored procedure starts executing, the nesting series is increased by one level, and the nested progression is reduced by one level when the called stored procedure completes execution. An attempt to exceed the highest level of nesting in levels 32 causes the entire stored procedure call chain to fail. The current nesting series for the executing stored procedure is stored in the @ @NESTLEVEL function.

l use cursors in stored procedures:


CREATE procedure Xg_insert_xxx

As

DECLARE @object_id as int

DECLARE @CaseBriefID as varchar (4)

DECLARE @number_style as varchar (50)

DECLARE @strNumberType2 as varchar (50)

DECLARE @number_code as varchar (50)

DECLARE @strSQL as varchar (1000)

DECLARE @strSQL1 as varchar (255)

DECLARE @brief_code as varchar (50)

DECLARE @creator as varchar (50)

DECLARE @BH as varchar (50)



DECLARE Object_cursor CURSOR

For the SELECT distinct object_id,casebrief_id,casebrief_code,table_creator from case_xxx



OPEN Object_cursor

FETCH NEXT from Object_cursor into @object_id, @CaseBriefID, @brief_code, @creator

while (@ @FETCH_STATUS = 0)

BEGIN

SELECT @BH =fullcode from view_user_depid where loginname= ' + @creator + '

Print @BH

DECLARE Number_cursor CURSOR

For the SELECT distinct number_style,number_code from case_xxx where object_id= @object_id



OPEN Number_cursor

FETCH NEXT from Number_cursor into @number_style, @number_code

while (@ @FETCH_STATUS = 0)

BEGIN

Set @strSQL = ' INSERT INTO case_xxx (casebrief_id,num_type,number) VALUES (' + @CaseBriefID + ', ' ' + @number_style + ' ", ' + @number_code + ') '

EXEC (@strSQL)

FETCH NEXT from Number_cursor into @number_style, @number_code

End

Close Number_cursor

Deallocate number_cursor

FETCH NEXT from Object_cursor into @object_id, @CaseBriefID, @brief_code, @creator

End

Close Object_cursor

Deallocate object_cursor

Go