Performance issues noted in SQL coding

1. Select the appropriate data type
To select a minimized data type for a column
Suppose the text in a column is of varying length, using varchar instead of char
Do not store Unicode do not use nvarchar or nchar
Assuming a row is not longer than 8000, use varchar instead of text
Use numeric types instead of character types for columns that only have numbers
Do not use string types to store date data.

2. Use the trigger carefully
Keep the code within the trigger minimized
Wherever possible, replace triggers with other, more efficient technologies
Try to avoid rolling back triggers
3. Return only the required data
Horizontally, do not return the columns you do not need, try not to use SELECT *
Vertically, don't return to the rows you don't need, try to use the Where condition to filter what you need
Consider using the top
Consider paging
For aggregate queries, the returned rows can be further qualified with the HAVING clause.
4, as little as possible to do repetitive work
Controls multiple runs of the same statement, especially if some of the underlying data is run multiple times.
Reducing the number of data transformations may require data conversion to be a design problem, but the reduction is achievable.
Merge multiple update for the same condition on the same table,
The update operation does not split into the form of a delete operation +insert operation. Although the function is the same. But the performance difference is very big.
Don't write any queries that don't make sense.
When inserting large amounts of data, try not to use loops and be able to use CTE. Suppose you want to use loops. Also put in a transaction;
5, pay attention to the use of temporary table method
In complex systems, temporary tables are very difficult to avoid. Note on temporary tables:
The statement is very complex and has too many connections to consider stepping through the temporary table.
Use the same part of the data for a large table multiple times, and consider staging the data with a temporary table.
You need to synthesize data from multiple tables to form a result. The ability to consider the use of temporary tables to summarize data for these tables in steps.

Other circumstances. You should control the use of temporal tables and table variables.
Note the collation.
About temporal table generation use SELECT into and create TABLE + INSERT into options.
6. Avoid using cursors
For some line-by-row processing, consider putting the client
Consider using an associated subquery instead of a cursor
The following issues must be noted when using cursors:
Use an efficient cursor type (such as forward-only)
Keep the result set as small as possible when using server-side cursors.

Cursors should not be closed at the end of the use, but also deallocate.
7. Proper use of the connection
Appropriate indexes are required for connected columns for frequently connected tables
The column used for the connection uses the same data type as much as possible
Avoid using columns with a column that has very few unique values, or it will cause the scan
Suppose that some queries need to be connected to 4 or many other tables, and can consider a low-norm table
8. Other areas needing attention
The sooner the problem is discovered, the lower the cost, the more performance problems can be found in the coding phase, in order to detect performance problems earlier, it is important to note:
Program apes pay attention to the amount of data in each table.

The coding process and the unit test process as far as possible with the data of a large database test, it is best to use the actual data test.
Every SQL statement is as simple as possible
Do not update data for tables that have triggers frequently
Note the limitations of database functions and their performance

Performance issues noted in SQL coding