Coding standard for SQL

1.      Formatting Standards

a.       Table Rules :

                                                              i.      Pascal notation; end with an ‘s’ Like Products, Customers

                                                            ii.      Group related table names

b.      Stored Procedure  Rules:

                                                              i.      Name : USP_<App Name>_ [<Group Name >_] <Action><table/logical instance>

                                                            ii.      Example : USP_Orders_GetNewOrders,  usp_Products_UpdateProduct

c.       Triggers Rule :

                                                              i.      Name : TR_<Table Name>_<action>

                                                            ii.       Examples: TR_Orders_UpdateProducts

d.      Indexes Rules :

                                                              i.      IX_<TableName>_<Columns >

                                                            ii.      IX_Products_ProductID

e.       Primary Keys :

                                                              i.      Key Name : PK_<TableName>

                                                            ii.      Example : PK_products

f.        Foreign Key :

                                                              i.       FK_<PrimaryKeyTable>_<ForeignKeyTable>

                                                            ii.      Example : FK_Products_Orders

g.       Default vales :

                                                              i.      DF_<TableName>_<ColumnName>

                                                            ii.      Example : DF_Products_Quantity

2.      General Rules :

a.       Use upper case for all SQL keywords

                                                              i.      SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.

b.      Do not use spaces in the name of database objects

                                                              i.      not use SQL keywords as the name of database objects

1.      In cases where this is necessary surround the object name with bracket, such as [Year]

c.       Do not prefix stored procedures with sp_

                                                              i.      Because it shows System stored procedures can conflict with other procedures 

d.      Indent code to improve readability

e.       Comment code blocks that are not easily understandable

                                                              i.      Use single-line comment markers(–)

                                                            ii.      Reserve multi-line comments (/*.. ..*/) for blocking out sections of code

f.       Use parentheses to increase readability           

                                                              i.      Example : WHERE (color=’red’ AND(size = 1 OR size = 2))

g.       Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.

h.      Use one blank line to separate code sections

i.        Use spaces so that expressions read like sentences.

                                                              i.      fillfactor = 25, not fillfactor=25

j.        Format JOIN operations using indents

k.      Format JOIN operations using indents

l.        Use ANSI Joins instead of old style joins

3.      Structure :

a.       Each table must have a primary key

                                                              i.      In most cases it should be an IDENTITY column named ID

b.      Normalize data to third normal form

                                                              i.      Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.

c.       Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead

d.      In VARCHAR data columns

                                                              i.      do not default to NULL

                                                            ii.      use an empty string instead

e.       Columns with default values should not allow NULLs

f.       As much as possible, create stored procedures on the same database as the main tables they will be accessing

4.      Coding guidelines :

a.       Optimize queries using the tools

b.      Do not use SELECT * (Use name of columns)

c.       Place SET statements before any executing code in the procedure

d.      Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server

e.       Avoid unnecessary use of temporary tables

                                                              i.      Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better⁶

f.       Avoid using <> as a comparison operator

                                                              i.      Use ID IN(1,3,4,5) instead of ID <> 2

g.       Use SET NOCOUNT ON at the beginning of stored procedures

h.      Fully qualify tables and column names in JOINs

i.        Fully qualify all stored procedure and table references in stored procedures.

j.        Do not define default values for parameters.

k.      If a default is needed, the front end will supply the value.

l.        Do not use the RECOMPILE option for stored procedures.

m.    Place all DECLARE statements before any other code in the procedure.

n.      Do not use column numbers in the ORDER BY clause.

o.      Do not use GOTO.

p.      use TRY/CATCH

q.      Do basic validations in the front-end itself during data entry

r.        Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server

s.       Always use a column list in your INSERT statements.

                                                              i.      This helps avoid problems when the table structure changes (like adding or dropping a column).

t.        Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.

                                                              i.      Any expression that deals with NULL results in a NULL output.

                                                            ii.      The ISNULL and COALESCE functions are helpful in dealing with NULL values.

u.      Avoid the use of cross joins, if possible.

v.      When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.

w.     Avoid using TEXT or NTEXT datatypes for storing large textual data.⁹

                                                              i.      Use the maximum allowed characters of VARCHAR instead

x.      Avoid dynamic SQL statements as much as possible.

y.      Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.

z.       Do not use white space in identifiers.

5.      Show some smartness when writing a code :

a.       Let’s understand it by some examples

b.    Consider the following query to find the second highest offer price from the Items table

                                       i.   SELECT MAX(Price)  FROM Products                                                                         WHERE ID 

                                     IN 

                                    ( SELECT TOP 2 ID FROM Products ORDER BY Price DESC )

c.       Same code can be written as

                                           i.      SELECT MAX(Price)                                                                                       FROM                                                                                                              (SELECT TOP 2 Price FROM Products ORDER BY Price DESC)

d.      Use code Like this

                                                              i.      IF EXISTS( SELECT 1 FROM Products WHERE ID = 50)

                                                            ii.      Instead of :  IF EXISTS(SELECT COUNT(ID) FROM Products WHERE ID = 50)

6.      Security of Database Server :

a.        Disable unnecessary features and services.

b.      Install only required components.

c.       Enforce strong password policy (There should a strong password for every database user)

d.      Disable the SA or (any default) account or rename it.

                                                              i.      Do not use this account for SQL server management

e.       Remove BUILDIN\Administrators group from the SQL Server Logins

f.       Enable logging SQL Server login attempts (failed & successful).

g.      Defining Database Ownership to Increase Security

                                                              i.      Database Server instance can contain many databases which were created by users who are database owners - DBO (by default).

                                                            ii.      Thus several best practices should be implemented regarding these special ownerships

1.      Minimize the number accounts/users that have the db_owner role for each database.

2.      Have distinct owners for databases; not all databases should be owned by SA or by any other user in sysadmin server role.

h.      Hardening the network connectivity

                                                              i.      A default installation of SQL Server use TCP port 1433 for client requests and communications. These ports are well known and are common target for hackers. Therefore it is recommended to change default ports associated with the SQL Server installation. It is same for My SQL.

i.        SSL Configuration in Database Server

                                                              i.      Database Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. Enabling SSL encryption increases the security of data transmitted across networks between instances of Database Server and applications

j.        Auditing Mechanism in Database Server (Specially for SQL server)

                                                              i.      Database Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security:

1.      None—Disables auditing (no events are logged)

2.      Successful Logins Only—Audits all successful login attempts

3.      Failed Logins Only—Audits all failed login attempts

4.      Both Failed and Successful Logins—Audits all login attempts