What SQL coding standard do you follow? [closed]

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

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

Answer 2

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 3

Data Types to be used: We should use only following data types:

• INT
• BIGINT
• SMALLINT
• VARCHAR
• BIT
• DATETIME

Give default value for BIT datatype. It should not be nullable Table and Column names should never be in lower case. It should describe its purpose. Avoid using short forms. While creating a table FK and PK to be well thought and defined. Variables names should start with one/two letter indicating its data type in lower case. For e.g. INT variable should begin with i. Name should be descriptive and short forms should be avoided. It each word should start with capital letter followed by all small letters.

E.g.

Correct way: – iTotalCount

Incorrect way: – xyz

Table columns used with “WHERE” clause inside stored procedures should be indexed/keyed. This will increase the speed of data processing. Ordering of parameters in WHERE clause should be done properly. Primary key/index should precede bit variables. E.g.:- An index is created on combination of columns (REF_ID, T_TYPE_STR, CNUMBER, TLOG_ID)

— Correct way where Indexed keys are used in sequence in ‘WHERE’ clause

SELECT REF_ID,T_TYPE_STR,C_NUMBER,TLOG_ID

FROM T_T_DATA_tbl

WHERE REF_ID = 1

AND LOG_ID = ‘4042654’

AND T_TYPE_STR = ‘SA’

AND CNUMBER = ‘10702’

–Incorrect way

SELECT REF_ID, T_TYPE_STR, CNUMBER, LOG_ID

FROM T_T_DATA_tbl

WHERE LOG_ID = ‘4042654’

AND T_TYPE_STR = ‘SA’

While writing a stored procedure we should have description section at the beginning which will contain Author:

Creation date:

Description:

If any sp is modified this section should be appended with

Modified by:

Modified on:

Description:

ROW_INSERTION_DATE_TIME AND ROW_UPDATION_DATE_TIME column should have default values as GETDATE().

More at : http://www.writeulearn.com/sql-database-coding-standards/

Answer 4

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 5

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 6

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