What SQL coding standard do you follow?

Question

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.

Answer 1

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

Answer 2

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'
)

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

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

Answer 12

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
        )
    )