1.1, advanced query statement
1. Truncate Table [Table_name]
Delete all rows in the table without logging a single row delete operation.
Truncate Table is functionally identical to the delete statement without WHERE clauses: both of them delete all rows in the table. But Truncate Table is fast than the Delete, and the system and transaction log resources are used.
Every time the delete statement deletes a row, and records one per line of the deleted log in the transaction log. TRUNCATE TABLE deletes data by releasing the data page used by the storage table data and only the release of the page is only in the transaction log.
The count value used for the new row identification is reset to the seeds of the column. If you want to keep the identification count value, use Delete.
For tables referenced by the Foreign Key constraint, you cannot use truncate table, but you should use the DELETE statement without WHERE clauses. Since Truncate Table is not recorded in the log, it cannot activate the trigger.
2, sp_databases
Returns the database listed in the sysdatabases system table.
SP_Databases is not equivalent in an open database connection (ODBC).
3, sp_datatype_info
SP_DATATYPE_INFO is equivalent to SQLgetTypeInfo in ODBC. The return result is sorted by DATA_TYPE, and then mapping the data type to sort the tightness of the corresponding ODBC SQL data type.
4, sp_columns
Returns the column information of the specified table or view of the current environment.
SP_COLUMNS [TABLE_NAME]
Or: sp_columns @table_name = '[table_name]', @Column_name = '[column_name]'
5, sp_tables
Usage is the same, no longer refer to.
1.2, stored procedure
All design excellent Microsoft® SQL ServerTM 2000 applications should use the stored procedure. This should be such regardless of whether the application's business logic is written to the stored procedure. Even standard Transact-SQL statements that do not have business logic components, can also obtain performance benefits after packaging with parameters. The Transact-SQL statement that compiles the stored procedure can save a lot of processing at execution.
1.2.1 Design Rules for Storage Procedures
l CREATE Procedure Definitions itself may include any number and type of SQL statement other than the following CREATE statement, and anywhere in the stored procedure cannot use the following statement:
Create Default
Create Trigger
CREATE Procedure
Create View
Create Rule
l The maximum number of parameters during storage is 2100.
l You can reference a temporary table within the stored procedure.
l If you create a local temporary table within the stored procedure, the temporary table is only for the stored procedure; after exiting the stored procedure, the temporary table will disappear.
1.2.2 Creating a stored procedure
Example:
Create Procedure [PR_INSERT_CASEBRIEF_NEW]
@P_casebrief_code as varchar (50), @ 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_spy_starttime, @ p_spy_endtime)
SELECT @P_casebrief_id = @@ identity
Go
**** p_casebrief_id is the primary key of the table, automatically grows.
l Microsoft® SQL ServerTM 2000 stored procedures return data in four ways:
1. Output parameters, can return data (integer or character value, etc.), or return the cursor variable (the cursor is a set of results).
2, return to the code, always the integer value.
3. Results set of SELECT statements, these statements are included in any other stored procedure called in this store or during the stored procedure.
4, the global cursor available from the stored procedure.
l When a stored procedure calls another stored procedure, the stored procedure is nested. The stored procedure can nearest 32. When the stored stored procedure starts, the nested level is increased; when the stored stored procedure is executed, the nested level is reduced by one. Attempting to exceed the highest nested level of 32, the failure of the entire stored procedure call chain will result. The current nested grade of the stored procedure being executed is stored in the @@nestlevel function.
l Use a cursor during storage:
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 select distinct object_id, caasebrief_id, casbebrief_code, table_creator from case_xxx
Open Object_cursor
Fetch next from object_cursor @ Object_id, @ Casebrief, @ 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 select distinct number_style, number_code from case_xxx where object_id = @ Object_id
Open Number_cursor
Fetch next from number_cursor @ Number_Style, @ Number_codeWhile (@@ 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 @ Number_Style, @ Number_code
End
Close Number_Cursor
Deallocate Number_Cursor
Fetch next from object_cursor @ Object_id, @ Casebrief, @ brief_code, @ creator
End
Close Object_Cursor
Deallocate Object_cursor
Go
