SQL code specification

EXAMPLES:

ORDERS_PK Products_IDX ProductDetails_ndx ProductDetails_ndx2

Triggers

All triggers should contain the name of the table, an underscore followed by "tr" and the letters which represent the intention of the trigger (i for insert, u for update, d for delete). If there are more than one trigger, a Numerical Designation, Starting with 2 Should Be appended to the name.

EXAMPLES:

Customers_tri Orders_triu Products_Trd Products_TRD2

User Defined Functions (UDFS)

A User Defined Function Should Prefixed by "UDF_" and the a description That Logical Follows The Function Process. The Description Should Be Proper Case Words with no space.

EXAMPLES:

UDF_GETNEXTID UDF_SUMORDERLINES

DEFAULTS

All Defaults Should Be Prefixed with "DF_" and the Some Description of The Default Value. The Description Should Be Proper Case with no spaces or understandcores.

EXAMPLES:

DF_ONE DF_GETDATE

Conclusion I hope that all of you out there have some type of coding standard to which you adhere. I think most of us naturally develop one over time, but if you need to work with other individuals, it makes sense to formalize your standards. Feel free to adopt my coding standards if you need them and I would be interested in hearing about any enhancements you make or disagreements you have.As always I welcome feedback in the forum below and please vote your opinion on this article.

Coding Standards - Part 2 - Formatting SQL

Introduction One of the areas that I feel is very important for managing and maintaining a stable environment is the use of standards. In any SQL Server environment, we deal with any number of objects or areas, but we often work with T-SQL code in our daily work. and as anyone know who has worked with others, having standards greatly improves and simplifies the ability of one person to take over for another and maintain some continuity between the individuals.

This article examines another area of ​​standards that I have found to be helpful in a multi-person team:. The formatting of code I know that formatting is a question of style and I will probably get some arguments here, but having everyone maintain a similar Format Great Speeds The Ease with understand The code.

The Problem How often do you get a sliver of code that is formatted as one long line of code? If you work with developers, the answer is probably quite often. Usually a developer formats T-SQL code in a manner that corresponds to the way In which they wouldormat C , ASP, VB, OR ANY Other Code. Which year not behward you format the code.

? How often do you then reformat it I know that I have often been sent something like this: select * from customers, orders, orderline where customers.orderid = orders.orderid and orders.orderid = orderline.orderid and orderline.amount> 100

Now this is a simple query, but when I am attempting to troubleshoot something, I like to have it in a format that makes life easier for me Usually the first step for me would be to format this as follows:. Select * from customers, Orders, OrderLine Where Customers.Orderid = Orders.Orderid and ORDERS.Orderid = OrderLine.Orderid and ORDERLINE.AMOUNT> 100

I would Also The Take Further Steps That Would Further Clarify The Joins That Are Occurring.

Why?

Because I understand the workings of a SQL statement by looking for a certain structure and verifying the joins are correct, no cross joins, proper columns, etc. And I often need to add and remove sections for testing, it helps me to quickly follow the Logic When Everything is arranged in a particular structure.

Setting a coding standard is something everyone should do and, IMHO, it is as important as setting naming standards. As with any standard, it simply makes it easier to work together and more efficiently when we understand each other's code.

I am not advocating you adopt my standards, though that would be nice. At least for me if I ever come to work for your company. However, I do urge that you adopt some standard and feel free to use these are a starting point.

MY Solution Here Are A List of items That i Have Included in Standards. These primarily Apply to select Queries, But The Can Easily Be adapted to insert, Update or delete queries.

Tabs - Sounds like a minor item, but the formatting of anything starts with tabs They can really help of hurt someone when editing or even viewing as some editors will reformat your display based on tabs I set a standard of 3 spaces Enough to... see a difference and still fit fairly long statements on my screen completely.Aliases - I pretty much always use an alias I used to do the 'a', 'b', etc. thing, essentially numbering each table sequentially as it appeared in. the query. I changed that to adopt a "standard" alias for each table in a database. These are usually 1 or 2 character abbreviations, sometimes 3, that I use over and over. While it seems cumbersome, I had a database with 300 Tables and Remembering The Aliases Was Pretty Easy for Both me and my development Team. Some Examples:

. Orders - o OrderLineItems - oi Customers - c CustomerSuport - cs I'm sure this will be easy for you and it also makes the reading of a query much cleaner I hate wading through joins that look like: and customersupport.phonenumber = customeraddress. Phonenumber

IT's Much Easier to See: and cs.phonenumber = ca.phonenumber

Spacing - I try to encourage spacing and the use of new lines for formatting queries I break each section into a new line (see the next item) as well as indent it I use tabs and spaces judiciously to make each area easier to read.. ......................................... ..

Select * from customerid = 5 and c.customeraddress is not null

I also include spaces around each parenthesis and comma and operator. These make the CTRL- controls handy for moving inside the query. Nothing worse then being unable to get to the> in a comparison because the column names are slammed up against the operator.Column names -. I've gone back and forth on this, but overall I've found that having each column on a separate line to be extremely convenient for commenting them in and out of queries I also precede columns with a comma, rather than having it follow the column name for the same reason. I often find this the best technique for debugging. Formatting in this way also allows me to easily cut and paste from the column list to the GROUP BY or ORDER BY sections.I also indent the join Syntax by One Tab and The on Clause by One Further Tab. There Help with The Readability of The Query. EXAMPLE:

selectcustomerid, customername, address, phone, status from Customers cinner join orders oon c.customerid = o.customeridleft outer join orderlines oion o.orderid = oi.orderidand oi.status = 1 where c.customerid = 5 and c.customeraddress is not Null Order Bycustomerid, Customername, Status

Note That I Cut and pasted the column list to the order by and the remoded a couple columns be selecting the entire line. A small shing, but it makes my typing season so much.

Sections - Each query can be broken up into sections based on the major keywords I move each of these to a new line to make the reading easy as well as the navigation convenient It makes more sense when looking at it, so here is an.. EXAMPLE:

selectcustomerid, customername, count (*) from Customers cinner join orders oon c.customerid = o.customeridleft outer join orderlines oion o.orderid = oi.orderidand oi.status = 1 where customerid = 5 and c.customeraddress is not null and ( c.active = 1ORc.status> 5) group bycustomerid, customername havingcount (*)> 1 order bycustomerid, customernameA few other items worth mentioning. I include special operators (TOP, DISTINCT, etc.) on the first line with SELECT. This keeps me from confusing them as a column of some sort. I also indent subselects in a similar manner, but starting from the point of the parenthesis. I then match up the closing parenthesis with the opening one on its own line as in the last example ABOVE.

Conclusions I know, it is merely intended to handle the formatting of sql. Even there, I'm Sure Therething I've Left out. As i wrote this i realized how much is involved and how CONFUSING IT CAN Be to Explain.

Since I am most often working with SQL, this is a high priority area. There are also standards for formatting the various object statements, including comments, etc. I'll be tackling those in another article later on.

I'd be interested to hear what other standards, if any, people have implemented and how they are working. I suspect this is often more of a "my own style" area, but I have learned to adapt to others, and I have Found That Having A Standard Truly Makes Work Easier.

AS always I welcome feedback on this article useing the "your opinion" Button Below.

Steve Jones ヾ Kranch.Net June 2002