Naming Conventions and Style of T-SQL

1. Use upper case for all T-SQL constructs, except Types:

SELECT MAX(MyField) FROM MyTable

2. User lower case for all T-SQL Types and usernames:
DECLARE @MyVariable int

3. Use Camel casing for all UDO’s:
CREATE TABLE dbo.MyTable
(
MyField int
)

4. Avoid abbreviations and single character names
--Correct
DECLARE @Counter int

--Avoid
DECLARE @C int

5. UDO naming must confer to the following regular expression ([a-zA-Z][a-zA-Z0-9]+) - in short don’t use any special or language dependent characters to name objects. Constraints can use the underscore character.

--Avoid

CREATE TABLE dbo.[User Information]

6. Use the following prefixes when naming objects:

• usp - User Stored Procedures

• svf - Scalar Valued Functions

• tvf - Table Valued Functions

• vi - Views

• FK_ - Foreign keys

• DF_ - Default constraints

• IX_ - Indexes

CREATE PROCEDURE dbo.uspMyProcedure AS (...)

CREATE FUNCTION dbo.svfMyFunction

(...)

RETURNS int

AS

(...)

CREATE FUNCTION dbo.tvfMyFunction

(...)

RETURNS TABLE

AS

(...)

CREATE VIEW dbo.viMyView AS (...)

7. Name tables in the singular form:

--Correct

CREATE TABLE dbo.Address

--Avoid

CREATE TABLE dbo.Addresses

8. Tables that map many-to-many relationships should be named by concatenating the names of the tables in question, starting with the most central table’s name.

9. Primary and Foreign key fields are postfixed with ID.

--Correct

CREATE TABLE dbo.[User]

(

UserID int NOT NULL,

AddressID int NOT NULL –-Foreign key

)

--Avoid

CREATE TABLE dbo.[User]

(

UserID int NOT NULL,

AddressFK int NOT NULL –-Fieldname indicates its use as a foreign key

)

10. Avoid naming fields in a way that indicates its use as a foreign key.

--Avoid

CREATE TABLE dbo.[UserAddress]

(

UserFK int NOT NULL,

AddressFK int NOT NULL

)

11. Name Stored Procedures as [schema] .[usp][Object][Operation].

When creating Procedures to wrap single INSERT/UPDATE/DELETE statements, operation should be Insert, Update and Delete respectively.

12. Always assign schema to UDO’s when defining.

--Correct

CREATE TABLE dbo.MyTable (...)

--Avoid

CREATE TABLE MyTable (...)

13. Always include the schema when referencing an object:

--Correct

SELECT * FROM dbo.MyTable (...)

--Avoid

SELECT * FROM MyTable (...)

14. Properly arrange statements: Either use one-liners without indentation or multi-liners with indentation. Don’t mix the two.

--Correct one-liner

SELECT * FROM dbo.MyTable

--Correct multi-liner

SELECT *

FROM dbo.MyTable

WHERE MyTableID IN

(

SELECT MyForeignTableID

FROM dbo.MyForeignTable

)

AND MyColumn > 1

--Avoid

SELECT *

FROM dbo.MyTable --Missing indentation

WHERE MyField > 1 AND --Misplaced AND

Myfield <>

--Avoid mixing multiline and singleline expressions

SELECT * FROM dbo.MyTable

WHERE MyField > 1

15. When creating local scope always indent:

BEGIN

(...)

END

16. When using parenthesis around multi-line expressions, always put them on their own lines:

--Correct

RETURN

(

(...)

)

--Avoid

RETURN (

(...) )

17. When using IF statements, always BEGIN new scope:

--Correct

IF(1 > 2)

BEGIN

(...)

END

ELSE

BEGIN

(...)

END

--Avoid

IF(1 > 2)

(...)

ELSE

(...)

18. Always create scope when defining Procedures and multi statement Functio

--Correct

CREATE PROCEDURE dbo.uspMyProcedure

AS

BEGIN

(...)

END

--Avoid

CREATE PROCEDURE dbo.uspMyProcedure

AS

(...)

19. When joining always identify all columns with aliases and always alias using the AS keyword.

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON U.AddressID = A.AddressID

--Avoid

SELECT U.Surname,

Street –-Missing alias

FROM Users U –-Missing AS

JOIN dbo.Address ON U.AddressID = dbo.Address.AddressID –-Missing Alias

20. Avoid joining in the where clause, instead use ANSI syntax for joining. Include the reference key last:

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON A.AddressID = U.AddressID

--Avoid

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U,

dbo.Address AS A

WHERE U.AddressID = A.AddressID –-Joins in the WHERE clause

21. Avoid using RIGHT joins – rewrite to LEFT joins.

22. When doing INNER JOIN’s, avoid using the INNER keyword:

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON A.AddressID = U.AddressID

--Avoid

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

INNER JOIN dbo.Address AS A ON A.AddressID = U.AddressID

23. When defining Procedures and Functions, include a commented Test Harness. Declare used variables for usage in testing. In Procedures include a transaction which is properly rolled back after checking values. Skip this step if the Procedure is a simple INSERT/UPDATE/DELETE with no logic besides that.

--Correct

CREATE FUNCTION dbo.tvfMyFunction

(

@MyParameter int

)

AS

/* TEST HARNESS

DECLARE @MyParameter int

SET @MyParameter = 1

SELECT * FROM dbo.tvfMyFunction(@MyParameter)

*/

(...)

--Correct

CREATE PROCEDURE dbo.uspMyProcedure

(

@MyParameter int

)

AS

/* TEST HARNESS

DECLARE @MyParameter int

SET @MyParameter = 1

BEGIN TRAN

SELECT * FROM dbo.MyTable –-MyTable before operation

EXEC dbo.uspMyProcedure(@MyParameter)

SELECT * FROM dbo.MyTable –-MyTable after operation

ROLLBACK TRAN

*/

(...)

--Avoid

/* TEST HARNESS

SELECT * FROM dbo.tvfMyFunction(1) –-argument not declared

*/

24. If you use designers to generate DML – reformat it using the design styles defined here. In effect it is disallowed

to check in DML from designers into a project repository.

Using designers to generate DDL however is allowed and encouraged.

25. Use comments only to illuminate things that are not obvious from reading the code.

Read More