我的SQL编码规范

T-SQL Coding Guidelines

Naming Conventions

1.        Using uppercase for all T-SQL reserved words

DECLARE

@ParentId     INT,

       @Owner NVARCHAR(128),

       @Name         NVARCHAR(128),

       @Value VARBINARY(MAX),

       @ValueStr     NVARCHAR(4000) = NULL

2.        Name tables in singular forms

--correct

CREATE TABLE dbo.MyProperty

(

...

)

 

--avoid

CREATE TABLE dbo.MyProperties

(

...

)

3.        Using Camel casing for all user defined objects, such as tables, views, functions, triggers and stored procedures

 

CREATE PROCEDURE dbo.DPMAddNewValue

4.        Avoid abbreviations and single character names

--correct

DECLARE @ProjectName NVARCHAR(2000)

 

--avoid

DECLARE @PN NVARCHAR(2000)

--or

DECLARE @p NVARCHAR(2000)

 

5.        Always assign schema to user defined objects when defining.

CREATE PROCEDURE dbo.Proc1

(

      @Param1 INT

)

AS

BEGIN

      SELECT 1

END

 

 

CREATE FUNCTION dbo.Func1

(

      @Param1 INT

)

RETURNS INT

AS

BEGIN

      RETURN 1

END

6.        Always include the schema when referencing an object

SELECT col1 FROM dbo.Table1

7.        When joining tables, always alias tables with lower cases names and identify all columns with aliases and always alias using the AS keyword.

--correct

SELECT a.Col1, a.Col2, b.Col1, b.Col2

FROM TableA AS a

INNER JOIN TableB AS b ON a.Col1 = b.Col1

WHERE a.Col1 = 1

       AND b.Col2 = 2

      

      

--avoid

SELECT TableA.Col1, TableA.Col2, TableB.Col1, TableB.Col2

FROM TableA

INNER JOIN TableB  ON TableA.Col1 = TableB.Col1

WHERE TableA.Col1 = 1

       AND TableB.Col2 = 2

Coding

1.        Always use multi-lines with indentation unless the SQL statement is so short that it can be show in one line.

SELECT

     col1, col2, col3,

     col4, col5, col6,

     SUM(col7)

FROM dbo.Table1

WHERE col1 = 1

     AND col2 = 2

     AND col3 = 3

GROUP BY col1, col2, col3, col4, col5, col6

ORDER BY col1, col2, col3

2.        Using space and/or return to separate valuing statements and fields

–correct

SELECT

     col1, col2, col3,

     col4, col5, col6,

     SUM(col7)

FROM dbo.Table1

WHERE col1 = 1

     AND col2 = 2

     AND col3 = 3

GROUP BY col1, col2, col3, col4, col5, col6

ORDER BY col1, col2, col3

–avoid

SELECT

     col1, col2, col3,

     col4, col5, col6,

     SUM(col7)

FROM dbo.Table1

WHERE col1 = 1

     AND col2 = 2

     AND col3 = 3

GROUP BY col1, col2, col3, col4, col5, col6

ORDER BY col1, col2, col3

3.        When using IF ELSE, WHILE statements, always begin a new scope with BEGIN and END.

 

–correct

DECLARE @I INT

 

IF 1=2

BEGIN

       SET @I = 1

 

       WHILE 1 = 1

       BEGIN

…

       END

END

ELSE

BEGIN

       SET @I = 2

END

 

--avoid

DECLARE @I INT

 

IF 1=2

SET @I = 1

ELSE

SET @I = 2

4.        Always use parentheses to scope parameters and use BEGIN and END to scope codes when defining stored procedures

--correct

CREATE PROCEDURE dbo.Proc1

(

       @Param1              INT,

       @Param2              DATETIME,

       ...

)

AS

BEGIN

 SELECT 1

END

 

--avoid

CREATE PROCEDURE dbo.Proc1

@Param1              INT,

@Param2              DATETIME,

...

AS

SELECT 1

...

5.        Avoid joining tables using where clause, instead using ANSI syntax for joining.

--correct

SELECT a.Col1, a.Col2, b.Col1, b.Col2

FROM TableA AS a

INNER JOIN TableB AS b ON a.Col1 = b.Col1

WHERE a.Col1 = 1

       AND b.Col2 = 2

      

      

--avoid

SELECT a.Col1, a.Col2, b.Col1, b.Col2

FROM TableA AS a, TableB AS b

WHERE a.Col1 = b.Col1 --the joining condition

       AND a.Col1 = 1

       AND b.Col2 = 2

6.        Avoid using RIGHT join – rewrite to LEFT join.

--correct

SELECT a.Col1, a.Col2, b.Col1, b.Col2

FROM TableA AS a

LEFT JOIN TableB AS b ON a.Col1 = b.Col1

WHERE a.Col1 = 1

       AND b.Col2 = 2

      

--avoid

SELECT a.Col1, a.Col2, b.Col1, b.Col2

FROM TableB AS b

RIGHT JOIN TableA AS a ON a.Col1 = b.Col1

WHERE a.Col1 = 1

       AND b.Col2 = 2

 

7.        Avoid using @@ERROR function to handle exceptional states. Instead using TRY/CATCH construct TRY/CATCH is introduced in SQL Server2005.

8.        Avoid using T-SQL code arbitrarily; try to package them in Views, Procedures, Functions and Batches.  Try to use parameterized SQL code as far as possible.

9.        Use Table-valued variables or the new CTE (Common Table Expression) construct instead of creating Temporary Tables. If you need advanced indexing functionality on the Temporary Table, create the Table as a physical entity on the database used, prefixing it with Temp_.

10.    Always individually name fields in SELECT and INSERT statements

CREATE TABLE dbo.T1

(

      Col1 INT,

      Col2 VARCHAR(50),

      Col3 DATETIME

)

 

--correct

INSERT INTO  dbo.T1 (Col1, Col2, Col3)

VALUES (1,'2',GETDATE())

 

--avoid

INSERT INTO  dbo.T1

VALUES (1,'2',GETDATE())

 

11.    Avoid using dynamic SQL in Procedures.

--avoid

CREATE PROCEDURE dbo.Proc1

(

      @Param1 INT

)

AS

BEGIN

      sp_executesql N'SELECT 1'

END

 

12.    Avoid using PRINT statements or debug parameters in procedures; instead use the debug functionality of the Visual Studio.

CREATE PROCEDURE dbo.Proc1

(

      @Param1 INT

)

AS

BEGIN

      SELECT 1

      --avoid

      PRINT N'SELECT 1'

END

Performance best practice

1.        Avoid using SELECT *, it will slightly reduce performance

·         Anytime make sure you need all the fields in the table(s)

·         Select * is more easily to cause full table scan and fields lookup in SQL engine, in many cases more IO than needed is performed.

2.        Avoid using function on left side in a statement in where statements

--correct

SELECT

    IntCol, VarcharCol, DateCol

FROM Table1

WHERE IntCol =  CONVERT(INT,'123456')

    AND DateCol = CONVERT(DATETIME,'2008-08-08 20:08:08',120)

 

--avoid

SELECT

    IntCol, VarcharCol, DateCol

FROM Table1

WHERE CONVERT(VARCHAR(10),IntCol) = '123456'

    AND CONVERT(VARCHAR(20),DateCol,120) = '2008-08-08 20:08:08'

3.        Avoid implicit data conversion as far as possible

 

--correct

DECLARE @I      INT,

        @Nvchr  NVARCHAR(100)

SET @I = 1

SET @Nvchr = N'A'

 

--avoid

DECLARE @I      INT,

        @Nvchr  NVARCHAR(100)

SET @I = '1'

SET @Nvchr = 'A'

 

 

4.        Use stored procedures to execute SQL batch as far as possible

5.        Set the NOCOUNT state as the first statement in all procedures where you don’t need record count returned.

CREATE PROCEDURE dbo.Proc1

(

    @Param1 INT

)

AS

BEGIN

    --correct

    SET NOCOUNT ON

    SELECT 1

END

6.        Avoid operating (selecting, inserting, updating or deleting) too many records at one time, this will cause table lock for a long time. Try to operate in loop by small pieces.

--avoid

DELETE dbo.BigTable

WHERE ID BETWEEN 1 AND 10000

 

--recommend

--loop 100 time

DELETE dbo.BigTable

WHERE ID BETWEEN 1 AND @LoopVariable (100,200,...,10000)

7.        Try to avoid using cursor as far as possible

8.        Change Image field to VarBinary(Max) and Text/NText field to Varchar(Max)/NVarchar(max) as they are recommended data types in SQL Server2005/2008