SQL query Efficiency Considerations Summary _mssql

First, accurate query conditions, for the introduction of parameters

Second, SQL logic execution order

From-->join-->where-->group-->having-->distinct-->order-->top

Three, Horizontal

Fields required by the query
When connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column so that you can reduce the time of parsing and reduce the syntax errors caused by column ambiguity
Iv. do less repetitive work

Control multiple executions of the same statement, especially the multiple execution of some underlying data that many programmers rarely notice
Reduce data conversions multiple times
Eliminate unnecessary subqueries and join tables, subqueries are generally interpreted as an outer join in the execution plan, and extra connection tables incur additional overhead
V. About ZERO table # and table variable @

If the statement is complex and there are too many connections, consider using temporary tables and table variables to step through
If you need to use the same part of a large table more than once, consider staging this part of the data with temporary tables and table variables
If you need to synthesize data from multiple tables to form a result, consider using temporary tables and table variables to summarize the data for more than one table in a step-by-step way
With regard to the selection of temporary tables and table variables, the temporary table is faster than the amount of data
Select INTO will be faster than create TABLE + INSERT into, but select into will lock the system tables sysobjects, sysindexes, syscolumns in tempdb, in a multiuser concurrency environment, Easy to block other processes
Six, subquery

Subqueries can be introduced with in, not in, EXISTS, not EXISTS
Relative subqueries for not in, not exists can instead be written using left join
If the subquery is guaranteed to not be duplicated, the in, exists related subqueries can be replaced with a inner JOIN
The correlated subqueries in are replaced with exists
Vii. Index

Avoid calculating operations on indexed fields
SELECT ID from T WHERE num/2=100
should read:
SELECT ID from T WHERE num=100*2
Avoid using not,<>,!= on index fields
Avoid using is null and is not NULL on indexed columns
Avoid data type conversions on indexed columns
Avoid using functions on index fields
Avoid using null values in columns that are indexed
Do not connect multiple fields to an indexed field
WHERE fame+ '. ' +lname= ' Haiwei. Yang '
should read:
WHERE fname= ' Haiwei ' and lname= ' Yang '
Eight, multiple table connection

When multiple tables are connected, the connection conditions must be fully written, rather than redundant.
Join conditions try to use clustered indexes
IX, other

In statements that can be used with union all, use the Union ALL
Avoid using In,not in,or in the WHERE clause
Avoid using resource-intensive operations, and SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine execution and consume resource sorting (sort) functionality. Distinct requires a sort operation, while the other requires at least two times to perform the sort
Like '%5400% ' This query does not refer to the index, and the kind ' x5400% ' refers to the range index.