TSQL coding patterns

• Download bdj-v06.zip - 145.6 KB

Introduction

If you are coding for Transact SQL, then this article and the zip-file contain some useful code for typical tasks.

Background

As a database developer you often have to develop batch jobs, so some of the most important points are:

• find out where errors happen
• how long queries take to execute

To get this information you will need batch logging and some form of debugging. It is also very useful to have test cases describing what you expect as output. (As you see there are not many test-procedures attached to this article because the code is for demonstration of principles. For an real application there should be many test.)

Some months later when you modify some logic, your test code will help you to find problems before your code goes to production. This test code forces you to think clearly about interfaces and can also be used as examples that show others how your code can be used.

Others who have to maintain your code would be grateful if your code is as easy to read as possible. A coding standard is helpful in ensuring this, as well as using nice names for objects (tables, views, procedures, functions).

Keep the procedures small, so it is easy to understand them quickly. I try avoid triggers if I can (see Celko and Kyte). Use many functions, but be aware of the impact. I prefer to have try/catch blocks in functions, but if you cannot you can use CLR functions or stored procedures (using prefix "get").

There are a lot of ideas of how to choose names, letter case and layout. In my opinion these rules should not be too restrictive and should allow room for being creative. On the other hand there should be some minimum naming rules and they should be easy to follow. For example: I always write keywords in lower case text and use a code formatter to fix it. And if automatic SQL formatting is acceptable, I use it.

Having procedures that generate code (see generate_delete_proc) can communicate your coding rules to others and avoid manual typing. And of course, remember to comment your code with comments in TSQL-doc style, so the code should be easy to read and to write.

Use of Hungarian notation may disturb reading, so I prefer not to use it; see Celko for detailed arguments. Specifically, prefixes for views and tables should be avoided; Chris Date explains very clear why. Here are some examples for discussion:

• tblOrderDetail,
• TOrderDetail,
• tOrderDetail,
• OrderDetail,
• Order_detail,
• order_detail,
• orderDetail

Before we had schemas, perhaps we put things in different databases or uses prefixes to indicate logical groupings, such as tblCmsOrderDetail. Be honest: which ones are easiest to read? What if you wish to change the implementation making the table into a view of other tables? Do you want to a "vw" prefix then? What about materialized views and indexed views – should they have their own prefix then?

Microsoft documentation uses prefixes in examples, so we accept that for tutorial purposes, but in real application code it slows down reading and understanding of the business rules.

However, as you can see in attachments, I use postfixes (_in, _out, _inout; Feuerstein style) as I feel a real benefit from quickly being able to distinguish input, output and local parameters.

In the examples attached you will find procedures that have the following format:

• Proc: <verb><Object>
• Function: <attribute> | <name>

I let tables start with lowercase letters (avoiding shift on keyboard), because I find it easier to read and type, but feel free to start names with uppercase letter. In my opinion there are multiple correct answers in this case.

For tables, I choose classical pseudo-keys (and unique keys on the natural key) as typical for OLTP-tables. Purists might prefer natural keys, but in practice I found that even natural keys do not always meet the requirement to be "stable". Pseudo-keys may make joins easy, but be aware that every model has it limitations. For temporal or loading tables there typical will be no benefit of id columns. And for very extreme tables with many millions of rows and no need for foreign keys, you probably wish to avoid it.

For reporting purpose you may also benefit from dimensional modeling.

Thank to all of the authors of the books and articles!

B. D. Jensen

References

• http://www.sqlservercentral.com/columnists/kpowers/thegocommandandthesemicolonterminator.asp
• http://www.sqlservercentral.com/columnists/MWittrock/tsqldoc.asp
• http://www.sommarskog.se/
• T-SQL_Coding_Standard.asp