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.
Do not use the tinyint data type for a column with the IDENTITY property. Use int instead. 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 schema such as dbo on Microsoft SQL Server when creating objects. Otherwise, objects may end up being owned by the current user's user account.
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., AddedBy, 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.
Familiarize yourself with query performance tools, like "Show Actual Execution Plan". These tools can help troubleshoot poor performance in scalar functions, aggregation keywords(DISTINCT, SUM, MAX, MIN), CASE statements and table joins.
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 Syniti Construct with web.
Include the base table name in any view. For instance, if creating a view that queries the Customer table, name it webCustomer. 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.
Besides underscores, avoid using non-alphanumeric characters in SQL object names. Avoid using only numeric characters, e.g. 1234, for field names.
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, webCustomer could be broken down into webCustomer1, webCustomer2 and webCustomer3. Register the view webCustomer3 as Datasets.
Avoid object aliases in trivial cases. Though they can improve code readability, they do not render well in the Query Builder.
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.
If you name a column in a table after a SQL reserved word (for example, “Table”) you are required to mention the field name in the parenthesis. For example, “Table(Name)”.