SQL Server database operation tips

 

This includes prompting pending operations, shrinking databases, compressing databases, transferring databases to new users with existing user permissions, checking backup sets, and restoring databases during installation.

1. suspend the operation

When installing the SQL or SP patch, the system prompts that a pending installation operation is required. Restart is often useless. solution:
To HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Session Manager
Delete pendingfilerenameoperations

2. shrink the database

-- Re-Indexing
DBCC reindex
DBCC indexdefrag
-- Shrink data and logs
DBCC shrinkdb
DBCC shrinkfile

3. compress the database

DBCC shrinkdatabase (dbname)

4. Transfer the database to a new user with the existing user permission

Exec sp_change_users_login 'Update _ one', 'newname', 'oldname'
Go

5. Check the backup set

Restore verifyonly from disk = 'e: \ dvbbs. Bak'

6. Restore the database

Alter database [dvbbs] Set single_user
Go
DBCC checkdb ('dvbbs ', repair_allow_data_loss) with tablock
Go
Alter database [dvbbs] Set multi_user
Go

-- Checkdb has three parameters:
-- Repair_allow_data_loss
-- Execute all the repairs completed by repair_rebuild, including allocating and unassigning rows and pages to correct the allocation errors, structure row or page errors, and deleting corrupted text objects. These fixes may cause some data loss. The repair operation can be completed under the user transaction to allow the user to roll back the changes. If the rollback is fixed, the database will still contain errors and the backup should be restored. If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix. After the restoration, back up the database.
-- Repair_fast Performs small and time-consuming repair operations, such as fixing the additional keys in non-clustered indexes. These repairs can be completed quickly without the risk of data loss.
-- Repair_rebuild: execute all the repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing ). There is no risk of data loss when performing these repairs.

-- DBCC checkdb ('dvbbs ') with no_infomsgs, physical_only

7. Two Methods for clearing SQL server logs

During usage, we often encounter very large database logs. Here we introduce two solutions ......

Method 1

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed regularly to avoid excessive database logs.
1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click your server --> double-click to open the database directory --> select your database name (such as the Forum database) forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save
2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly.
3. After shrinking the database, we recommend that you set your database attributes to the standard mode. The operation method is the same as the first one, because logs are often an important basis for restoring the database in case of exceptions.

Method 2

Set nocount on
Declare @ logicalfilename sysname,
@ Maxminutes int,
@ Newsize int

Use databasename -- Name of the database to be operated
Select @ logicalfilename = 'databasename _ log', -- Log File Name
@ Maxminutes = 10, -- limit on time allowed to wrap log.
@ Newsize = 1 -- the size of the log file you want to set (m)

-- Setup/initialize
Declare @ originalsize int
Select @ originalsize = size
From sysfiles
Where name = @ logicalfilename
Select 'original size of '+ db_name () + 'Log is' +
Convert (varchar (30), @ originalsize) + '8 K pages or '+
Convert (varchar (30), (@ originalsize * 8/1024) + 'mb'
From sysfiles
Where name = @ logicalfilename
Create Table dummytrans
(Dummycolumn char (8000) not null)

Declare @ counter int,
@ Starttime datetime,
@ Trunclog varchar (255)
Select @ starttime = getdate (),
@ Trunclog = 'backup log' + db_name () + 'with truncate_only'

DBCC shrinkfile (@ logicalfilename, @ newsize)
Exec (@ trunclog)
-- Wrap the log if necessary.
While @ maxminutes> datediff (MI, @ starttime, getdate () -- time has not expired
And @ originalsize = (select size from sysfiles where name = @ logicalfilename)
And (@ originalsize * 8/1024)> @ newsize
Begin -- outer loop.
Select @ counter = 0
While (@ counter <@ originalsize/16) and (@ counter< 50000 ))
Begin -- Update
Insert dummytrans values ('fill log ')
Delete dummytrans
Select @ counter = @ counter + 1
End
Exec (@ trunclog)
End
Select 'final size of '+ db_name () + 'Log is' +
Convert (varchar (30), size) + '8 K pages or '+
Convert (varchar (30), (size * 8/1024) + 'mb'
From sysfiles
Where name = @ logicalfilename
Drop table dummytrans
Set nocount off

 

8. Several Methods for deleting duplicate data in the database

During database usageProgramSome problems may be caused by repeated data, which leads to incorrect database settings ......

Method 1

Declare @ Max integer, @ ID integer
Declare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)> 1
Open cur_rows
Fetch cur_rows into @ ID, @ Max
While @ fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ Max
Delete from table name where primary field = @ ID
Fetch cur_rows into @ ID, @ Max
End
Close cur_rows
Set rowcount 0

Method 2

There are two repeated records:

1. Completely repeated records, that is, records with all fields already exist,
Second, some records with duplicate key fields, such as duplicate name fields, are not necessarily repeated or can be ignored.

A. For the first type of repetition, it is easier to solve.
Select distinct * From tablename
You can get the result set without repeated records.
If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:
Select distinct * into # TMP from tablename
Drop table tablename
Select * into tablename from # TMP
Drop table # TMP
The reason for this repetition is that the table design is not weekly. You can add a unique index column.

B. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.
Select Identity (INT, 1, 1) as autoid, * into # TMP from tablename
Select min (autoid) as autoid into # tmp2 from # TMP group by name, autoid
Select * from # TMP where autoid in (select autoid from # tmp2)
The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

 

9. Two methods to change the user of a table in the database

You may often encounter a problem where all the tables cannot be opened due to the result of restoring a database backup to another machine. The reason is that the current database user was used during table creation ......

-- Change a table
Exec sp_changeobjectowner 'tablename', 'dbo'

-- Store and change all tables
Create procedure DBO. user_changeobjectownerbatch
@ Oldowner as nvarchar (128 ),
@ Newowner as nvarchar (128)
As

Declare @ name as nvarchar (128)
Declare @ owner as nvarchar (128)
Declare @ ownername as nvarchar (128)

Declare curobject cursor
Select 'name' = Name,
'Owner' = user_name (UID)
From sysobjects
Where user_name (UID) = @ oldowner
Order by name

Open curobject
Fetch next from curobject into @ name, @ owner
While (@ fetch_status = 0)
Begin
If @ owner = @ oldowner
Begin
Set @ ownername = @ oldowner + '.' + rtrim (@ name)
Exec sp_changeobjectowner @ ownername, @ newowner
End
-- Select @ name, @ newowner, @ oldowner

Fetch next from curobject into @ name, @ owner
End

Close curobject
Deallocate curobject

Go

10. Data is written cyclically in SQL Server.

 

Declare @ I int
Set @ I = 1
While @ I <30
Begin
Insert into test (userid) values (@ I)
Set @ I = @ I + 1
End

 

11. Two Methods for restoring the database without Database Log Files

Accidental deletion of database log files or other causes of Database Log damage

Method 1

1. Create a database with the same name

2. Stop SQL server again (do not detach the database)

3. overwrite the new database with the data file of the original database.

4. Restart SQL Server.

5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)

6. After completion, You can generally access the data in the database. At this time, the database itself usually needs problems. The solution is to use
The database script creates a new database and imports the data.

Use master
Go

Sp_configure 'Allow updates', 1 reconfigure with override
Go

Update sysdatabases set status = 32768 where name = 'suspicious database name'
Go

Sp_dboption 'questionable database name', 'single user', 'true'
Go

DBCC checkdb ('questionable database name ')
Go

Update sysdatabases set status = 28 where name = 'questionable database name'
Go

Sp_configure 'Allow updates', 0 reconfigure with override
Go

Sp_dboption 'questionable database name', 'single user', 'false'
Go

Method 2

Set the database to emergency mode
Stop the SQL Server service;
Remove the data file xxx_data.mdf of the application database;
Re-create a database xxx with the same name;
Stop the SQL service;
Overwrite the original data file;
Run the following statement to set the database to emergency mode;
Run

"Use master"

Go

Sp_configure 'Allow updates', 1

Reconfigure with override

Go"

Execution result:

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

The configuration option 'allowupdates' has been changed from 0 to 1. Run the reconfigure statement for installation.

Then run "Update sysdatabases set status = 32768 where name = 'xxx '"

Execution result:

(The number of affected rows is 1)

Restart the SQL Server service;

Run the following statement to set the application database to single user mode;

Run "sp_dboption 'xxx', 'single user', 'true '"

Execution result:

The command is successfully completed.

DBCC checkdb;

Run "DBCC checkdb ('xxx ')"

Execution result:

The DBCC result of 'xxx.

The DBCC result of 'sysobjects.

The object 'sysobjects' has 273 rows on 5 pages.

The DBCC result of 'sysindexes.

The object 'sysindexes' has 202 rows, which are on 7 pages.

The DBCC result of 'syscolumns.

.........

 

Run the following statement to disable the system table modification option;

Run "sp_resetstatus" XXX"

Go

Sp_configure 'Allow updates', 0

Reconfigure with override

Go"

Execution result:

Before updating the database 'xxx' entries in sysdatabases, the mode is 0, the status is 28 (the status is suspect_bit = 0 ),

No row in sysdatabases is updated because the mode and status are correctly reset. No error. No changes were made.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

The configuration option 'allowupdates' has been changed from 1 to 0. Run the reconfigure statement for installation.

 

Re-establish another database XXX. lost;

DTS export wizard

Run the DTs export wizard;

Copy the source to select the emergencymode database XXX and import it to XXX. lost;

Select "copy objects and data between SQL Server databases" and try it multiple times. It seems that it cannot be done. It just copies all the table structures, but there is no data or views and stored procedures, in addition, the DTS wizard finally reports a copy failure;

Therefore, we finally chose "Copy tables and views from the source database", but later we found that we can only copy some table records;

Therefore, select "Use a query to specify the data to be transmitted" and export the missing table records;

Views and stored procedures are added by executing SQL statements.

 

12. Maintain the index of the table in SQL Server

Some problems are often encountered in the use and creation of database indexes. here we can use some alternative solutions...

-- Step 1: Check whether maintenance is required and whether the scan density/scan density is 100%
Declare @ table_id int
Set @ table_id = object_id ('table name ')
DBCC showcontig (@ table_id)

-- Step 2: reconstruct the table Index
DBCC dbreindex ('table name', pk_index name, 100)

-- Redo the first step. If the scan density/scan density is less than 100%, rebuild all the indexes in the table.
-- YANG Jing: Not necessarily 100%.
DBCC dbreindex ('table name', '', 100)

 

13. Check the patch version of SQL Server

The patch version check of SQL Server is not as direct as the windows patch version check. If a System Administrator does not know the patch number corresponding to the SQL Server version, it may also encounter some trouble, therefore, it is explained that using this method to identify machines is a safe way and will not affect the system.
1. log on to SQL Server using iSQL or SQL query analyzer. If iSQL is used, enter iSQL-U sa in the CMD window, and then enter the password; if you use the SQL query analyzer, start it from the program and enter the SA and password (you can also use Windows for verification ).
2. in iSQL, enter:
Select @ version;
Go

Or input in the SQL query analyzer (in fact, if you don't want to enter it, you just need to open the help information :))
Select @ version;
Then, press the execution button;
The SQL version information is returned as follows:
Microsoft SQL Server 2000-8.00.760 (Intel x86) Dec 17 2002 14:22:05 copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (build 2195: Service Pack 3)
8.00.760 indicates the SQL Server version and patch number. The relationship is as follows:

SQL Server 2000 version and level @ version product level
Original version 8.00.194 RTM of SQL Server 2000
Database components SP1 8.00.384 SP1
Database components SP2 8.00.534 SP2
Database components SP3, sp3a, or MSDE 2000 release a 8.00.760 SP3
Database components SP4 8.00.2039 SP4

In this way, we can see the correct version and patch number of SQL Server.

We can also use xp_msver to view more detailed information.

14. SQL Server database backup and recovery measures

 

14.1 back up the database

1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console.
2. SQL Server group --> double-click to open your server --> double-click to open the database directory
3. Select Your Database Name (for example, Forum database Forum) --> click the tool in the menu above --> select backup database
4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up

14.2 restore a database

1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console.
2. SQL Server group --> double-click to open your server --> click the new database icon in the icon bar to retrieve the name of the new database.
3. Click the new database name (such as the Forum database Forum) --> then click the tool in the menu above --> select recover Database
4. In the displayed window, select "restore from device"> "select device"> "add"> "select your backup file name"> "add" and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1. (If you have backed up the same file multiple times, click View next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button
5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need to set it according to your SQL installation (you can also specify your own directory). The logical file name does not need to be changed, the file name to be moved to the physical server must be changed based on the recovered machine. For example, if your SQL database is installed in D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data, then modify the directory of the recovered machine according to the change, and the final file name should be changed to your current database name (for example, if it was originally bbs_data.mdf, the current database is forum, change it to forum_data.mdf). The log and data files must be modified in this way (the log file name is * _ log. LDF). You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify D: \ sqldata \ bbs_data.mdf or D: \ sqldata \ bbs_log.ldf ), otherwise, an error is returned.
6. After the modification is complete, click "OK" below to restore. a progress bar is displayed, prompting the recovery progress. After the restoration is complete, the system will automatically prompt "success". If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations. The common error is nothing more than a directory error, repeated file names, incorrect file names, insufficient space, or database in use errors, you can close all SQL windows and re-open them to restore the database. If an error is prompted, stop the SQL Service and restart it, as for the above other errors, you can change the content of the errors to restore them.

14.3. shrink the database

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed regularly to avoid excessive database logs.
1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click your server --> double-click to open the database directory --> select your database name (such as the Forum database) forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save
2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly.
3. After shrinking the database, we recommend that you set your database attributes to the standard mode. The operation method is the same as the first one, because logs are often an important basis for restoring the database in case of exceptions.

14.5 set daily automatic Database Backup

We strongly recommend that users with conditions perform this operation!
1. Open the Enterprise Manager and click Microsoft SQL Server> SQL Server group in the root directory of the console. Double-click to open your server.
2. Click Tools in the menu above --> select Database Maintenance Scheduler
3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity.
4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "backup every day". Click "OK ".
5. Next, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk D, such as D: \ databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, the backup file extension is generally Bak and the default is used.
6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next step to complete
7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started.
8. At this time, the database plan has been successfully run and will be automatically backed up according to the above settings.

Modify plan:
1. Open the Enterprise Manager, in the root directory of the console, choose Microsoft SQL Server> SQL Server group> double-click your server> Manage> database maintenance plan. plan, can be modified or deleted

14.6 data transfer (new database or transfer server)

In general, it is best to use backup and restoration operations to transfer data. In special cases, you can use the Import and Export Method for transfer. Here we will introduce the Import and Export method, the data import/export method can be used to reduce or contract the database size when the database contraction is invalid. By default, this operation provides you with a certain understanding of SQL operations, if you do not understand some of these operations, you can consult the relevant staff of the mobile network or query online materials.
1. Export all tables and stored procedures of the original database into an SQL file, when exporting data, make sure to select the options to compile the index script and write the primary key, foreign key, default value, and check the constraints script.
2. Create a database and execute the SQL File Created in step 1 for the new database.
3. Use SQL to import and export all the table content from the original database to the new database.

Restore data to a time point using database logs
Because of the abnormal data loss, and do not want to use backup data restoration, as long as there is a backup and the current log is well preserved, you can try this method, maybe the loss can be recovered ......

1. If a full-Database Backup (or multiple differential or incremental backups exist) exists before the misoperation, the first thing to do is
Perform a log backup (if you set trunc. Log On chkpt to 1 to prevent the log file from becoming larger)
Backup log dbname to disk = 'filename'
2. Recover a full-database backup. Use with norecovery. If there are other differences or incremental backups, recover them one by one.
Replay
Restore database dbname from disk = 'filename' with norecovery
3. Restore the last log backup, that is, the log backup you just created. Specify the recovery time point to the time before the misoperation.
Restore log dbname from disk = 'filename'
With stopat = 'date _ time'

These operations can be completed in the SQL Server Enterprise Manager, which is not difficult...

Of course, if the misoperation involves operations that do not remember logs, such as truncate table and select into, then there is no farli.
Use the above method to restore data...

How to restore the damaged SQL Server2000 Database File
The problem is serious. Whether it can be fixed depends on your luck ......

In SQL Server, if the database file (non-system database file) encounters an error, it is only applicable to databases other than the master and MSDB databases.

Description:

1. Create a test database (the database type is full)
2. Create a table and insert vertex records
Create Table A (C1 varchar (2 ))
Go
Insert into a values ('A ')
Go
Insert into a values ('bb ')
Go
3. Make a full backup to the test_1.bak file.
4. Make some modifications
Insert into a values ('cc ')
Go
Create Table B (C1 INT)
Go
Insert into B values (1)
Go
Insert into B values (2)
Go
5 shutdown Database Server
6. Use ultraedit to edit the database file test_data.mdf. Modify the dot-byte content at will, which is equivalent to a fatal damage to the database.
7. Start the database, run the Enterprise Manager, open the database, and the test turns gray.
8. Run iSQL-slocalhost-USA-P.
1> backup log test to disk = 'd: Program filesmicrosoft SQL servermssqlbackup
Est_2.bak 'with no_truncate
2> go
Two pages have been processed. These pages belong to the 'test _ log' file of the database 'test' (located on file 1 ).
The backup log operation successfully processed two pages and took 0.111 seconds (0.087 MB/second ).

9. Restore the oldest full backup
1> Restore database test from disk = 'd: Program filesmicrosoft SQL servermssql
Backup est_1.bak 'with norecovery
2> go
96 pages have been processed. These pages belong to the 'test _ data' file of the database 'test' (located on file 1 ).
One page has been processed, which belongs to the 'test _ log' file of the database 'test' (located on file 1 ).
The Restore database operation successfully processed 97 pages, and took 0.107 seconds (7.368 MB/second ).

10 restore recent logs
1> restore Log Test from disk = 'd: Program filesmicrosoft SQL servermssqlbacku
P est_2.bak 'with recovery
2> go
Two pages have been processed. These pages belong to the 'test _ log' file of the database 'test' (located on file 1 ).
The restore log operation successfully processed two pages, and took 0.056 seconds (0.173 MB/second ).

15. Storage Process Writing experience and Optimization Measures

1. Suitable for readers: database developers, who have a large amount of data in the database and who are interested in optimizing the SP (stored procedure.

II. Introduction: complex business logic and database operations are often encountered during database development. In this case, SP is used to encapsulate database operations. If there are many SP projects and there is no certain specification for writing, it will affect the difficulties of system maintenance and the difficulty of understanding the big SP logic in the future, in addition, if the database has a large amount of data or the project has high performance requirements for the SP, you will encounter optimization problems. Otherwise, the speed may be slow. After hands-on experience, an Optimized SP is hundreds of times more efficient than an optimized SP with poor performance.

Iii. content:

1. If developers use tables or views of other databases, they must create a view in the current database to perform cross-database operations. It is best not to directly use "Databse. DBO. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage specifications:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

III. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

IV. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

VI. use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

VII. Try to use "> =" instead of "> ".

VIII. Note the replacement between the or clause and the union clause.

IX. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

XI. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page lock will be upgraded to the table lock.

B) Specification for indexing:

I. You should consider creating indexes in combination with applications. We recommend that you create a large OLTP table with no more than six indexes.

Ii. Try to use the index field as the query condition, especially the clustered index. If necessary, you can use index index_name to forcibly specify the index.

Iii. Avoid performing table scan when querying large tables. If necessary, create an index.

IV. when using an index field as a condition, if the index is a joint index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used.

V. Pay attention to index maintenance, rebuild indexes periodically, and recompile the stored procedure.

C) use of tempdb:

I. Avoid using distinct, order by, group by, having, join, and cumpute as much as possible, because these statements will increase the burden on tempdb.

Ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

III. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.

IV. if the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.

V. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

Vi. Use caution when connecting large temporary tables to other large tables to query and modify them, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in one statement.

D) ReasonableAlgorithmUsage:

Based on the SQL optimization technology mentioned above and the SQL Optimization content in the ASE tuning manual, combined with practical applications, a variety of algorithms are used for comparison to obtain the method with the least resource consumption and the highest efficiency. Specific ASE optimization commands available: Set statistics Io on, set statistics time on, set showplan on, etc.