Release Date: 4/15/2005
| Update Date: 4/15/2005
Brian Walker
It may make people feel very strange, but it seems to have no "official" T-SQL coding standard. As early as the end of 1999, I was surprised to discover the SQL Server 7.0 standard proposed by John Hindmarsh, and I summarized him in the editorial in February 2000. (In February 2000 and the "download" of this month, John original standard.) Later, Ron Talmage wrote a series of column articles, proposed his suggestions for all kinds of "best methods", of course, SQL The Server team has also officially released the SQL Server Best Method Analyzer (SQLBPA). Now, a database administrator and application developer Brian Walker with more than 25 years of experience has put forward his advice and prompts.
The coding criteria is often ignored when t-SQL programming, but these standards are key tools for the development team to work smoothly. The coding standard introduced here is my many years of development. They certainly have not been universally accepted, but it is undeniable that some standards have subjective colors. My purpose is actually more in order to raise everyone's awareness, rather than touting themselves for T-SQL style: the most important thing is to establish some reasonable coding standards and follow these standards. You will find a range of different coding criteria, techniques, and prompts for T-SQL programming in this article. They are not listed in any particular priority or important order.
Let us start from the format. On the surface, the format of the T-SQL code does not seem to be important, but the consistent format can make your colleague (whether the same group is still a larger-wide T-SQL development team member) is easier to browse and understand you. Code. The T-SQL statement has a structure that follows a clear structure to make it easier to find and confirm different parts of the statement. The unified format also allows you to easily add the code segment in a complex T-SQL statement to make the debugging work easier. The following is a format of the SELECT statement:
Select c.name
, E.namelast
, E.namefirst
, E.NUMBER
ISNULL (I.Description, 'Na') AS DESCRIPTION
From tblcompany as c
Join Tblemployee AS E
On C.CompanyID = E.companyID
Left join tblcoverage as v
ON E.employeeiD = v.employeeid
Left join tblinsurance as i
ON v.insuranceid = I.insuranceID
Where c.name like @name
And v.created> Convert (SmallDateTime,
'01 / 01/2000 ')
ORDER by c.name
, E.namelast
, E.namefirst
, E.NUMBER
ISNULL (I.Description, 'NA')
SELECT @retain = @@ error, @Rows = @@ rowcount
IF @Status = 0 set @status = @retain
► A nested code block uses the indentation of four spaces. (Multi-line SELECT statement in the above code is a SQL statement.) When starting a new row in the same statement, the SQL keyword right is aligned. Configure the code editor to use spaces instead of using tabs. In this way, no matter what program is used to view the code, the format is consistent. ►Word all T-SQL keywords, including T-SQL functions. Variable name and cursor name use mix and case. Data types use lowercase.
► The table name is brief, but the meaning is as clear as possible. Typically, using uppercase table names as aliases, use the AS keyword specify the alias or field alias.
► When multiple tables are involved in a T-SQL statement, the table name is always used to qualify the field name. This makes others read more clearly and avoids the implications of the implicit.
► When the correlation number appears in a continuous code line (such as a series of substring function calls), they are arranged. This is easy to browse the digital list.
► Using a logical block of the T-SQL code using one (rather than two), as long as you need it.
► Declare the T-SQL local variable (eg @LNGTableID), use the appropriate data type declaration and consistent uppercase.
► Always specify the length of the character data type and make sure that the maximum number of characters that may be required, because the characters that are exceeded will be lost.
► Always specify the accuracy and scope of the decimal data type, otherwise, the default is not specified and integer range.
► Use the error handler, but remember that the error check example in the first (BOL) does not work as the introduction. The T-SQL statement (IF) used to check the @@ ERROR system function is actually cleared in the process and cannot capture any values other than zero. (Even if the example works, they can only capture a final error, not the first error you want to capture.) You must use SET or SELECT to capture the error code immediately, as shown in the example. If the status variable is still zero, it should be converted to the status variable.
► Avoid using the "Not Declare" feature, such as the columns in the system table, the functions that are not declared in the T-SQL statement or the unspected system stored procedures or extended stored procedures.
► Do not rely on any implicit data type conversion. For example, it cannot give a character value for the digital variable, and it is assumed that T-SQL will perform the necessary conversions. In contrast, the data type should be matched using the appropriate CONVERT function before assigning or comparing the value for the variable. Another example: Although T-SQL is implicit and automatically RTRIM for comparison, it is not possible to rely on this behavior because the compatibility level sets non-character expressions to complicate the situation.
► Do not compare empty variable values directly with the comparison operator (symbol). If the variables may be empty, IS NULL or IS Not NULL should be used, or use the ISNULL function.
► Do not use the STR function to round, this function can only be used for integers. If you need a string form of a decimal value, you should use the CONVERT function (transfer to a different range) or the ROUND function, and then convert it to a string. You can also use CEILING and FLOOR functions.
► Be careful when using mathematical formulas, because T-SQL may enforce expression as a unwanted data type. If a decimal result is required, add some and zero (.0) after integer constants (.).
► Never rely on the SELECT statement to return lines in any particular order unless the order is specified in the Order By clause.
► Typically, the ORDER BY clause should be used with the SELECT statement. Presented order (even not the most convenient) is better than unpredictable sequential, especially during development or debugging. (Before deploying into the production environment, you may need to delete the Order By clause.) In the case of the order of the return line, you can ignore the overhead of the ORDER BY. ► Do not use double quotes in the T-SQL code. Single quotes should be used for character constants. If there is no need to qualify the object name, you can use (non-ANSI SQL standard) parentheses to enclose the name.
► In SQL Server 2000, try to use the table variable instead of a temporary table. If the table variable contains a lot of data, please note that the index is very limited (only the primary key index).
► Create a temporary table in the routine, and finally delete a temporary table. Mixing DDL and DML statements helps handle additional recompilation activities.
► To recognize that the temporary table is not unused, appropriately uses them to make some routines more efficient, for example, when you need to repeat some data sets in the large table or the usual table. However, for a disposable event, it is best to use the export table.
► Be careful when using a table value UDF, because a parameter is passed in a variable (not constant), if this parameter is used in the WHERE clause, it will cause a table scan. Also avoid using the same table value UDF multiple times in a query. However, the table value UDF does have some very convenient dynamic compilation function. [Related Information: See Tom Moreau "Using UDF Fill Table Variable" in the "Generated Serial Number" column in November 2003. - Editor Press]
► Almost all stored procedures should set the set nocount OFF at the beginning, and set the set nocount off at the end. [SET NOCOUNT ON Make SQL Server no need to send DONE_IN_PROC messages to the client after performing each statement of the stored procedure. - Editor Press] This standard is also suitable for triggers.
► As long as multiple database modifications in routines use multiple databases, including a statement in a loop, you should consider declaring explicit transactions.
► Before using cursor-based methods or temporary table methods, you should look for a set-based solution to solve the problem. Set-based methods are usually more effective.
► Like the temporary table, the cursor is not not available. The FAST_FORWARD cursor using the small dataset is usually better than other progressive processing methods, especially when a few tables must be referenced to the required data. The routine of the result set includes "total" is usually more fast than using the cursor. If the development time allows, cursor-based methods and set-based methods can try it, see which method is better.
► It is convenient to use the table containing the serial number (from 1 to n).
► Understand the working principle of Cross Join and use it. For example, you can effectively use Cross Join between the work data sheet and the serial number, and the result is a record that will contain each work data and the serial number combination.
► My Conclusion is: T-SQL code is often very simple, so if a code block looks difficult to handle or repeat more, there may be a simpler, better way.
in conclusion
If you have any views on my suggestion, please feel free to send me an email to discuss it or ask your advice on other issues. I hope you will use this as the opening of the conversation.
Additional Information: Excerpted from Karen's editorial in February 2000
On the frontier front of the standard development, there is an independent newborn force headed by SQL Server Database Administrators John Hindmarsh. MCT, MCSE and MCDBA are most worth speaking to study. John's contribution is to write a detailed white paper, an outlined suggestions for the relevant standards of various SQL Server. Other unique articles I know are the "format and style" in Andrew Zanevsky's "Transact-SQL Programming" (ISBN 1-56592-401-0). Andrew, SQL Server Professional Contributors Tom Moreau and Paul Munkenbeck and John Friends and colleagues Stephen James have contributed to John's white paper. Below is an example of the suggestion of John to prepare a stored procedure: •
Use SQL-92 standard connection syntax.
•
To improve performance, you should use the connection, then use subqueries or nested queries.
•
Ensure that variables and parameters are matched to table data columns.
•
Be sure to use all variables and parameters, or all delete.
•
Place the temporary object to the local whenever possible.
•
Only a temporary table created during the stored procedure.
•
Check the validity of the input parameters.
•
Prioritize Select ... INTO, then use INSERT ... SELECT to avoid a lot of dead locks.
•
The logic unit required for maintenance work; do not create a large or long running process without creating a large amount of or long run.
•
Don't use Select * in any code.
•
Use indentation, blocks, tabs, and spaces during the process (see sample scripts).
•
The T-SQL statement is capitalized.
•
Add a large number of annotations to ensure that the process can be identified. Well comments are used in places that help clarify the process.
•
Includes transaction management unless the process is called from the MTS process. (Write an independent process for the MTS process.)
•
Monitor @@ TRANCOUNT to determine the responsibility level of the transaction.
•
Avoid using GOTO, except in the error handler.
•
Avoid using a nested process.
•
Avoid implicitly parsing object names to make sure all objects are owned by DBO.
Download 412Brian.zip
Link to www.microsoft.com/downloads/details.aspx?displayla ng=en&familyid=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displayLANG=EN
For more information on SQL Server Professional and Pinnacle Publishing, please visit its Web site http://www.pinpub.com/
Note: This is not a Web site for Microsoft Corporation. Microsoft does not assume any responsibility for the contents of the Web site.
This article is reproduced since SQL Server Professional in December 2004. Copyright 2004 Pinnacle Publishing, Inc. Unless otherwise stated. all rights reserved. SQL Server Professional is a published publication in Pinnacle Publishing. No part of this article is not used or replicated in any way without Pinnacle Publishing, Inc., except for any part of this article (except for short references in the comment). To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.
© 2005 Microsoft Corporation All rights reserved. all rights reserved. Use the specification.
Go to the original English page
