General Tsql Rules

Coding Rules Standards

Coding Rules and Standards

  1. Do not use SELECT *
  2. Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
  3. Avoid unnecessary use of temporary tables
  4. Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better
  5. Avoid using as a comparison operator
  6. Use ID IN (1,3,4,5) instead of ID 2
  7. Use SET NOCOUNT ON at the beginning of stored procedures
  8. Do not use cursors or application loops to do insert
  9. Instead, use INSERT INTO
  10. Fully qualify tables and column names in JOINs
  11. Fully qualify all stored procedure and table references in stored procedures.
  12. Do not define default values for parameters.
  13. If a default is needed, the front end will supply the value.
  14. Do not use the RECOMPILE option for stored procedures.
  15. Place all DECLARE statements before any other code in the procedure.
  16. Do not use column numbers in the ORDER BY clause.
  17. Do not use GOTO.
  18. 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
  19. Do basic validations in the front-end itself during data entry
  20. 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
  21. Always use a column list in your INSERT statements.
  22. 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.
  23. Any expression that deals with NULL results in a NULL output.
  24. The ISNULL and COALESCE functions are helpful in dealing with NULL values.
  25. Do not use the identitycol or rowguidcol.
  26. Avoid the use of cross joins, if possible.
  27. When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
  28. Avoid using TEXT or NTEXT data types for storing large textual data
  29. Use the maximum allowed characters of VARCHAR instead
  30. Avoid dynamic SQL statements as much as possible
  31. Access tables in the same order in your stored procedures and triggers consistently
  32. Do not call functions repeatedly within your stored procedures, triggers, functions and batches
  33. Default constraints must be defined at the column level.
  34. 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.
  35. Define all constraints, other than defaults, at the table level.
  36. When a result set is not needed, use syntax that does not return a result set
  37. 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.
  38. Constraints that apply to more than one column must be defined at the table level.
  39. Use the CHAR data type for a column only when the column is non-nullable
  40. Do not use white space in identifiers.
  41. The RETURN statement is meant for returning the execution status only, but not data.

Formatting

  1. Use upper case for all SQL keywords
    a. SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
  2. Indent code to improve readability
  3. 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
  4. 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’
  5. Use parentheses to increase readability
    a. WHERE (color=’red’ AND (size = 1 OR size = 2))
  6. · Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
  7. Use one blank line to separate code sections
  8. Use spaces so that expressions read like sentences.
    a. Fill factor = 25, not fillfactor=25
  9. Format JOIN operations using indents
    a. Also, use ANSI Joins instead of old style joins
  10. Place SET statements before any executing code in the procedure
General Tsql Rules was last modified: October 1st, 2017 by Justin Antony

Leave a Reply

Your email address will not be published.