What SQL coding standard do you follow? [closed]

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

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:

Avoid useless typing.

Use aliases for tables/views. Always. And make them sensible aliases.

Indent code in some way.

Avoid quotations (yes, this is why I hate Django)

Use join syntax

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

What SQL coding standard do you follow? [closed]

Related

Recent Posts