SQL Server Performance Improvement Method)

1: Do not use functions on columns. You can use functions on scalar.

For example, if where dateadd (Day, 15, orderdate) = '2014/1/80', change it to orderdate = dateadd (day,-15, '2014/1/123 ');

2: do not declare a char variable to be compared with the nchar column. On the contrary, nvarcha and varchar are the same. During the comparison, the convert function is implicitly used, and thus the index may not be used.

3: do not join unnecessary tables. Avoid using distinct clauses whenever possible.

4: Select * not only increases network traffic, but also easily causes table scanning. Use the where statement to limit the rows you return and return large result sets by page, returns only the rows and columns you need.

5: Do not use any predicate such as <> and not like as few as possible. replace them with if exists and if not exists, so that you can use indexes.

6: Advantages of stored procedures: logical separation, separation of business logic and data processing logic; adjustment of SQL statements does not require redeploymentProgramReduces network bandwidth, improves security and integration, and assigns access permissions to stored procedures.[Not sure whether it is possible];Parameterized query prevents SQL injection and re-Compilation of query plans

7: The use of cursors will repeatedly extract rows, lock, manage locks, return rows, and other operations. To use them, try to use read-only forward cursors instead of FireWire cursors, which will increase the pressure on tempdb.[Not Sure].Usually, the cursor is used to process consecutive rows. If the table has a primary key, a while statement can be used to replace the use of the cursor.

8: after the transaction starts, the resource will be locked to prevent other transactions from being executed. Therefore, keep the transaction as short as possible. You can start a transaction in the application. A common practice is to verify the data validity before starting the transaction. Of course, you also need to verify the data in the transaction, however, this avoids many chances of rollback in the transaction.

9: