Coding Rules Standards
Coding Rules and Standards
- Do not use SELECT *
- Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
- Avoid unnecessary use of temporary tables
- Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better
- Avoid using as a comparison operator
- Use ID IN (1,3,4,5) instead of ID 2
- Use SET NOCOUNT ON at the beginning of stored procedures
- Do not use cursors or application loops to do insert
- Instead, use INSERT INTO
- Fully qualify tables and column names in JOINs
- Fully qualify all stored procedure and table references in stored procedures.
- Do not define default values for parameters.
- If a default is needed, the front end will supply the value.
- Do not use the RECOMPILE option for stored procedures.
- Place all DECLARE statements before any other code in the procedure.
- Do not use column numbers in the ORDER BY clause.
- Do not use GOTO.
- Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs OR use TRY/CATCH
- Do basic validations in the front-end itself during data entry
- 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
- Always use a column list in your INSERT statements.
- 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.
- Any expression that deals with NULL results in a NULL output.
- The ISNULL and COALESCE functions are helpful in dealing with NULL values.
- Do not use the identitycol or rowguidcol.
- Avoid the use of cross joins, if possible.
- When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
- Avoid using TEXT or NTEXT data types for storing large textual data
- Use the maximum allowed characters of VARCHAR instead
- Avoid dynamic SQL statements as much as possible
- Access tables in the same order in your stored procedures and triggers consistently
- Do not call functions repeatedly within your stored procedures, triggers, functions and batches
- Default constraints must be defined at the column level.
- 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.
- Define all constraints, other than defaults, at the table level.
- When a result set is not needed, use syntax that does not return a result set
- Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
- Constraints that apply to more than one column must be defined at the table level.
- Use the CHAR data type for a column only when the column is non-nullable
- Do not use white space in identifiers.
- The RETURN statement is meant for returning the execution status only, but not data.
- Use upper case for all SQL keywords
a. SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
- Indent code to improve readability
- 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
- Use single quote characters to delimit strings
a. Nest single quotes to express a single quote or apostrophe within a string
b. For example, SET @Example = ‘SQL”s Authority’
- Use parentheses to increase readability
a. WHERE (color=’red’ AND (size = 1 OR size = 2))
- · Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
- Use one blank line to separate code sections
- Use spaces so that expressions read like sentences.
a. Fill factor = 25, not fillfactor=25
- Format JOIN operations using indents
a. Also, use ANSI Joins instead of old style joins
- Place SET statements before any executing code in the procedure
General Tsql Rules was last modified: October 1st, 2017 by