Application Development

SQL Code Guidelines

  • Declare SET ANSI_NULLS ON for all scripts. This provides consistent NULL behavior while avoiding vendor-specific NULL behavior.
  • Declare SET QUOTED_IDENTIFIER ON, which allows identifiers to be escaped based on ANSI rules. Furthermore, this is the default for newer SQL Server client tools.
  • Use nchar/nvarchar unless there's a reason not to. This aids in internationalization.
  • Use nvarchar(128) for all SQL identifiers. This corresponds to the sysname datatype.
  • Use nvarchar(255) for all file system paths. Actual path length limitations on Windows are extremely convoluted. This number provides a good minimum value.
  • Do not use the tinyint data type for a column with the IDENTITY property. Use smallint instead. This is because a tinyint + IDENTITY does not appear in VS with the AutoIncrement property set to true. Avoid using tinyint, which provides little benefit.
  • Assume that the environment and identifiers such as table names are case-sensitive.
  • Ensure that parameter and variable names within a function or procedure are consistently specified with regard to case. In a case-sensitive environment @User is not the same as @user.
  • Explicitly reference the dbo schema when creating objects. Otherwise, objects may end up being owned by the current user's user account.
  • Add audit columns to every table (e.g., AddedBy, AddedOn, ChangedBy, ChangedOn). Refer to Add Reserved Colums to Tables for more information.
  • Consider adding record-locking columns (e.g., LockedBy, LockedOn). Note, however, that record locking impacts performance.
  • Avoid blob fields (text/ntext/image) for performance. Instead use varchar(max) for text, nvarchar(max) for ntext and varbinary(MAX) for images.
  • Use sp_executesql instead of EXEC as the former allows for query plan caching.
  • Always explicitly name the constraint when creating constraints, rather than allowing SQL Server to auto-generate a name (which can make constraints harder to work with later). Use the naming convention Type_Table_Column where Type would be PK, FK, CK, DF or UQ and represents the constraint type.
  • Do not allow NULLs in columns with the bit datatype. Use an appropriate (named) DEFAULT constraint. Exceptions to this should be rare and justifiable.
  • Do not permit NULLs in columns with any other numeric data types (including integer, decimal, float, real). Assign the columns a suitable DEFAULT value where possible. This is particularly important for "parameter" values where allowing NULLs forces the developer to hard code a value into the application.
  • Define columns of the same name that appear in multiple tables (e.g., boaStatus, Acknowledged etc.) consistently and assign them the same attributes wherever they appear in the schema.
  • Do not create a unique constraint on the column(s) that make up the table’s Primary Key.
  • Do not create a unique constraint without verifying that there is not already a unique constraint on that column.
  • Do not create an index without verifying that there is not already an index on that column.
  • Avoid use of the CASE statement in all views.
  • Avoid table aliases in views, except in the case of self-joins where aliases are required to avoid "Ambiguous Column Name" compilation errors.

Formatting

  • Use tabs instead of spaces.
  • Place each stored procedure parameter and user-defined function argument on its own line and indent one tab stop.
  • Do not format views that may be edited by the Query Builder. It's time wasted as the Query Builder strips the formatting along with any comments.
  • Do not use any excess syntax such as PRINT or GO statements in delta scripts. Appropriate comments are fine.
  • Use upper-case for SQL keywords:

SELECT MyColumn

FROM MyTable

  • Break multiple clauses within a single statement onto their own line to keep lines short. See previous example.
  • Separate multiple statements with spaces.

SELECT @MyColumn = MyVariable

FROM MyTable

 

SELECT @MyOtherColumn = MyOtherVariable

FROM MyOtherTable

Naming Conventions

The guidelines below are a brief summary of the Syniti naming conventions for SQL objects.

  • Do not pluralize table names.
  • Do not abbreviate. Customer is Customer, not Cust or Cstmr. Exceptions include extremely common, pronounceable abbreviations such as Min and Max and well known acronyms such as HTML.
  • Prefix any view or stored procedure registered in Stewardship Tier with web. Limited exceptions to this may be permitted, for example, using a cts prefix to keep CTS-related objects readily identifiable. Obtain approval from Product Management before adding a new prefix.
  • Prefix the name of any objects (stored procedures, functions, views) meant to be consumed by client WebApps with boa. These constitute Stewardship Tier's supported, public API.
  • Suffix the name of Horizontal views swith Hor; Vertical views with Ver; lists with List, etc.
  • Include the base table name in any view. For instance, if creating a view that queries the Customer table and serves as the Horizontal view, name it webCustomerHor. If creating a stored procedure which affects the status of an order, name it webOrderChangeStatusUpd.
  • Use underscores to improve readability: webOrderChangeStatusUpd can be named webOrder_ChangeStatus_Upd.
  • Suffix stored procedures that insert records with Ins and procedures that update records with Upd. If the stored procedure both inserts and updates records, it may be too complex and should be broken into smaller procedures.
  • Break down complex views with more than one or two joins into multiple views. Name the views from 1 to N where 1 is the lowest level view. For instance, webCustomerHor could be broken down into webCustomer1Hor, webCustomer2Hor and webCustomer3Hor. Register the view webCustomer3Hor in Stewardship Tier.
  • Avoid object aliases in trivial cases. Though they can improve code readability, they do not render well in the Query Builder. A notable exception includes the generation of dynamic SQL. In such cases, aliases can improve readability dramatically and the Query Builder cannot be used to edit the SQL.
  • Use the following naming convention when creating SQL Server indexes:
    • For an index with a single key column: IX_Table_KeyCol1
    • For an index with multiple key columns: IX_Table_KeyCol1_KeyCol2_KeyColn
    • For an index with included columns: IX_Table_KeyCol1_INCL_Col1_Coln
  • Some examples (all from the JobStatus table):
    • An index on the JobboaStatus column only: IX_JobStatus_JobboaStatus
    • An index on the JobboaStatus and the RetentionDays columns: IX_JobStatus_JobboaStatus_RetentionDays
    • An index on the JobboaStatus column with an included column of RetentionDays: IX_JobStatus_JobboaStatus_INCL_RetentionDays
  • Specifically avoid using IX_Table_1, 2, 3 etc. when creating indexes.
  • Name constraints as follows: Type_Table_Column where Type is PK, FK, CK, DF or UQ and represents the constraint type. An example would be DF_Page_Description as a default constraint on the Page table, Description column.
  • Do not include the word "Name" in column names that reference SQL objects, columns, parameters, etc. It's Page.Table, not Page.TableName; Page.HorizontalView, not Page.HorizontalViewName; etc. Besides being shorter, the former makes for a cleaner UI. Note that columns like DataSource.DataSourceName and WebApp.WebAppName must have the word "Name" in the column name. In these cases, the user is supplying a name, not selecting an object.