Home > Application Development > Application Standards > SQL Server Standards

SQL Server Standards

Naming Conventions

No.	Standard	Source
1.	General naming standards	1
2.	Database names match standards	1
3.	Table names match standards	1
4.	Field names match standards	1
5.	Stored Procedure names match standards	1
6.	User defined function names match standards	1
7.	View names match standards	1
8.	Trigger names match standards	1
9.	Index names match standards	1
10.	Primary and Foreign Key names match standards	1
11.	User defined data type names match standards	1
12.	User defined roles names match standards	1
13.	Constraint naming standards - In General	1

Coding Conventions

No.	Standard	Source
14.	SQL statements are accurately and consistently structured	2
15.	T-SQL reserved words are UPPER cased	2
16.	Column lists are used in all SQL statements	1
17.	All `DECLARE` and other DDL statements are defined at the start of the file	2
18.	Variables are used as Constants	3
19.	Only ANSI joins are used in SQL statements	2
20.	All code files contain a standard header.	1
21.	All scripts MUST have the 'USE' keyword.	1
22.	Execute sp_recompile after altering stored procedures, triggers, tables, or views.	2
23.	Execute sp_refreshView immediately after altering views.	2
24.	Work with NULL values appropriately.	2
25.	When creating constraints, create one constraint at the time.	2
26.	Use ALTER instead of CREATE where possible.	2
27.	Use SP_EXECUTESQL() instead of EXECUTE() where possible.	3
28.	All release MUST have a rollout and a rollback package.	1
29.	Ensure that scripts are run in correct order.	2
30.	Do use transactions in the code appropriately.	2
31.	DO beware of deadlocks.	2
32.	DON'T open large Recordsets.	1
33.	DON'T use 'INSERT' to import bulk data.	1
34.	Do not include SQL 'Data Manipulation Language' in application Code.	1
35.	Make sure the Database connection string is protected.	1
36.	Do not access the Database from the Application with the "sa" Account.	1
37.	Write comments in stored procedures, triggers and SQL batches generously.	2
38.	Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword.	3
39.	Avoid searching using not equals operators (<> and NOT).	2
40.	Use 'Derived tables' wherever possible.	3
41.	Use VIEWs wherever possible.	3
42.	Avoid dynamic SQL statements as much as possible.	3
43.	Do use string manipulations, concatenations, row numbering, case conversions, type Conversions, basic validations etc., in the front-end applications.	2
44.	Do not call functions repeatedly within your stored procedures, triggers, functions and batches.	2
45.	Always be consistent with the usage of character 'case' in your code.	1
46.	ALWAYS use SCOPE_IDENTITY rather than @@IDENTITY	1
47.	Use SET NOCOUNT ON before any data manipulation commands In Stored procedures	1
48.	Fully understand the functionality and overhead in each SQL command	2

Design

No.	Standard	Source
49.	Database is designed to 3rd normal form	1
50.	Tables contain a primary key	1
51.	`CHAR` and `TEXT` data types are not used.	2
52.	Foreign keys and default constraints are defined	1
53.	BIT data type columns are clustered together in a table definition	3
54.	All data access is via stored procedures	1
55.	Clustered indexes are applied to the most relevant columns	3
56.	Computed columns are not used	2
57.	Generic tables are not used to store disparate data.	1
58.	Indexes are applied to the most relevant columns.	2
59.	Limit the use triggers only for auditing, custom tasks and validations that cannot be performed using constraints.	2
60.	Perform all your referential integrity checks and data validations using constraints.	1
61.	Add a fixed set of columns for tables that require audit.	2
62.	Avoid NULL columns where possible.	3
63.	Avoid using in appropriate column types.	2

Error Handling

No.	Standard	Source
64.	A Consistent Error Handling and Logging policy is used.	1
65.	Pre 2005 databases use `@@Error`	3
66.	2005 databases use `Try`, `Catch` constructs	3

Performance and Scalability

No.	Standard	Source
67.	`NO COUNT` is set to `ON`.	2
68.	The lowest safe isolation level is used.	2
69.	`NOLOCK Hint` is used wherever possible.	3
70.	Cursors are avoided.	2
71.	Temporary table variables are used rather than Temporary tables.	2
72.	High volume queries use inline T-SQL rather than user defined functions.	3

Deployment Best Practices

No.	Standard	Source
73.	Every script MUST have the 'USE' keyword and the database name on the first line to specify the database that the script is to be run on.	1
74.	Every release MUST have a rollout and a rollback package.	1
75.	Write rollout verification - to be returned to dev as a verification from QA/production migration process.	2
76.	Ensure that scripts are run in correct order.	1
77.	Creating new objects.	1
78.	Altering existing objects.	1
79.	Make sure to pass all needed documentation and information.	1

Miscellaneous

No.	Standard	Source
80.	Prepare information about database and application.	2
81.	Make sure all appropriate stakeholders are involved and informed	1
82.	Make sure that DB dose not have any dependencies between linked servers	1
83.	Do not use 'genuser' login.	1
84.	All database objects should be owned by 'dbo'	1
85.	Always use large databases, and 'stress' test the application.	1
86.	Do not use 'dbo_owner' access.	1