SQL statement skills, not exclusive, presented here

I am not here to sort it out, but it feels pretty good after reading it. I would like to share it with you.

1. In applications, ensure that the number of accesses to the database is minimized on the basis of implementing functions;
Search for parameters to minimize the number of rows accessed to the table and minimize the result set, thus reducing the network burden.
Open operations should be processed separately as much as possible to improve the response speed each time; when using SQL in the data window, try to make
The indexes used are placed in the first selected column. The algorithm structure should be as simple as possible. During queries, do not use wildcard too much.
For example, select * from T1 statement, select several columns for which to use: Select col1, col2 from
T1; if possible, try to limit the number of rows in the result set as much as possible, for example: Select top 300
Col1, col2, col3 from T1, because in some cases the user does not need so much data. Do not
Using Database cursors in applications is a very useful tool, but it is more useful than using conventional, set-oriented SQL statements.
Statement requires more overhead; extract data searches in a specific order.

2. Avoid using incompatible data types. Such as float and INT, char and varchar, binary, and
Varbinary is incompatible. Incompatible data types may make the optimizer unable to execute some operations that can be imported
. For example:
Select name from employee where salary> 60000
In this statement, if the salary field is of the money type, it is difficult for the optimizer to optimize it because 60000
It is an integer. We should convert the integer type into a coin type during programming, instead of waiting for the conversion at runtime.

3. Avoid performing function or expression operations on fields in the WHERE clause whenever possible, which will cause the engine to abandon
Use indexes to scan the entire table. For example:
Select * from T1 where F1/2 = 100
Should be changed:
Select * from T1 where F1 = 100*2

Select * from record where substring (card_no, 5378) = '20140901'
Should be changed:
Select * from record where card_no like '201312'

Select member_number, first_name, last_name from Members
Where datediff (YY, datofbirth, getdate ()> 21
Should be changed:
Select member_number, first_name, last_name from Members
Where dateofbirth <dateadd (YY,-21, getdate ())
That is, any column operation will cause table scanning, including database functions, computing expressions, and so on.
If possible, move the operation to the right of the equal sign.

4. Avoid using it! = Or <>, is null, is not null, in, not in, and so on,
Because this will make the system unable to use the index, but can only directly search the data in the table. For example:
Select ID from employee where ID! = 'B %'
The optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.
 
5. Use numeric fields as much as possible. Some developers and database administrators prefer to include value-based messages.
Information Field
Designed to be optimized, this reduces query and connection performance and increases storage overhead. This is because the engine
Process the query and connect back to compare each character in the string one by one, but for the number type, you only need to compare one
This is enough.

6. Use the exists and not exists clauses reasonably. As follows:
1. Select sum (t1.c1) from T1 where (
(Select count (*) from T2 where t2.c2 = t1.c2> 0)
2. Select sum (t1.c1) from t1where exists (
Select * From T2 where t2.c2 = t1.c2)
The two produce the same results, but the latter is obviously more efficient than the former. Because the latter will not generate a large number of locks
Fixed table or index scan.
If you want to check whether a record exists in the table, do not use count (*) as inefficient and waste the server
Server resources. It can be replaced by exists. For example:
If (select count (*) from table_name where column_name = 'xxx ')
Can be written:
If exists (select * From table_name where column_name = 'xxx ')

You often need to write a t_ SQL statement to compare a parent result set and a child result set to find whether the statement exists in the parent
Records in the result set but not in the subresult set, such:
1. Select a. hdr_key from hdr_tbl a ---- tbl a indicates that TBL is replaced by alias.
Where not exists (select * From dtl_tbl B where a. hdr_key = B. hdr_key)

2. Select a. hdr_key from hdr_tbl
Left join dtl_tbl B on A. hdr_key = B. hdr_key where B. hdr_key is null

3. Select hdr_key from hdr_tbl
Where hdr_key not in (select hdr_key from dtl_tbl)
The three writing methods can get the same correct results, but the efficiency is reduced in turn.

7. Try to avoid using non-start letters to search for indexed character data. This also makes the engine unable
Use indexes.
See the following example:
Select * from T1 where name like '% L %'
Select * from T1 where substing (name, 2, 1) = 'l'
Select * from T1 where name like 'l %'
Even if the name field has an index, the first two queries still cannot be accelerated using the index, and the engine has
Perform operations on all data in the entire table one by one. The third query can use indexes to speed up operations.

8. There may be more than one connection condition between two tables by using the connection condition.
Write the complete connection conditions in the WHERE clause, which may greatly improve the query speed.
Example:
Select sum (A. Amount) from account A, card B where a. card_no = B. card_no
Select sum (A. Amount) from account A, card B where a. card_no = B. card_no
And a. account_no = B. account_no
The second statement is much faster than the first statement.

9. Eliminates sequential access to data in large table rows
Although all check columns have indexes, some forms of where clauses force the optimizer to use
Sequential access. For example:
Select * from orders where (customer_num = 104 and order_num> 1001) or
Order_num= 1008
The solution can be to use the Union to avoid sequential access:
Select * from orders where customer_num = 104 and order_num> 1001
Union
Select * from orders where order_num = 1008
In this way, you can use the index path to process queries.

10. Avoid difficult Regular Expressions
The like keyword supports wildcard matching, technically called a regular expression. However, this matching is especially time-consuming.
. Example: Select * from customer where zipcode like "98 ___"
Even if an index is created on the zipcode field, sequential scanning is used in this case. For example
If you change the statement to select * from customer where zipcode> "98000 ",
The index will be used for query, which obviously greatly improves the speed.
11. Use view to accelerate query
Sort a subset of a table and create a view, which sometimes accelerates query. It helps avoid multiple sorting
In addition, the optimizer can be simplified in other aspects. For example:
Select Cust. Name, rcvbles. Balance ,...... Other Columns
From Cust, rcvbles
Where Cust. customer_id = rcvlbes. customer_id
And rcvblls. Balance> 0
And Cust. postcode> 98000"
Order by Cust. Name
If this query is executed multiple times but more than once, you can find all the unpaid customers and put them in one
View, and sort by customer name:
Create view DBO. v_cust_rcvlbes
As
Select Cust. Name, rcvbles. Balance ,...... Other Columns
From Cust, rcvbles
Where Cust. customer_id = rcvlbes. customer_id
And rcvblls. Balance> 0
Order by Cust. Name

Then, query in the view in the following way:
Select * From v_cust_rcvlbes
Where postcode> 98000"
The number of rows in the view is smaller than that in the master table, and the physical order is the required order, reducing the disk size.
I/O, so the query workload can be greatly reduced.

12. If you can use between, do not use in.
Select * from T1 where ID in (10, 11, 12, 13, 14)
Changed:
Select * from T1 where ID between 10 and 14
Because in will make the system unable to use the index, but can only directly search the data in the table.

13. Distinct does not require group
Select orderid from details where unitprice> 10 group by orderid
You can change it:
Select distinct orderid from details where unitprice> 10

14. partial use of Indexes
1. Select employeeid, firstname, lastname
From names
Where dept = 'prod' or city = 'Orlando 'or division = 'food'

2. Select employeeid, firstname, lastname from names where dept =
'Prod'
Union all
Select employeeid, firstname, lastname from names where city = 'Orlando'
Union all
Select employeeid, firstname, lastname from names where division =
'Food'
If the dept column has an index, query 2 can partially use the index, and query 1 cannot.

15. Do not use Union if Union all is used.
Union all does not execute the select distinct function, which reduces unnecessary resources.

16. Do not write any queries that do not do anything.
For example, select col1 from T1 where 1 = 0
Select col1 from T1 where col1 = 1 and col1 = 2
This type of Dead Code does not return any result set, but it consumes system resources.

17. Try not to use the select into statement.
The select into statement will lock the table and prevent other users from accessing the table.

18. When necessary, force the query optimizer to use an index
Select * from T1 where nextprocess = 1 and processid in (8, 32, 45)
Changed:
Select * from T1 (Index = ix_processid) Where nextprocess = 1 and
Processid in (8, 32, 45)
The query optimizer forcibly uses the index ix_processid to execute the query.

19. Although the update and delete statements are basically fixed
Discussion:
A) Try not to modify the primary key field.
B) when modifying varchar fields, try to replace them with values of the same length.
C) Minimize the update operations on tables containing update triggers.
D) Avoid columns to be copied to other databases by update.
E) avoid updating columns with many indexes.
F) avoid updating columns in the WHERE clause condition.

The above mentioned are some basic notes for improving the query speed. However, in more cases
Different statements need to be tested and compared repeatedly to obtain the best solution. The best way to do this is test.
Which of the following SQL statements with the same function has the least execution time? However, if the database has a small amount of data, it cannot be compared.
In this case, you can view the execution plan. That is, you can score the query points for Multiple SQL statements that implement the same function.
The parser, press Ctrl + L to view the indexes used by the query, and the number of table scans (the two have the greatest impact on performance ).
Check the cost percentage.
You can use the Wizard to automatically generate a simple stored procedure: Click the run wizard icon on the Enterprise Manager toolbar and click
Click database and create Stored Procedure wizard ". Debugging complex stored procedures: on the left of the query Analyzer
Object Browser (no? Press F8) Select the stored procedure to be debugged, right-click, click debug, and enter parameters
Run. A floating toolbar is displayed, including one-step execution and breakpoint settings.