Performance issues in SQL Coding

1. select an appropriate data type
Select the minimal data type for the column
If the text length in a column is different, use VARCHAR instead of CHAR
Do not use NVARCHAR or NCHAR if Unicode is not stored
If the length of a row cannot exceed 8000, use VARCHAR instead of TEXT
Use the numeric type instead of the character type for columns with only digits
Do not store date data of the string type.
2. Use Triggers with caution
Minimize the code in the trigger
If possible, try to replace the trigger with other more efficient technologies.
Avoid rollback triggers whenever possible
3. Only the required data is returned.
In the horizontal view, do not return columns that you do not need, and try not to use select *
In the vertical view, do not return unwanted rows. Try to use the where condition to filter the desired content.
Consider using TOP
Paging considerations
For aggregate queries, you can use the HAVING clause to further limit the returned rows.
4. Try to do less repetitive work
Control multiple executions of the same statement, especially the multiple executions of some basic data.
Data conversion may be designed to reduce the number of data conversions, but it is possible to reduce the number of data conversions.
Merge multiple updates for the same table with the same condition,
The UPDATE operation should not be split into DELETE operations + INSERT operations. Although the functions are the same, the performance difference is great.
Do not write meaningless queries.
When inserting a large amount of data, try not to use loops. You can use CTE. If you want to use loops, put them in a transaction;
5. Pay attention to the usage of temporary tables
In complex systems, temporary tables are difficult to avoid. Note the following when using temporary tables:
The statement is complex and has too many connections. You can use a temporary table for step-by-step execution.
The same part of data of a large table is used multiple times. Temporary tables are used for temporary data storage.
To combine the data of multiple tables to form a result, you can use a temporary table to summarize the data of these tables step by step.
In other cases, use of temporary tables and table variables should be controlled.
Pay attention to sorting rules.
Select into and create table + insert into for temporary tables.
6. Avoid using cursors.
For some line-by-line processing considerations
Consider using the associated subquery instead of the cursor
Note the following when using a cursor:
Use an efficient cursor type (for example, forward-only)
Use server-side cursors to keep the result set as small as possible.
You must DEALLOCATE the cursor.
7. Use the connection properly
Suitable indexes are required for columns used for connection in frequently connected tables.
Try to use the same data type for connected Columns
Avoid using connected columns with few unique values; otherwise, scan may occur.
If you want to connect four or more tables for some queries, consider downgrading some tables.
8. Other notes
The earlier the problem is discovered, the lower the cost is solved. Many performance problems can be found in the encoding stage. To detect performance problems early, note the following:
The programmer pays attention to and cares about the data volume of each table.
The coding and unit testing processes should be tested with databases with a large amount of data as much as possible. It is best to test with actual data.
Every SQL statement should be as simple as possible
Do not update data of tables with triggers frequently
Pay attention to the limitations of database functions and their performance