Efficient SQL statements (SQL Server)

The following SQL statement takes Northwind as an example

1. Do not use functions and expressions on the left side of the "=" in the WHERE clause because the system cannot apply the index in a function or an expression

SELECT * from Customers WHERE Cast (CustomerID as VARCHAR) = ' 1 '--clustered Index scan full table scan
SELECT * from Customers WHERE CustomerID = ' 1 '--clustered index Seek Index Scan

2. Return only the necessary rows or columns
2.1 Reduced I/O times
2.2 Reduce the amount of data loaded into memory

3, about the composite Index (TBD)
The order in the composite index is important to apply the index in the query when the column in the query statement can only match the 1th column in the composite Index

4, the use of distinct statement principles
Use as little as possible, because the database engine spends a lot of time comparing all the fields, filtering out duplicate records, and thus affecting the efficiency of the query.
When the field is small, it can be used appropriately;

5. Union statement
The Union must meet the following requirements:
1. All SELECT statements must have the same number of columns
2. The data type of the corresponding column in all SELECT statements must be compatible

The procedure for executing a query statement that contains a union is as follows:
1. Execute all SELECT statements in sequence
2. Combine the result set of all SELECT statements into one result set
3, sorting the result set, and filtering out duplicate records (due to the need for 3rd step operation, resulting in a low efficiency of the joint query)
Can use union all without sorting and filtering duplicate records, high efficiency

6. Using Stored Procedures
The database engine can parse and optimize a stored procedure when it is created, and use the in-memory version of the procedure after the first execution of the procedure.
In contrast, each time a SQL statement is run, it is sent repeatedly from the client and compiled and optimized each time SQL Server executes the statements.

7. If you need to query a subset of data in a table with a very large amount of data several times, you can put this part of the data in a temporary table and then manipulate the temporary table.

8, fuzzy match% and index relationship
assumes that there is a table good, the primary key is a clustered index, and the type is nvarchar (()
SELECT * from Goods as G WHERE G. Goodsname like '% Product 1 ' --clustered Index scan full table scan
SELECT * from Goods as G WHERE g.goodsname like ' quotient% Product 1 ' --clustered index Seek indexed scan
SELECT * from Goods as G WHERE g.goodsname like ' item 1 ' --clus tered the index Seek index Scan
Conclusion: wildcard% is placed in the middle and back will walk the index, not in front of