10 principles for Optimizing SQL Server SQL statements

1. Use the index to traverse the table more quickly.

The index established by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. A reasonable index design should be based on the analysis and prediction of various queries. Generally speaking: ①. There are a number of duplicate values, and often have a range of queries (between, >,<,>=,< =) and order BY, the group by occurs column, you can consider establishing a clustered index ; ②. Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider a composite index; ③. Combined index to make the key query an index overlay as much as possible, its leading column must be the most frequently used column.

2, is null and is not NULL

You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

3, in and exists

exists is far more efficient than in. It is related to full table scan and range scan. Almost all in-action clause queries are rewritten as subqueries that use exists.

4, in the vast number of queries as far as possible to use the format conversion.

5, when in Server 2000, if the stored procedure has only one parameter and the output type, you must give this parameter an initial value when calling this stored procedure, otherwise the call error will occur.

Basic principles of SQL Server SQL statement Optimization 6, ORDER by and Gropu by

With both the order by and the group by phrase, any index helps to improve the performance of select. Note If there is a null value in the index column, optimizer cannot be optimized.

7. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

8, IN, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.

9, SET showplan_all on view execution scheme. DBCC checks database data integrity. DBCC (Database consistency Checker) is a set of programs that are used to verify the integrity of SQL Server databases.

Basic principles of SQL Server SQL statement optimization 10, using cursors with caution

In some cases where cursors must be used, consider moving the qualifying rows of data into a temporary table and then manipulating the temporary table definition cursors, which can significantly improve performance.

10 principles for Optimizing SQL Server SQL statements