Improved SQL Server performance benefits from logical database design

Principles of SQL statement optimization:

1, use the index to traverse the table faster

The index established by default is not a clustered index, but sometimes it is not optimal. Under a non-clustered index, data is physically stored randomly on a data page. The reasonable index design should be based on the analysis and prediction of various queries. In general: ①. You can consider establishing a clustered index if you have a large number of duplicate values and often have range queries (between, >,<,> =,< =) and columns that occur with order by and group by. ②. Multiple columns are often accessed at the same time, and each column contains duplicate values to consider establishing a combined index; ③. Combined indexes to make the key query an index overlay, the leading columns must be the most frequently used columns. Indexes can help improve performance but not the more indexes the better, just the opposite. Too many indexes cause the system to be inefficient. When the user adds an index to the table, maintaining the collection of indexes will update the work accordingly.

2, is null and is not NULL

cannot be indexed with NULL, and any column that contains null values will not be included in the index. Even if there are multiple columns in the index, the column is excluded from the index as long as one of the columns 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. The inside relates to the full table scan and range scan. Almost all of the in-operation Subcode queries are rewritten as subqueries using exists.

4, in the mass query as little as possible with the format conversion.

5. When in SQL SERVER 2000, if the stored procedure has only one parameter and is an output type, the parameter must be given an initial value when the stored procedure is invoked, otherwise a call error will occur.

6. Order BY and Gropu by

Using the order by and group by phrases, any index contributes to the performance improvement of a SELECT. Note If there is a null value in the index column, optimizer will not be optimized.

7, any action on the column will result in table scan, which includes database functions, evaluation expressions, and so on, when the query to move the operation to the right of the equal sign as much as possible.

8, IN, or clauses often use a worksheet to invalidate the index. If you do not produce a large number of duplicate values, you can consider the sentence to be opened. The open clause should contain an index.

9. SET Showplan_all on to view the execution scenario. DBCC checks database data integrity.

DBCC (DataBase consistency Checker) is a set of programs that verify the integrity of SQL Server databases.