What SQL coding standard do you follow?

Is there any widely used SQL coding standard out there? SQL is little bit different from C/C++ type of programming languages. Really don't know how to best format it for readability.

Wouldn't call it coding standard - more like coding style

SELECT
    T1.col1,
    T1.col2,
    T2.col3
FROM
    table1 T1
    INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
    T1.col1 = 'xxx'
    AND T2.Col3 = 'yyy'

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular

I like the comma preceding way:

SELECT
      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4
FROM
    tablename
WHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

it makes it the easiest to read and debug in my opinion.

I know this is long, but bear with me, it's important. This question opened a cool can of worms. And if you don't like database blocks, read on.

And, before anyone thinks about knocking down my response, please see the following article and connected articles to it about locking, and recompiles; two of the most damaging resources hits on a SQL database.

http://support.microsoft.com/kb/263889

I can type pretty quickly, and I don't like to type any more than the next person. But the points below I follow extremely closely, even if it is more typing. So much that I've built my own SP apps to do it for me.

The points I bring up are really important! You might even say to yourself, "are you kidding, that's not an issue", well, then you didn't read the articles above. AND, it's totally moronic that M$ would put these points in as NOTEs. These issues to me should be BOLD and SCREAMING.

I also do a lot of coding to build my basic scripts using C# applications to speed up development and these practices are very sound (10 years worth) to make scripting SPs easier and especially faster.

There are more than this, but this is what I do for the first 60% of everything.

---

Best practices

• Use the brackets around objects, so the query engine excplicitly knows a field when it sees it
• Use THE SAME CASE as table object names and field names
• When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
• Reference the owner of the object so security is explicitly known and doesn't have to be figured out
• DON'T us "sp_" as this refers to system stored procs, and overhead
• Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

Preferences

• Prefix stored procs with proc
• Suffix every stored proc with SEL, UPD, DEL, INS (or SELECT, UPDATE, DELETE, INSERT)
• Capitalize reserved words
• Main keywords on new line (scripting)
• Use commas before columns (scripting)
• Prefix views with vw
• Don't prefix tables
• Table names singular
• Add a suffix to the standard names like "_ByPK", "_OrderByLastName", or "_Top15Orders" for variations on the stock SP

---

Select

> CREATE PROC [dbo].[procTable_SEL] > AS > SET NOCOUNT ON > SELECT > [Column1] = T1.[col1] > , [Column2] = T1.[col2] > , [Column3] = T2.[col3] > FROM [dbo].[Table] T1
> INNER JOIN ON [dbo].[Table2] T2 ON T1.ID = T2.ID > WHERE > T1.[col1] = 'xxx' > AND T2.[Col3] = 'yyy' > SET NOCOUNT OFF > GO

---

Update

> CREATE PROC [dbo].[procTable_UPD] > AS > SET NOCOUNT ON > UPDATE t1 SET > [Column1] = @Value1 > , [Column2] = @Value2 > , [Column3] = @Value3 > FROM [dbo].[Table1] T1 > INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID] > WHERE > T1.[col1] = 'xxx' > AND T2.[Col3] = 'yyy' > SET NOCOUNT OFF > GO

---

Insert

> CREATE PROC [dbo].[procTable_INS] > AS > SET NOCOUNT ON > INSERT INTO [Table1] ( > [Column1] > , [Column2] > , [Column3] > ) > VALUES ( > @Value1 > , @Value2 > , @Value3 > ) > SET NOCOUNT OFF > GO

OR

> CREATE PROC dbo.procTable_INS > AS > SET NOCOUNT ON > INSERT INTO [table1] ( > [Column1] > , [Column2] > , [Column3] > ) > SELECT > [Column1] = T1.col1 > , [Column2] = T1.col2 > , [Column3] = T2.col3 > FROM dbo.Table1 T1
> INNER JOIN ON Table2 T2 ON T1.ID = T2.ID > WHERE > T1.[col1] = 'xxx' > AND T2.[Col3] = 'yyy' > SET NOCOUNT OFF > GO

---

Delete

> CREATE PROC dbo.procTable_DEL > AS > SET NOCOUNT ON > DELETE > FROM [dbo].[Table1] T1 > INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID] > WHERE > T1.[col1] = 'xxx' > AND T2.[Col3] = 'yyy' > SET NOCOUNT OFF > GO

If you google, there are plenty of coding standards out there. For example,

Database Coding Standard and Guideline

and

SQL SERVER Database Coding Standards and Guidelines Complete List

From a really very nice blog on PostgreSQL, but this topic is applicable in general:

Maintainable queries - my point of view (depesz.com)

> ...I decided that my priorities for writing maintainable queries: > > 1. Avoid useless typing. >
> 2. Use aliases for tables/views. > Always. And make them sensible > aliases. >
> 3. Indent code in some way. >
> 4. Avoid quotations (yes, this is why I > hate Django) >
> 5. Use join syntax >

I do agree with capitalization of reserved words and every other identifier, except my own.

I personally don't like to prefix a stored procedure name with sp_ - it is redundant, IMO. Instead, I like to prefix them with a "unit of functionality" identifier. e.g. I'll call the sprocs to deal with orders order_Save, order_GetById, order_GetByCustomer, etc. It keeps them all logically grouped in management studio and makes it harder to pick the wrong one. (GetOrderByProduct, GetCustomerById, etc...)

Of course, it is personal preference, other people may prefer to have all the Get sprocs together, all the Save ones, etc.

Just my 2c.

I generally keep very little per line, ie:

select
    col1,
    col2,
    col3
from
    some_table tabl1
where
    col1 = 'some'
and 
(
    col2 = 'condition'
or  col2 = 'other'
)

Google for sql pretty printer or look here. I haven't tried it out myself, but it gives you a good start. Most commercial tools like Toad have a "formatting" option which helps, too.

Play around with www.sqlinform.com - I recommend using the ANSI-92 standard, and then pretty it up with that site.

SELECT c.id
	 , c.name
	 , c.folder
	 , cs.num_users active_members
	 , cs.num_videos

  FROM campaign c
  JOIN campaign_stats cs
	ON cs.campaign_id = c.id
  JOIN (SELECT _c.id
             , _c.name

	      FROM campaign _c
		 WHERE _c.type = 9) t_c 
    ON t_c.id = c.id

 WHERE c.id IN (1,2,3)
   AND cs.num_videos > 10

This works pretty good for us.

This actual query doesn't make much sense since I tried to build it quickly as an example... but that's not the point.

• t_c stands for category table sub-query or "temp category".

• _underscoring of stuff inside sub-queries.

• alias column names to make sense in the context of the query. e.g. "active_members"

• putting commas at the beginning of the new lines makes it easier to build dynamic queries:

    $sql .= ", c.another_column"
  

• everything else is straightforward.

Anything in blue is upper case SELECT, DELETE, GO, etc

Table names are singular like the table that holds our customers would be the customer table

Linking tables are tablename_to_tablename

use _ between works in table names and parameters

example

BEGIN
	SELECT
    	Company.ID AS Company_ID,
		Company.Client_Name,
		Company.Website,
		Office.Office_Name
    FROM
		Company_Office WITH(NOLOCK)
		INNER JOIN Company WITH(NOLOCK) ON Company_Office.Company_ID = Company.ID
    WHERE
END

I'm surprised that the coding style I've used for almost 20 years isn't on this list:

  SELECT column1,
         column2,
         column3,
         COALESCE(column4, 'foo') AS column4
    FROM tablename
   WHERE column1 = 'bar'
ORDER BY column1,
         column2

I find this the absolutely most readable, but I admit that it is tedious to type. If right aligning the keywords is too much, I'd opt for left aligning them:

SELECT   column1,
         column2,
         column3,
         COALESCE(column4, 'foo') AS column4
FROM     tablename
WHERE    column1 = 'bar'
ORDER BY column1,
         column2

create table
    #tempTable (
        col1 int,
        col2 int,
        col3 int
    )

insert into
    #tempTable (
        col1,
        col2,
        col3
    )
    select
        col1,
        col2,
        col3
    from
        Table3
        inner join Table2
            on Table1.col1 = Table2.col2
    where col1 = 5

select
    col2,
    case when col1 = 3
        then 'something'
        else 'somethingelse'
    end
from #tempTable
where
    col1 = 5
    and (
        col2 = 5
        or col3 in (
            select field
            from Table2
            where
                somecol = 2
                and othercol = 5
        )
    )