http://www.nsurvey.org/

http://www.codeproject.com/dotnet/VersioningControlledBuild.asp

http://www.microsoft.com/downloads/details.aspx?FamilyId=8A2E454D-F30E-4E72-B531-75384A0F1C47&displaylang=en

From: http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04l9.asp

T-SQL Coding Standards
Brian Walker
Surprising as it may be, there don't seem to be any "official" T-SQL coding standards. Back in late 1999, I was pleased to discover John Hindmarsh's proposed standards for SQL Server 7.0 and summarized some of his recommendations in a February 2000 editorial. (John's original standards were included in the February 2000 Downloads and are also included in this month's.) More recently, Ron Talmage has written a series of columns with his recommendations for a variety of "best practices," and, of course, the SQL Server team has officially shipped a Best Practices Analyzer for SQL Server (SQLBPA). Now, Brian Walker, a DBA and application developer with more than 25 years of experience, weighs in with advice and tips.

Coding standards are often overlooked when it comes to T-SQL programming, but they're a crucial component of a smoothly operating development team. The coding standards I espouse here are ones that I've developed over the years. No, they're not universally accepted, and, admittedly, some of them are subjective. My message is really more about raising awareness than promoting myself as an arbiter of T-SQL style: The most important thing is to establish some reasonable coding standards and adhere to them. What you'll find in this article is a series of miscellaneous coding standards, hints, and tips for T-SQL programming. They're not arranged in any particular order of priority or importance.

Let's start with formatting. On the surface, the formatting of T-SQL code may not seem that important, but consistent formatting makes it easier for colleagues–be they fellow team members or members of the larger worldwide T-SQL fraternity–to scan and understand your code. T-SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes. Here's a formatting example for a 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.CreateDate > 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
► Use four-space indentation for statements within a nested block of code. (The multi-line SELECT statement in the preceding code is a single SQL statement.) Right-align SQL keywords when starting a new line within the same statement. Configure the code editor to use spaces instead of tabs. This makes the formatting consistent regardless of what program is used to view the code.

► Capitalize all T-SQL keywords, including T-SQL functions. Use mixed case for variable names and cursor names. Use lowercase for data types.

► Keep table name aliases short, but as meaningful as possible. In general, use the capital letters of a table name as an alias and use the AS keyword to specify aliases for tables or fields.

► Always qualify field names with table name aliases when there are multiple tables involved in a T-SQL statement. This adds clarity for others and avoids ambiguous references.

► Align related numbers into columns when they appear in contiguous lines of code, such as with a series of SUBSTRING function calls. This makes the list of numbers easier to scan.

► Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally.
► Use appropriate data type declarations and consistent capitalization when declaring T-SQL local variables (such as @lngTableID).

► Always specify the length of a character data type, and make sure to allow for the maximum number of characters users are likely to need, since characters beyond the maximum length are simply lost.

► Always specify the precision and scale of the decimal data type, which otherwise defaults to an unspecified precision and integer scale.

► Employ error handling, but bear in mind that the error-checking examples in BOL don't work as advertised. The T-SQL statement (IF) used to check the @@ERROR system function actually clears the @@ERROR value in the process and will never capture a value other than zero. (Even if the examples did work, they'd only capture the last error that occurred rather than what is likely to be of more interest–the first error.) The error code must be captured immediately using SET or SELECT as in the preceding example. It should then be transferred to a status variable if the status variable is still zero.

► Avoid using "undocumented" features such as undocumented columns in system tables, undocumented functionality in T-SQL statements, or undocumented system or extended stored procedures.

► Do not rely upon any implicit data type conversions. For example, don't assign a character value to a numeric variable assuming that T-SQL will do the necessary conversion. Instead, use the appropriate CONVERT function to make the data types match before doing a variable assignment or a value comparison. Another example: Although T-SQL does an implicit and automatic RTRIM on character expressions before doing a comparison, don't depend on that behavior because compatibility level settings and use of nchar complicate things.

► Do not directly compare a variable value to NULL with comparison operators (symbols). If the variable could be null, use IS NULL or IS NOT NULL to do a comparison, or use the ISNULL function.

► Don't use the STR function to perform any rounding; use it with integers only. Use the CONVERT function (going to a different scale) or the ROUND function before converting to a string if the string form of a decimal value is needed. CEILING and FLOOR are other options.

► Be careful with mathematical formulas, since T-SQL may force an expression into an unintended data type. Add point zero (.0) to integer constants if a decimal result is desired.

► Never depend on a SELECT statement returning rows in any particular order unless the order is specified in an ORDER BY clause.

► In general, use an ORDER BY clause with any SELECT statement. A predictable order, even if it's not the most convenient one, is better than an unpredictable order, especially during development or debugging. (You may want to remove the ORDER BY clause before deployment to production.) In those situations where the order of the resulting rows really doesn't matter, don't bother with the overhead of an ORDER BY.

► Don't ever use double quotes in your T-SQL code. Use single quotes for string constants. If it's necessary to qualify an object name, use (non-ANSI SQL standard) brackets around the name.

► As much as possible, use table variables in place of temporary tables with SQL Server 2000. If the table variable will contain a large set of data, be aware that indexing is very limited (primary key index only).

► Create temporary tables early in the routine, and explicitly drop them at the end. Interspersed DDL and DML statements can contribute to excessive recompile activity.

► Recognize that temporary tables are not inherently evil, and considered use of them can make some routines much more efficient–for example, when a certain set of data from a large or heavily used table will be referenced repeatedly. However, for a one-off, a derived table may be a better choice.

► Be cautious with table-valued UDFs, because passing in a parameter with a variable, rather than a constant, can result in table scans if the parameter is used in a WHERE clause. And avoid using the same table-valued UDF more than once in a single query. Table-valued UDFs do, however, have some dynamic compilation features that can be handy. [Related: See Tom Moreau's use of a UDF to populate a table variable in his November 2003 column on generating sequence numbers.–Ed.]

► Almost every stored procedure should have SET NOCOUNT ON near the beginning, and it's good form to have SET NOCOUNT OFF near the end. [SET NOCOUNT ON eliminates SQL Server's sending DONE_IN_PROC messages to the client for each statement in a stored procedure.–Ed.] This standard also applies to triggers.

► You should consider declaring an explicit transaction any time more than one database modification statement is used in a routine. This includes a single statement executed multiple times in a loop.

► Always look for a set-based solution to a problem before implementing a cursor-based approach or a temporary table approach. A set-based approach is usually more efficient.

► Cursors, like temporary tables, aren't inherently evil. A FAST_FORWARD cursor over a small set of data will often outperform other methods of handling row-by-row processing. This is especially true if several tables must be referenced to get the necessary data. A routine that includes "running totals" in the result set often executes fastest when implemented using a cursor. If development time allows, try both a cursor-based approach and a set-based approach to see which one performs best.

► A table of sequence numbers, 1 through N, can be very handy.
► Understand how a CROSS JOIN works, and take advantage of it. For example, you can use a CROSS JOIN effectively between a table of working data and a table of sequence numbers; the result set contains a record for every combination of working data and sequence number.

► I'll wrap things up with this observation: T-SQL code tends to be concise, so if a block of code looks unwieldy or repetitive, there's probably a simpler and better method.

Conclusion
Let me know what you think of my recommendations, and feel free to e-mail me to discuss any of them–or to make suggestions for others. I invite you to think of this as an opening statement in a dialogue.

Sidebar: From Karen's February 2000 Editorial
On the standards front, there's a new, independent effort, spearheaded by SQL Server DBA John Hindmarsh, MCT, MCSE, MCDBA, which is most definitely worth your time. What John has done is write a detailed white paper outlining his recommendations for all sorts of standards associated with SQL Server. The only other similar effort I'm familiar with is Andrew Zanevsky's chapter "Format and Style" in Transact-SQL Programming (ISBN 1-56592-401-0). Andrew, as well as SQL Server Professional contributors Tom Moreau and Paul Munkenbeck, contributed to John's white paper, as did John's friend and colleague Stephen James. Here's an example of John's recommendations for writing stored procedures:

• Use SQL-92 standard join syntax.
• Use joins in preference to sub or nested queries for improved performance.
• Ensure variables and parameters match table data columns in type and size.
• Ensure all variables and parameters are used or else deleted.
• Keep temporary objects local in scope wherever possible.
• Limit use of temp tables to those created in stored procs.
• Check input parameters for validity.
• Use INSERT…SELECT in preference to SELECT…INTO, to avoid extensive locks.
• Maintain the logical unit of work requirement; don't create extensive or long-running processes where these can be shortened.
• Don't use SELECT * in any code.
• Lay out the procedure with indents, blocks, tabs, and white space–refer to the sample scripts.
• Use uppercase for T-SQL statements.
• Comment procedures extensively to ensure the processes are identified. Use line comments where these help clarify a processing step.
• Include transaction management unless the procedure is to be called from MTS processes. (Write separate procedures for MTS processes.)
• Monitor @@TRANCOUNT to determine transaction responsibility level.
• Avoid GOTO–except for error handling.
• Avoid nested procedures.
• Avoid implicit resolution of object names–make sure all objects are owned by dbo.

From… http://blogs.msdn.com/ricom/archive/2005/02/16/374167.aspx

Collections too slow? When to write your own basic types
As usual there isn’t really one set of rules that will always guide you to making the right decisions but I’d like to offer some approximately correct guidance to help you to decide when you should decline the standard menu and instead cook up your own basic types.

Rule #1: Don’t do it
Seriously, who needs the aggravation? If you create your own basic types (a collection, an event source, an enumerator, a string buffer, etc.) you’ll automatically start at a penalty. The built-in class is bound to be used in your application, either directly or indirectly. Even if you could substitute your class for the built-in class it’s likely that the new class doesn’t do everything the standard version does – in fact that may be the very reason you created your own class. So now your program will have to load (at least) two classes that do approximately the same job. Likewise your developers will have to keep (at least) two classes in their head and know how to choose between them. And of course you’ll have to service the extra class yourself – no handy security fixes from your pals at Microsoft in the event of boo-boos, you’ll have to do your own patches. You do have a site like windowsupdate.com in your release plan right? No? Hmmm… you might be needing one. Lastly, and perhaps most compellingly, it just doesn’t scale – if everyone creates their own version of some basic class that’s approximately but not quite the same as the one offered by default then the total redundancy that end users (like my mom) get in their applications will be staggering.

So, seriously, just don’t.
OK, you still really want to? Or better yet you really don’t want to but you’re pretty sure you have to. Here are some of the more common reasons why it becomes necessary.

Rule #2: Do it if data volume demands specialization
Our base classes are designed to be broadly applicable and so they provide a broad spectrum of services and a nice high quality of service. However this can work against you: Suppose your application needs say a million hash tables… Yikes! Do you really want our full service hash tables for that? Maybe not. How many entries in these hash tables? If there’s a million of them maybe not too many. What’s the overhead per hash table going to be? There could be very big savings on the table for a specialized class. Maybe enough savings to let you target a lower class of consumer hardware and still be functional. That kind of flexibility might be enough to justify the extra work.

Rule #3: Do it if call volume demands specialization
Again, our base classes are designed to be easy to use in a variety of situations and provide robust error reporting. However if (e.g.) collections are critical to your performance scenario and your call volume (number of calls) is such that every cycle counts you may need to have your own specialized collection. I’m picking on the collections here because everyone knows them, but the same phenomenon can happen in pretty much any low-level class. Sometimes the cost of the nice parameter checking (and we’d be slammed if we didn’t do good parameter checking) is comparable to the whole job at hand. Sometimes the cost of polymorphism (the virtual function calls) for plugability is comparable to the cost of the full job of the API. Consider poor ArrayList – we need to do argument validation on even the simple methods or else developers will be faced with very difficult to decode exceptions internal to the class. So we put in extra checks to help developers get rich messages and be more productive. However, if an ArrayList collection was the cornerstone of your application, creating a specialized class might be just what you need meet throughput goals on more modest hardware (and hence win valuable contracts).

Rule #4: Do it if exotic threading disciplines are required for scale
Many of our lowest level classes offer little guarantee in the way of multi-threaded services. We tend to write them so that you provide your own locking mechanism and we stay the heck out of the locking business. That’s a good position to take in my opinion because low level classes like hashtables don’t really understand the context in which they are being used, so they would tend to take locks at the wrong granularity to be useful if you try to make them natively multithreaded. This is all fine and well but if the objects are fundamentally single threaded (and hence need to be protected by locks) but you can’t afford to take those locks (because it would kill your scale-up on a multi-processor machines) then you might need a specialized collection, enumerator, or whatever, that is multi-processor aware and (very carefully!) uses volatile storage and maybe interlocked operations to provide some useful multi-threaded support in a lock-free manner. This might be what it takes to get good value for the dollar on (e.g.) an 8, or 32 processor box.

Rule #5: Do it if strict control over memory or exceptions is critical
The final case is probably the rarest by far. However there are cases where users are trying to create classes that will be used in a near-real-time scenario (such as video playback). In that case you might want to use only a set of classes that carefully control the lifetime of the objects allocated (e.g. via recycling) so as to have garbage collections happen in a much more predictable fashion. This is not an easy undertaking but it is possible and even important in some cases where managed code is being used in (e.g.) a kiosk.

Rule #6: Report performance issues in the regular classes whenever you can
Take advantage of http://lab.msdn.microsoft.com/ProductFeedback/ (aka Ladybug) to give us a wake-up call if some area of the framework is letting you down. This is a great way to influence the team's priorities and believe me we really want to do the most important stuff first. It's also a great way to share workarounds and warnings when appropriate.

Summary: Have careful measurements and clear verifiable goals for success before proceeding
All of the cases I’ve discussed identified a performance problem that was both acute and relevant to the overall success of the product. Before taking the cost of re-implementing a low level type you’ll want to quantify the costs and benefits of adopting your own and then verify that you are getting those benefits. E.g. (fictitious example) “Simulations indicated that a lock-free multi-reader version of the hashtable would yield 25% more throughput on 8-way processors and 50% more on 32-way processors for our product. These gains should result in an extra $1M revenue in the next fiscal year which justifies the expected $100,000 in extra support costs.”

You might start with ideas like “Hmm, I think I could do this with less memory if I roll my own” but I strongly caution you to not stop there. Have strong data to back your position or chances are you’ll get nothing but aggravation.

Remember, if you are delivering a small component into someone else’s larger system, you probably shouldn’t build your own low level implementations. But if you are a medium or large team delivering a complete subsystem or application, and you can devote real bodies to the problem, then you might be able to justify the investment.

posted on Wednesday, February 16, 2005 12:50 AM

http://www.ilovejackdaniels.com/css/css-cheat-sheet/