T-SQL coding standard [reprinted]

T-SQL coding standard http://www.microsoft.com/china/msdn/library/data/sqlserver/sp04l9.mspx

Release date: 4/15/2005|Updated on: 4/15/2005

Brian Walker

It may be strange, but it seems that there is nothing"Formal"T-SQLEncoding standard. As early1999At the end of the year, I was pleasantly surprised to find thatJohn HindmarshProposedSQL Server 7.0Standard, I'm in2000Year2He summarized some of his suggestions in his editorial. (2000Year2Month and month"Download"All includeJohnThe original standard .) Later,Ron TalmageWrote a series of ColumnsArticle, Raised his"Best Practice"Of course,SQL ServerThe Group has also been officially releasedSQL ServerBest Method Analyzer(Sqlbpa). Currently,25Years of Experience in Database administrators and ApplicationsProgramDeveloperBrian WalkerHe also gave his suggestions and tips.

Coding standards are often ignored for T-SQL programming, but these standards are key tools for the development team to work smoothly. The encoding standards introduced here are my development achievements over the years. Of course they are not yet universally accepted and undeniable. Some standards are subjective. My purpose is actually more to raise awareness than to boast that I am the arbitration of T-SQL styles: the most important thing is to establish reasonable coding standards and follow these standards. You will find a series of different coding standards, techniques, and tips for T-SQL programming in this article. They are not listed in any particular order of priority or importance.

Let's start with the format. Surface, T-SQLCodeThe format does not seem important, but the consistent format can make your colleagues (both members of the same group and members of a wider range of T-SQL development teams) you can easily browse and understand your code. The T-SQL statement has a structure that follows a clear structure that makes it easier to find and validate different parts of the statement. The unified format also makes debugging easier by adding and deleting code segments in complex T-SQL statements. The following is an example of the SELECT statement format:

Select C. name, E. namelast, E. namefirst, E. number, isnull (I. description, 'A') as descriptionfrom tblcompany as cjoin tblemployee as Eon C. companyid = E. companyidleft join tblcoverage as von E. employeeid = v. employeeidleft join tblinsurance as ion v. insuranceid = I. insuranceidwhere C. name like @ nameand v. createdate> convert (smalldatetime, '2014/1/123') order by C. name, E. namelast, E. namefirst, E. number, isnull (I. description, 'A') Select @ retain = @ error, @ rows = @ rowcountif @ status = 0 set @ status = @ retain
 

►The statement in a nested code block uses four spaces for indentation. (The multi-line SELECT statement in the above Code is an SQL statement .) When a new row is started in the same statement, the SQL keyword is right aligned. Configure the code editor to use spaces instead of tabs. In this way, no matter which program is used to view the code, the format is consistent.

►Capital all T-SQL keywords, including T-SQL functions. Variable names and cursor names are case-insensitive. The data type must be in lower case.

►The table name alias should be brief, but the meaning should be as clear as possible. Generally, an uppercase table name is used as an alias and the as keyword is used to specify the alias of a table or field.

_ When a T-SQL statement involves multiple tables, you always use the table name alias to limit the field name. This makes it easier for others to read clearly and avoids references with vague meanings.

►When related numbers appear in consecutive code lines (such as a series of substring function calls), they are arranged in columns. This allows you to easily browse the number list.

►Use one (rather than two) empty line to separate the Logical Block of the T-SQL code, as long as needed.

►Declare T-SQL local variables (e.g. @ lngtableid) with appropriate data type declarations and consistent uppercase.

►AlwaysSpecify the length of the character data type, and ensure that the maximum number of characters that may be required by the user is allowed, because the characters exceeding the maximum length will be lost.

►AlwaysSpecify the precision and range of the decimal data type. Otherwise, the precision and Integer Range are not specified by default.

►Use the error handler, but remember that the error check example in the first line (BOL) does not work as described. The T-SQL Statement (IF) used to check the @ error system function actually clears the @ error value in the process and cannot capture any value except zero. (Even if the example works, they can only capture the last error, rather than the first error you want to capture .) You must use set or select to capture error code immediately, as shown in the previous example. If the status variable is still zero, convert it to the status variable.

Do not use the "undeclared" feature, such as columns not declared in the system table, features not declared in the T-SQL statement, undeclared system stored procedures, or extended stored procedures.

►NoIt depends on any implicit data type conversion. For example, you cannot assign character values to numeric variables, but assume that the T-SQL performs the necessary conversions. Instead, use the appropriate convert function to match the data type before assigning or comparing values to the variable. Another example: although the T-SQL implicitly and automatically performs rtrim on the character expression before a comparison, it cannot rely on this behavior because setting a non-character expression at the compatibility level can complicate the situation.

►NoCompare empty variable values with comparison operators (symbols. If the variable may be null, use is null or is not null for comparison, or use the isnull function.

►Do not use the STR function for rounding. This function can only be used for integers. If you need a string in decimal format, you should first use the convert function (go to a different range) or the round function, and then convert it to a string. You can also use the ceiling and floor functions.

►Be careful when using mathematical formulas, because the T-SQL may force the expression to be understood as an unwanted data type. If a decimal result is required, the vertex and zero (. 0) should be added after the integer constant ).

►The SELECT statement must not depend on any particular order to return rows, unless the order is specified in the order by clause.

►Generally, the order by clause should be used with the SELECT statement. Predictable Order (even if not the most convenient) is stronger than unpredictable order, especially during development or debugging. (Before deploying it to the production environment, you may need to delete the order by clause .) When the order of returned rows is irrelevant, the overhead of order by can be ignored.

Do not use double quotation marks in T-SQL code. Single quotation marks should be used for character constants. If you do not need to limit the object name, you can use (not ansi SQL standard) brackets to enclose the name.

►In SQL Server 2000, try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index ).

►Create a temporary table in the routine, and then explicitly Delete the temporary table. Mixing DDL with DML statements helps with additional recompilation activities.

►Recognize temporary tablesNotNot available. You can use them appropriately to make some routines more effective. For example, when you need to repeatedly reference a large table or a dataset in a common table. However, it is best to use the export table for one-time events.

►Be careful when using the table value UDF, because if you use this parameter in the WHERE clause when passing a parameter in a variable (instead of a constant), it will cause table scanning. Avoid using the same table value UDF multiple times in a query. However, the table value UDF does have some very convenient dynamic compilation functions.[For more information, seeTom MoreauIn2003Year11Month"Generate serial number"In the column"UseUDFFill table Variables". -Editor's Note]

►Set nocount on should be set at the beginning for almost all stored procedures, and set nocount off at the end.[Set nocount onEnableSQL ServerYou do not need to send the statement to the client after executing each stored procedure.Done_in_procMessage.-Editor's Note]This standard also applies to triggers.

►As long as you use multiple database modification statements in the routine, including executing one statement multiple times in a loop, you should consider acoustic explicit transactions.

►Before using the cursor-based or temporary table method, you should first find a set-based solution to solve the problem. The set-based method is generally more effective.

►Like a temporary table, the cursor is not unavailable. Using the fast_forward cursor for a small dataset is usually better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. A routine that includes "Total" in the result set is usually faster than executing with the cursor. If the development time permits, you can try both the cursor-based method and the set-based method to see which method works better.

►It is convenient to use a table containing sequence numbers (from 1 to n.

►Understand how cross join works and use it. For example, you can effectively use cross join between a work data table and a sequence number table, and the result set will contain records of a combination of each work data and sequence number.

►In my concluding remarks: T-SQL code is often very concise, so if a code block looks hard to handle or repeat a lot of content, there may be a simpler and better way.

Conclusion

If you have any comments on my suggestions, please feel free to email me for discussion or give your suggestions on other issues. I hope you will start your conversation.

Other information: fromKaren 2000Year2Editorial of the month

At the forefront of standard development, there was an independent new force led by SQL Server database administrator John Hindmarsh. MCT, MCSE, and mcba are the most worth your time. John contributed by writing a detailed White Paper which outlined his suggestions on various standards related to SQL Server. The only article I know that puts forward similar suggestions is Andrew zanevsky's "format and style" chapter in "Transact-SQL programming" (ISBN 1-56592-401-0. Andrew, contributed by SQL Server Professional Tom Moreau, Paul munkenbeck, and Stephen James both contributed to John's White Paper. The following is an example of John's suggestions for writing a stored procedure:

•	Use SQL-92 standard join syntax.
•	To improve performance, connections should be prioritized and then subqueries or nested queries should be used.
•	Make sure that the type and size of the variables and parameters match the table data column.
•	Make sure that all variables and parameters are used or deleted.
•	Try to place the temporary object locally.
•	Only temporary tables created in the stored procedure are used.
•	Check the validity of the input parameters.
•	Select... into is used first, and insert... select is used to avoid a large number of deadlocks.
•	The logical unit required for maintenance. Do not create a large number of processes that run for a long time if they can be shortened.
•	Do not use select * in any code *.
•	Use indentation, blocks, tabs, and spaces during the process (see the sample script ).
•	The T-SQL statement must be capitalized.
•	Add a large number of annotations during the process to ensure that the process can be identified. Use line comments where it helps clarify the processing steps.
•	Includes transaction management, unless you want to call the process from the MTS process. (Compile an independent process for the MTS process .)
•	Monitor @ trancount to determine the transaction responsibility level.
•	Avoid using Goto, except in the error handling program.
•	Avoid using nested procedures.
•	Avoid implicit parsing of object names and ensure that all objects belong to DBO.

Download412brian. Zip

LinkWww.microsoft.com/downloads/details.aspx? Displayla % 20ng = en & familyid = B352EB1F-D3CA-44EE-893E-9E07339C1F22 & displaylang = en

For more information about SQL Server Professional and pinnacle Publishing, visit your website.Http://www.pinpub.com/

Note: This is not a web site of Microsoft Corporation. Microsoft is not liable for the content on this web site.

This article is reproduced from SQL Server Professional in February December 2004. Unless otherwise stated, all rights reserved: 2004 pinnacle Hing, Inc .. All rights reserved. SQL Server Professional is an independent publication of pinnacle Hing. You shall not use or copy any part of this article in any way without the prior consent of pinnacle Publishing, Inc. (except for short references in comments ). To contact pinnacle Hing, inc., call 1-800-788-1900.

2005 Microsoft Corporation is copyrighted. All rights reserved. Usage rules.