Microsoft SQL Server database standard guidelines

SQL DB Standard/ Script Review

1) All script must include header when updating/new
-- =============================================
-- Author:                     [ Name]
-- Create date: [2019/05/13]
-- Description:   [Description]
-- Used by:     [Function]
-- =============================================
2) Naming  convention ( no short form unless too long)   , Stored procedure naming based on table_action.
3) Must include checking if EXISTS for table,stored procedure and data patching key (Enable this when in management studio (Options > SQL Server Object Explorer > Scripting )

Set to true for the below :
a. Check for object existence
b. Schema qualify object name
c. Script extended properties

Tables
1) Table column , if the column type is datetime use datetime instead of just date
2) Place column in appropriate order for easy to see (adjust the column order)
3) Have CreatedDateTime column for archiving if there is no other datetime column , and also CreatedBy, ModifiedDateTime and ModifiedBy
4) Encrypt or hash sensitive data like password/credit card when insert into column.
5) Minimize leaving the column with null value unless its datetime , update to blank or any default value. Null cause inconsistent output when selecting.
6) If tables already too many column may consider normalising to another table or predicted the table to have many rows then open another table
7) When creating new table create a primary key
8) Put extended properties description for hardcoded value or put description in another table and specify foreign key linking table in description.

Stored Procedure
1) When writing select statement include With (NOLOCK) hints to reduce locking unless need urgently check concurrent data with updates.
2) Try to have only 1 stored procedure to update, delete, select data of one table if the where clause parameter is similar. Reuse same stored procedure better for performance.
3)  Write system logic in application and keep stored procedure minimal
4) Try not to use temp #table, use variable table or if must put in a table
5) Do not use cursor to loop. Try use cursor less method or do item by sets.
6) Do not need to include begin transaction and commit / rollback transaction
7) Use full schema name when referring to object e.g. (tables, stored procedure)
8) When creating parameter variable make sure its the same as what declared in tables to prevent conversion in the where/On join clause as it will effect performance.
9) Recommended to minimize sorting in select statement
10) If selecting/updating from the same table and the where clause is similar between different stored procedure try copy and paste so that it is exactly the same for server to cache performance
11) Include SET NO COUNT ON in spp

Data Patching
1) Have USE DATABASE statement to correctly update the selected database.
2) Include “SET IDENTITY INSERT [Table] ON” when insert to identity column
3) Have expected effected rows count as comment when submit patching script to make sure the actual number of rows patched is correct.
4) Not recommended delete statement for production in patching script.