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