Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
in principle
Write highly readable code: Follow naming principles and code style agreements to pay attention to the impact of T-SQL code performance: reduce network traffic, reduce disk IO, use index, avoid LOCK write security code
-------------------------------------------------- ----------------------------
When name a database object, a unified prefix or suffix is used.
Using a unified prefix or suffix is to improve the readability of the code, but stored procedures do not use SP_ as a prefix, the function does not use FN_ as a prefix. If SQL Server finds that the stored profix is prefixed as a sp_ as a prefix, you will first go to the Master database to query this stored procedure.
Add necessary comments
Store procedures or functions, you should comment, create time, modifier, modification time, function comment, use instructions, including a plurality of statements to perform this object
Timely inspect the execution situation
By default, if a SQL statement performs an error, SQL Server does not automatically perform execution in front of the Roll Back (set: set XACT_ABORT ON), after the SQL statement is executed, you need to check in time through global variable @@ error and @@ rowcount Performance.
Use standard JOIN way
The standard JOIN method refers to the filter condition in the While statement, which does not include JOIN conditions.
Try to avoid the client program directly by SELECT, INSERT, UPDATE, etc.
With storage process package data access, the stored procedure is compiled without each calculation of Execute Plan. And encapsulate logic while adding security.
Store procedure If you need to return data, use Output keywords
Do not return data with RETURN, the RETURN of the stored procedure should return the execution status of the stored procedure. If you need to return data, use the parameters with Output keywords.
Carefully use Identity as the primary key of the table's primary key
Identity will bring a lot of impact on client programs and Database interactions, and it will also bring trouble when data import exports, need to carefully evaluate these impacts. But Identity and GUID have advantages compared to readability. Try to avoid using NULL
If there is no special setting, NULL participation calculation results are null. If the principle of negligence, the correctness of the program logic will affect the correctness of the program logic, and the client program requires additional steps to process NULL. Need to set ANSI_NULLS to ON.
In the insert statement, use the determined column name
In the INSERT statement, use the determined column name with a small table structure to the T-SQL code
Try to use foreign keys, constraint checks to ensure data integrity
Data integrity is critical, foreign key, constraint check can avoid other write code to ensure that data integrity should not be used with select *, with the determined column name to replace *
The redundancy information in the query results affect the overall performance
Try to avoid using server-side cursors
Server-side games have a serious impact on performance, such as avoiding the cursor, if you can't avoid it, you should choose the most appropriate cursor type.
Try to avoid using a temporary table
Temporary tables can occur in disk IO operations, affect performance, can be used in nested queries, view, or table variables instead of a temporary table. If you need to cache a large amount of data, temporary table is better than Table variable, and pay attention to temporary table, if you need a series of SQL commands, add set nocount on the front.
Executing the SET NOCOUNT ON, the SQL command executes the number of rows that affect the impact will not return to the client, reduce network traffic, improve performance
Avoid using wildcards in the first character location when string match query
If the first character location uses a wildcard, INDEX does not work to avoid using IN or Not in.
Using IN or NOT IN, Index does not work when transaction is handled as much resource as much as possible
When transaction is processed, the least resource is taken up as much as possible to reduce resource locks and improve the overall performance of the database. At the same time, check the lock type and try to use a low-level lock type.
Matching transaction processing
If the stored procedure starts transaction processing, it should be responsible for ending this matter, Submit or Rollback
Use n keywords when operating nchar or nvarchar data types
With n keywords, SQL Server uses Unicode encoding to avoid garbled
Try to avoid using the fields of Text, NText, Binary, Image Data Types, try to avoid directing files or images directly into the database. Data type access method is different from a normal data type. Database is not a place where the file or image content is stored.
Try to use VARCHAR instead of char, use nvarchar instead of VARCHAR.
With varchar instead of the CHAR in order to save database space, nvarchar replaces VARCHAR to avoid the trouble brought by Unicode.
If data in a Table is different for different users, use View to isolate direct access to Table.
Check the impact of SQL injection attacks.
