Click here to Skip to main content
Click here to Skip to main content

Tagged as

Writing Readable SQL

, 10 Nov 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Chapter 1 excerpt of Guide to Exploring Database Schema

It is important to ensure that SQL code is laid out in the way that makes it easiest for the team to use and maintain it. Before you work out how to enforce a standard, you have to work out what that standard should be, and this is where the trouble often starts. SQL, unlike a language such as Python, doesn't require code to follow any formatting or layout rules in order to compile and run and, as William Brewer (www.simple-talk.com/sql/t-sql-programming/sql-code-layout-and-beautification/) has noted, it's hard to find two database developers who agree in detail on how it should be done.

In large corporations, there often is a software architect who decides on an organization-wide standard, and expects all developers to adopt the naming and layout conventions it prescribes. In smaller companies, the standard is often worked out between developers and maintenance teams at the application level. In either case, if there is no existing standard, one must be devised before coding starts. By laying SQL out carefully and choosing sensible object names you greatly assist your team members, as well as anyone who inherits your code.

Why Adopt a Standard?

It has often been said that every language marks its practitioners for keeps. Developers approach SQL as a second language and, as such, almost always write and format SQL in a way that is strongly inflected by their native language.

In fact, it is often possible to detect what language a database developer first cut his teeth on from looking at the way they format SQL. Fortran programmers tend to write thin columns of abbreviated code; Java programmers often like their SQL code to be in lower case; BASIC programmers never seem to get used to multi-line strings.

There is no single correct way of laying out SQL or naming your database objects, and the multiple influences on the way we write SQL code mean that even consensus agreement is hard to reach. When a developer spends forty hours a week staring at SQL code, he or she gets to like it laid out to a particular style; other people's code looks all wrong. This only causes difficulties when team members find no way of agreeing on a format, and much time is wasted lining things up or changing the case of object names before starting to work on existing code.

There was a time when unlearning old habits, in order to comply with existing layout standards in the workplace, was painful. However, the emergence of code formatting tools that work within the IDEs, such as SSMS, has given us a new freedom. We configure multiple layout templates, one to conform to our preferred, personal layout style, and another that conforms to the agreed standard, and to which the code layout can be converted as part of the Source-Control process. In development work, one can, and should, do all sorts of wild formatting of SQL, but once it is tested, and "put to bed," it should be tidied up to make it easier for others to understand.

Using good naming conventions for your database objects is still a chore, and allowances have to be made for a team to get familiar with the standard, and learn how to review the work of colleagues. If you can, produce a style guide before any code is cut, so that nothing need be saved in Source Control that doesn't conform. Any style guide should, I think, cover object naming conventions and code layout. I would keep the topic of structured code-headers, and code-portability, separate. Although ISO/IEC 11179 will help a great deal in defining a common language for talking about metadata, it is, inevitably, less prescriptive when discussing the practicalities of a style guide for a project. I have not found any adopted standard at all for layout, so I hope I can help with some suggestions in this chapter.

Object Naming Conventions

Object naming is really a different subject altogether from layout. There are tools now available to implement your code layout standard in the blink of an eye, but there is no equivalent tool to refactor the naming of all your SQL objects, to conform to a given standard (though SQL Refactor will help you with renaming tables).

Naming has to be done right, from the start. Because object naming is so bound up with our culture, it causes many arguments in development teams. There are standards for doing this (ISO/IEC 11179-5 – Naming and Identification Principles for Data Elements), but everyone likes to bring their own familiar rituals to the process. Here are a few points that cause arguments.

Tibbling

The habit most resistant to eradication is "Tibbling," the use of reverse Hungarian notation, a habit endemic among those who started out with Microsoft Access. A tibbler will prefix the name of a table with "tbl," thereby making it difficult to pronounce. So, for example, a tibbler will take a table that should be called Node, and call it tblNode. Stored procedures will be called something like spCreateCustomer and table-valued functions will be called tvfSubscription.

All this tibbling makes talking about your data difficult, but the habit is now, unfortunately, rather entrenched at Microsoft, in a mutated version that gives a PK_, FK_, IX_, SP_ or DF_ prefix to object names (but not mercifully to tables yet), so I doubt that it will ever be eradicated amongst SQL Server programmers.

Such object-class naming conventions have never been part of any national or international standard for naming data objects. However, there are well-established prefixes in DataWarehousing practice to make it possible to differentiate the different types of table (dim, fact, and so on).

Pluralizing

A pluralizer will always name a table after a quantity of entities rather than an entity. The Customer table will be called Customers, and Invoice will be Invoices. Ideally, the use of a collective name for the entities within a table is best, but failing that, the singular noun is considered better than the plural.

Abbreviating (or abrvtng)

An abbreviator will try to make all names as short as possible, in the mistaken belief that the code will run faster, take less space, or be, in some mystical sense, more efficient.

Heaving out the vowels (the "vowel movement") is a start, so that Subscription becomes Sbscrptn, but the urge towards the mad extreme will lead to Sn. I've heard this being called "Custing," after the habit of using the term Cust instead of Customer. To them, I dedicate Listing 1-1.

CREATE TABLE ## ( # INT ) 
DECLARE @ INT
SET @ = 8
INSERT  INTO ##
        ( # )
        SELECT  @ % 2
SELECT  *
FROM    ##
Listing 1-1: abrvtng mdnss.

This habit came from the old Fortran days when you could only use six characters at the most. SQL 92 allows 18 characters, but SQL Server has no practical limit, though you must keep it under 128 characters.

[Escaping]

Spaces are not allowed in object names, unless the name is escaped, so SQL names need some way of separating words. One could write customerAccounts, CustomerAccounts, customer_Accounts or Customer_Accounts. Yes, you need to make up your mind.

Desktop databases, such as Access, are more liberal about the character set you can use for object names, and so came the idea came of "escaping," "quoting," or delimiting such names so that they could be copied, without modification, into a full relational database.

Those of us who take the trouble to write legal SQL object names find the rash of square brackets that are generated by SSMS acutely irritating. Listing 1-2 shows some code that runs perfectly happily in SQL Server, purely because of the use of escaping with square brackets.

/* we see if we can execute a verse of Macauley's famous poem "Horatius." */

--create a table with a slightly unusual name
CREATE TABLE [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
  (
    [The horsemen and the footmen
    Are pouring in amain] INT ,
    [, hid by beech and pine,] VARCHAR(100)
  )    
--put a value into this table
INSERT INTO [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
        ( [The horsemen and the footmen
    Are pouring in amain] ,
          [, hid by beech and pine,]
        )
        SELECT  1 ,
                'an eagle's nest, hangs on the crest
    Of purple Apennine;'
 
/* now, with that preparation work done, we can execute the third verse */
SELECT  [The horsemen and the footmen
    Are pouring in amain]
FROM    [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
WHERE   [, hid by beech and pine,]
    LIKE 'an eagle's nest, hangs on the crest
          Of purple Apennine;'
Listing 1-2: Horatius and the square bracket.

It is true that "delimited" names used to be handy for non-Latin languages, such as Chinese, but nowadays you can use Unicode characters for names, so Listing 1-3 runs perfectly happily.

CREATE TABLE 中國數據庫表
   (
      我的主鍵 CHAR(2) NOT NULL
                                            PRIMARY KEY ,
      我的數據列 INTEGER NOT NULL
                                                   CHECK ( 我的數據列 > 0 )
    ) ;
Listing 1-3: Chinese tables.

Herein lies another horrifying possibility: SQL Server will allow you to use "shapes," as demonstrated in Listing 1-4.

CREATE TABLE "╚╦╩╗" ("└┬┴┐" NVARCHAR(10) )
DECLARE @ NVARCHAR(10)
SET @ = '═'
INSERT   INTO "╚╦╩╗"
         ("└┬┴┐" )
         SELECT replicate(@, 5)
SELECT *
FROM   "╚╦╩╗"
Listing 1-4: Shape tables.

The ISO ODBC standard allows quotation marks to delimit identifiers and literal strings. Identifiers that are delimited by double quotation marks can either be Transact-SQL reserved keywords or they can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers. This behavior can, mercifully, be turned off by simply by issuing: SET QUOTED_IDENTIFIER OFF.

Restricting

A habit that has crept into SQL from ex-Cobol programmers, I believe, is the use of a very restricted vocabulary of terms. This is rather like the development of cool street-argot with a highly restricted set of 400 words, rather than the 40,000 that are within the grasp of the normal adult. With SQL, this typically involves using words like GET, PUT' or SAVE, in a variety of combinations.

SQL is perfectly happy to oblige, even though the results are difficult to understand. Taking this to extremes, the code in Listing 1-5 is perfectly acceptable to SQL Server.

--first create a GetDate schema
CREATE SCHEMA GetDate
--and a GetDate table to go in it.
CREATE TABLE GetDate.GetDate
(
GetDate DATETIME,
[GetDate GetDate] DATETIME
)
GO
--and a function called GetDate
CREATE FUNCTION GetDate ( )
RETURNS TABLE
AS RETURN
  ( SELECT  getdate() AS [GetDate]
  )
GO
-- Now we can write some startlingly silly code
INSERT  INTO GetDate.GetDate
        ( GetDate.GetDate.GetDate.GetDate ,
          [GetDate GetDate]
        )
        SELECT  getdate() AS GetDate ,
                GetDate
        FROM    getdate()
-- but we can do far far siller stuff if we wanted
-- purely because there is no restriction on what 
-- goes between angle-brackets.       
CREATE FUNCTION [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate] ( )
RETURNS TABLE
AS RETURN
  ( SELECT  getdate() AS [GetDate]
  )
GO
 
INSERT  INTO GetDate.GetDate
        ( GetDate.GetDate.GetDate.GetDate ,
          [GetDate GetDate]
        )
        SELECT  getdate() AS GetDate ,
                GetDate
        FROM    [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
Listing 1-5: The dangers of restricting your SQL vocabulary.

A Guide to Sensible Object Names

The existing standards for naming objects are more concerned with the way of discussing how you name database objects, and the sort of ways you might document your decisions. We can't discuss here the complications of creating data definitions, which are important where organizations or countries have to share data and be certain that it can be compared or aggregated. However, the developer who is creating a database application will need to be familiar with the standard naming conventions for database entities, objects, constraints, routines, and relationships.

Hopefully, the developer will already have been provided with the standard data definitions for the attributes of the data elements, data element concepts, value domains, conceptual domains, and classification schemes that impinge on the scope of the application. Even so, there is still the task of naming things within the application context. For this, there are international standards for naming conventions, which are mostly taken from ISO 11179-5:

  • procedures should be a phrase, consisting of singular nouns and a verb in the present tense, to describe what they do (e.g. removeMultipleSpaces or splitStringToTable)
  • be consistent with the way you denote word-boundaries, the use of the underline character, the use of a capital letter or hyphen
  • tables, sets, views, and other collections should use a collective name, a name for a group of entities, such as "flock," "ledger," "team," "staff"
  • scalar names should be in the singular (e.g. "cost," "date," "zip")
  • any object name should use only commonly understood abbreviations, such as ZIP for "Zone Improvement Plan"
  • use standard and consistent postfixes (e.g. _ID, _name, _date, _quantity)
  • where there is no established business term in the organization, use commonly understood words for relationship tables (e.g. meeting, booking, marriage, purchase)
  • use capitalization consistently, as in written language, particularly where it is used for acronyms and other abbreviations, such as ID.
  • names should consist of one or more of the following components:
    • object class: the name can include just one "object class," which is the terminology used within the community of users of the application.
      Examples: Words like "Cost," "Member" or "Purchase" in data element names like EmployeeLastName, CostBudgetPeriod, TotalAmount, TreeHeightMeasure or MemberLastName
    • property term: these represent the category of the data.
      Examples: Total_Amount, Date, Sequence, LastName, TotalAmount, Period, Size, Height
    • qualifiers: these can be used, if necessary, to describe the data element and make it unique within a specified context; they need appear in no particular order, but they must precede the term being qualified; qualifier terms are optional
      Examples: Budget_Period, FinancialYear, LastName
    • the representation term: this describes the representation of the valid value set of the data element. It will be a word like "Text," "Number," "Amount," "Name," "Measure" or "Quantity." There should be only one, as the final part of the name, and it should add precision to the preceding terms.
      Examples: ProductClassIdentifier, CountryIdentifierCode, ShoeSizeMetric

The type of separator used between words should be consistent, but will depend on the language being used. For example, the CamelCase convention is much easier for speakers of Germanic or Dutch languages, whereas hyphens fit better with English.

It isn't always easy to come up with a word to attach to a table.

Not all ideas are simply expressed in a natural language, either. For example, "women between the ages of 15 and 45 who have had at least one live birth in the last 12 months" is a valid object class not easily named in English.
ISO/IEC 11179-1:2004(E): Page 19.

You can see from these simple rules that naming conventions have to cover semantics (the meaning to be conveyed), the syntax (ordering items in a consistent order), lexical issues (word form and vocabulary), and uniqueness. A naming convention will have a scope (per application? company-wide? national? international?) and an authority (who supervises and enforces the conventions?).

Code Layout

The layout of SQL is important because SQL was always intended to be close to a real, declarative human sentence, with phrases for the various parts of the command. It was written in the days when it was considered that a computer language should be easy to understand.

In this section, we will deal purely with the way that code is laid out on the page to help with its maintenance and legibility.

Line-breaks

SQL code doesn't have to be broken into short lines like a Haiku poem. Since SQL is designed to be as intelligible as an English sentence, it can be written as an English sentence. It can, of course, be written as a poem, but not as a thin smear down the left-hand side of the query window. Line-breaking (and indenting) is done purely to emphasize the structure of SQL, and aid readability.

The urge to insert large numbers of line-breaks comes from procedural coders where a vertical style is traditional, dating back to the days of Fortran and Basic. An advantage of the vertical style is that, when an error just reports a line-number, it takes less time to work out the problem. However, it means an over-familiarity with the scroll-bar, if the routine runs to any length.

Line breaks have to be inserted at certain points (I rather like to have a line-break at around the 80th character), and they shouldn't be mid-phrase. However, to specify that there must always be a line-break between each phrase (before the FROM, ON, and WHERE clauses, for example) can introduce an unnecessary amount of white space into code. Such indenting should never become mere ritual activity to make things look neat, like obsessively painting the rocks in front of your house with white paint.

Indenting

Code without indenting is very difficult to follow. Indentation follows a very similar practice to a structured document, where the left margin is indented according to the nesting of the section heading. There should be a fixed number of spaces for each level of nesting.

Generally, the use of tabs for indenting has resulted in indenting that is way too wide. Of course, written text can have wide indents, but it isn't done to around eight levels, skidding the text hard against the right-hand side of the page. Usually, two or three spaces is fine.

It is at the point where we need to decide what comprises a change in the nesting level that things get difficult. We can be sure that, in a SELECT statement, all clauses are subordinate to the SELECT. Most of us choose to indent the FROM or the WHERE clause at the same level, but one usually sees the lists of columns indented. On the other hand, it is quite usual to see AND, ON, ORDER BY, OR, and so on, indented to the next level.

What rules lie behind the current best practice? Many of us like to have one set of rules for DDL code, such as CREATE TABLE statements, and another for DML such as INSERT, UPDATE or SELECT statements. A CREATE TABLE statement, for example, will have a list of columns with quite a lot of information in them, and they are never nested, so indenting is likely to be less important than readability. You'd probably also want to insist on a new line after each column definition. The use of curly brackets in DDL also makes it likely that indenting will be used less.

Formatting lists

Lists occur all over the place in code. As in printed text, you can handle them in a number of different ways. If, for example, you are just listing entities, then you'd do it like this. I like many French cheeses, including Abbaye de Belloc, Baguette Laonnaise, Brie de Melun, Calenzana, Crayeux de Roncq, Esbareich, Frinault, Mixte, Pavé du Berry, Port-Salut, Quercy Petit, Regal de la Dombes, Sainte Maure, Sourire Lozerien, Truffe and Vignotte. Now, no typesetter would agree to arrange this in a vertical list, because the page would contain too much white space…

I like many French cheeses, including:

  • Abbaye de Belloc
  • Baguette Laonnaise
  • Brie de Melun
  • Calenzana
  • Crayeux de Roncq
  • Esbareich
  • etc.

... and they'd be most unlikely to want to put commas at the beginning of list elements. However, if the list elements consisted of longer strings, then it would be perfectly acceptable. In the same way, the rules for formatting SQL have to take into account the type of SQL statement being formatted, and the average length of each list element.

Punctuation

Commas, used as list separators, are often put at the beginning of lines. I realize that it makes the "commenting out" of list members easier during development, but it makes it difficult for those of us who are used to reading English text in books. Commas come at the end of phrases, with no space before them, but if they are followed by a word or phrase on the same line, then there is a space after the comma.

Semicolons are a rather more unfamiliar punctuation mark but their use has been a part of the SQL Standard since ANSI SQL-92 and, as statement terminators, they are being seen increasingly often in SQL.

Generally speaking, their use in T-SQL is recommended but optional, with a few exceptions. They must be used to precede CTEs and Service Broker statements when they are not the first statement in the batch, and a trailing semicolon is required after a MERGE statement.

Capitalization

Before we start, I'd like to define what I mean by the various terms.

  • This_Is_Capitalized
  • this_is_lowercase (or minuscule)
  • this_Is_CamelCase
  • THIS_IS_UPPERCASE – (or majuscule).

Schema objects are, I believe, better capitalized. I would strongly advise against using a binary or case-sensitive collation for the database itself, since this will cause all sorts of unintended errors. A quirk of all European languages is that words mean the same thing, whether capital or lowercase letters are used. Uppercase, or majuscule, lettering was used exclusively by the Roman Empire, and lowercase, or minuscule, was developed later on, purely as a cursive script. The idea that the case of letters changed the meaning of words is a very recent novelty, of the Information Technology Age. The idea that the use uppercase is equivalent to shouting, may one day be adopted as a convention, probably at around the time that "smileys" are finally accepted as part of legitimate literary punctuation.

Of course, one would not expect SQL programmers to be so perverse as to do this sort of thing, but I've seen C# code that approaches the scale of awfulness demonstrated in Listing 1-6.

CREATE DATABASE casesensitive
ALTER DATABASE casesensitive COLLATE SQL_Latin1_General_CP1_CS_AS
USE casesensitive
CREATE TABLE thing
  (
    Thing INT IDENTITY(1, 1) ,
    tHing VARCHAR(20) ,
    thIng INT NOT NULL ,
    thiNg FLOAT NOT NULL ,
    thinG DATETIME NOT NULL
  )
INSERT  INTO thing
        ( tHing ,
          thIng ,
          thiNg ,
          thinG
        )
        SELECT  'thing' ,
                1 ,
                34.659 ,
                '1 Oct 2009'
SELECT  *
FROM    thing
DROP TABLE thing
Listing 1-6: A capital idea.

Getting off the Fence

I wouldn't want to impose my views on anyone else. However, if you are looking for recommendations, here's what I usually suggest. I'd stick to the conventions below.

  • Keep your database case-insensitive, even if your data has to be case-sensitive, unless you are developing in a language for which this is inappropriate.
  • Capitalize all the Scalars and Schema object names (e.g. Invoice, Basket, Customer, CustomerBase, Ledger).
  • Uppercase all reserved words (such as SELECT, WITH, PIVOT, FROM, WHERE), including functions and data types.
  • Put a line-break between list items only when each list item averages more than thirty or so characters.
  • Put block delimiters (such as BEGIN and END) on a new line by themselves, correctly indented.
  • Put line breaks within SQL statements before the clause (FROM, ON, WHERE, HAVING, GROUP BY) only where it aids clarity in long statements, but not in every case.
  • Use the semicolon to aid the reading of code, even where SQL syntax states that it is only optional.
  • Use an increased indent for subordinate clauses if the ON, INTO, and HAVING statement is at the start of the line.

For sheer practicality, I'd opt for a layout that can be achieved automatically by your favorite code-layout tool (I use SQL Refactor and SQL Prompt, but there are several others). There is nothing more irritating than to find that someone has trashed a beautifully laid-out procedure by mangling it with a badly set up layout tool.

I tend to write my SQL fast and sloppily, to get some initial results quickly, and then refine and rewrite the code until it is fast and efficient. At that point, it is usually a mess, and it is very satisfying to run it through a layout tool to smarten it up. In fact, some time ago, before layout tools existed for SQL, I created a stored procedure that tidied up SQL code. It gradually ended up as the SQL Prettifier (www.simple-talk.com/prettifier), repurposed to render SQL in HTML, and with the formatting part taken out once SQL Refactor appeared. A tool like this can save a lot of inevitable arguments amongst developers as to the "correct" way to format SQL code.

Listing 1-7 shows the table-valued function from AdventureWorks, reformatted according to my preferences but, I suspect, perfectly horrible to anyone with strong feelings on the subject. The routine should, of course, have a structured header with a summary of what it does, and examples of its use, but that is a story for another chapter (Chapter 2, in fact).

CREATE FUNCTION dbo.ufnGetContactInformation (@ContactID INT)
RETURNS @retContactInformation TABLE (
  -- Columns returned by the function
  ContactID INT PRIMARY KEY NOT NULL,
  FirstName NVARCHAR(50) NULL,
  LastName NVARCHAR(50) NULL,
  JobTitle NVARCHAR(50) NULL,
  ContactType NVARCHAR(50) NULL)
AS /* Returns the first name, last name, job title and contact 
type for the specified contact.*/
 
BEGIN
  DECLARE @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @JobTitle NVARCHAR(50),
    @ContactType NVARCHAR(50);
   -- Get common contact information
  SELECT @ContactID = ContactID, @FirstName = FirstName,
         @LastName = LastName
 FROM Person.Contact WHERE ContactID = @ContactID;
 
  /* now find out what the contact's job title is, checking the 
  individual tables.*/
  SET @JobTitle 
    = CASE 
        WHEN EXISTS -- Check for employee
         ( SELECT * FROM HumanResources.Employee e
             WHERE e.ContactID = @ContactID )
          THEN 
             (SELECT Title FROM HumanResources.Employee
               WHERE ContactID = @ContactID)
        WHEN EXISTS -- Check for vendor
          ( SELECT * FROM Purchasing.VendorContact vc
              INNER JOIN Person.ContactType ct
                 ON vc.ContactTypeID = ct.ContactTypeID
            WHERE vc.ContactID = @ContactID )
          THEN 
             (SELECT ct.Name FROM
                            Purchasing.VendorContact vc
                 INNER JOIN Person.ContactType ct
                    ON vc.ContactTypeID =
                                     ct.ContactTypeID
              WHERE vc.ContactID = @ContactID)
        WHEN EXISTS -- Check for store
          ( SELECT * FROM Sales.StoreContact sc
               INNER JOIN Person.ContactType ct
                  ON sc.ContactTypeID = ct.ContactTypeID
            WHERE sc.ContactID = @ContactID )
          THEN
             (SELECT ct.Name FROM Sales.StoreContact sc
                INNER JOIN Person.ContactType ct
                   ON sc.ContactTypeID =
                                    ct.ContactTypeID
              WHERE ContactID = @ContactID)
        ELSE NULL
      END ;
 
  SET @ContactType 
    = CASE-- Check for employee
        WHEN EXISTS 
          ( SELECT * FROM HumanResources.Employee e
              WHERE e.ContactID = @ContactID )
        THEN 'Employee'
        WHEN EXISTS -- Check for vendor
          ( SELECT * FROM Purchasing.VendorContact vc
              INNER JOIN Person.ContactType ct
                 ON vc.ContactTypeID = ct.ContactTypeID
            WHERE vc.ContactID = @ContactID )
        THEN 'Vendor Contact'
        WHEN EXISTS  -- Check for store
          ( SELECT * FROM Sales.StoreContact sc
               INNER JOIN Person.ContactType ct
                 ON sc.ContactTypeID = ct.ContactTypeID
            WHERE sc.ContactID = @ContactID )
        THEN 'Store Contact'
        WHEN EXISTS  -- Check for individual consumer
          ( SELECT * FROM Sales.Individual i
               WHERE i.ContactID = @ContactID )
        THEN 'Consumer'
      END ;
 -- Return the information to the caller
  IF @ContactID IS NOT NULL 
    BEGIN
      INSERT INTO @retContactInformation
        SELECT  @ContactID, @FirstName, @LastName,
               @JobTitle,@ContactType ;
    END ;
  RETURN ;
END ;
GO
Listing 1-7: The ufnGetContactInformation function, reformatted according to the formatting guidelines presented in this chapter.

Summary

Before you start on a new database application project, it is well worth your time to consider all the layout and naming issues that have to be covered as part of the project, and finding ways of automating the implementation of a standard, where possible, and providing consistent guidelines where it isn't. Hopefully this chapter has provided useful guidance in both cases.

For further reading on this topic, try the links below.

  • Transact-SQL Formatting Standards (Coding Styles) (http://tiny.cc/1c7se) – Rob Sheldon's popular and thorough description of all the issues you need to cover when deciding on the way that SQL code should be laid out.
  • SQL Code Layout and Beautification (www.simple-talk.com/sql/t-sql-programming/sql-code-layout-and-beautification/) – William Brewer's sensible take on the subject, from the perspective of a programmer.
  • ISO/IEC 11179 (http://metadata-stds.org/11179/) – the international standard for vocabulary and naming conventions for IT data.
  • Joe Celko's SQL Programming Style (http://tiny.cc/337pl) – the first book to tackle the subject in depth, and still well worth reading. You may not agree with all he says, but reading the book will still improve your SQL Coding, as it is packed with good advice.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Red Gate Books

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 10 Nov 2010
Article Copyright 2010 by Red Gate Books
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid