Monday, July 9, 2018

SQL coding Standards

http://dhs.pa.gov/cs/groups/webcontent/documents/document/p_032218.pdf

MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 1 of 37 COMMONWEALTH OF PENNSYLVANIA DEPARTMENT’S OF HUMAN SERVICES, INSURANCE, AND AGING INFORMATION TECHNOLOGY STANDARD Name Of Standard: MS SQL Server Number: 2012 / 2014 Naming and Coding Standard STD-DMS009 Domain: Category: Data Date Issued: Issued By Direction Of: Date Revised: 9/15/2016 Clifton Van Scyoc, Dir of Division of Technical Engineering Abstract: The purpose of this document is to detail and give examples of Microsoft Structured Query Language (MS SQL) development standards followed at the Departments of Human Services (DHS), Insurance (PID) and Aging (PDA). General: Coding standards are conventions and methods developers follow when developing, editing or maintaining program code. Better programming style, developer understanding, readability and reduced application development time are the results of following coding standards. Database Modeling Environment Computer Associates (CA) ERwin Data Modeler is the standard tool for designing and modeling SQL Server 2008/2012/2014 databases. All models are reviewed by the Database Management Section. All completed models are stored in the Microsoft Visual Studio Team Foundation Server. Database Data Definition Environment Microsoft SQL Server Management Studio is the standard tool to use for the execution and modification of Data Definition Language (DDL) for the development of SQL Server 2008/2012/2014 databases. The general process is: MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 2 of 37 1. Initially generate a DDL from ERwin using forward engineering, or if required, create DDL statements manually within SQL Server Management Studio. 2. Execute the DDL within SQL Server Management Studio and, if necessary, edit and/or tune the statements using the Management Studio environment. 3. Reverse engineer the database back into ERwin to keep the database and data model synchronized. Store revised data models and all DDL scripts in Microsoft Visual Studio Team Foundation Server. Store the DDL scripts in a consistent manner under the project folder in Team Foundation Server. Database Programming Environment Use SQL Server 2008/2012/2014 Management Studio to code and test stored procedures and triggers. Prototype every process using Transact-SQL (T-SQL) within SQL Server Management Studio prior to creating a new stored procedure, view, function or trigger. This results in ease of debugging. Use Execution Plans Estimated Execution plans need to be used while developing T-SQL. Actual Execution plans need to be included in the migration packet. Use Database Engine Tuning Advisor SQL Server DBA’s and analysts use this tool to determine if additional keys or indexes may be required. Creation of the additional objects are determined and executed by database administrators and analysts. General Import/Export File Rules All files imported to and exported from SQL Server 2008/2012/2014 preferred delimited. Although commas, hyphens, semicolons and other characters can be used to delimit files, only a vertical bar (| or ‘pipe’) is allowed, no exceptions. Using other characters introduces the possibility of that character being embedded as valid data within a text or large variable character column. A vertical bar is less likely to be valid data. Commenting Guidelines These rules apply in general to all source files. Object Header Usage Include object headers in every source code file. Object Maintenance Documentation MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 3 of 37 Include object maintenance documentation in every source code file. Keep this documentation current on all revisions after moving any given source file into production. Before initial migration into production, revision information is not required. Comment Quality Comments must contain a description of the process the SQL module (source file), procedure, or trigger accomplishes. Write the description in clear, concise, non-colloquial English, using business terms to describe the processes. Do not use comments such as: -- -- Declare the Customer Name Variable -- DECLARE @CustomerName AS VARCHAR(32) Describe the purpose of the process and how it works, in non-technical terms. This is important because most developers can read the SQL and see line-by-line what it does mechanically, but may not be able to easily grasp the purpose (business logic) behind the code. Write comments to aid the reader of the code. Assume that the reader is not well versed in Transact-SQL and try always to describe the “how” and “why” of the process clearly. Comment Quantity More comments are better than using fewer comments. Remember that the code is reviewed first, then, over time (perhaps after many years), require revisions and enhancements. Providing adequate comments allows the database administrator, developer, and the developer’s peers to quickly understand the logic and perform necessary revisions, and so forth. Standard Object Header Every object contains an object header. An object header contains the database name, object name, creation date, author, a brief description of the object, parameters passed, returns, calling mechanism, tables and aliases used in code, other procedures called, DPSR #, notes, and any special comments and warnings. Module headers remain open on the right-hand side. Documentation for Object Maintenance This section describes how to document changes in the Transact-SQL source code. The method involves using a maintenance documentation heading and comments within the source code. A common developer signature provides these together. Object Maintenance Documentation Immediately following the object header there is a skeletal maintenance block. Use this skeletal block as a template for maintenance done to the object code. Copy, paste, and fill in this block when you alter the object code. The block copy marks are on the lines with the equal signs. Include the lines with the block copy marks. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 4 of 37 Make the insertion of the copied block directly after the skeleton block. Thus, the history is recorded from earliest to latest. Example of OBJECT HEADER AND MAINTENANCE DOCUMENTATION /****************************************************************************** * DATABASE: OMAPINTERNET * PROCEDURE NAME: usp_SelectAllResults * DATE: 04/03/2009 * AUTHOR: Homer Simpson * PROCEDURE DESC: This proc returns all the results for a specific NDC, * ShortName or market basket code or their combination * WorkOrder# 13798 ************************************************************************** * DATE: Developer Change ---------- ---------------- ------------------------------------------ 08/01/2013 Joe Developer Add Indicator **************************************************************************/ Minor Line-level Commenting Do not put comments on the same line as the code. Examples of this illegal style follow: DECLARE @IsNew AS Integer -– Is New Flag DECLARE @Firstname AS VARCHAR(32) -- First Name Field Insert all comments for a particular section of code before the code block, with one blank comment line before and after the comment. An example follows: -- -- Declare the local working variables -- DECLARE @IsNew AS Integer DECLARE @Firstname AS VARCHAR(32) -- -- Return identity value. -- SET @IDENTITY_CONTACT = CAST(SCOPE_IDENTITY() AS INT) SELECT @IDENTITY_CONTACT Formatting Guidelines Font Style, Size, and Capitalization MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 5 of 37 Font Style and Size Write all SQL code in 10 point Courier New font. Capitalization Please make sure that Transact-SQL keywords are opposite case from variables, field names, and object names when coding SQL statements,. Tab Stops Set tabs to either four spaces or eight spaces (default). Line Length Do not write a Transact-SQL line that exceeds 132 characters in length, unless there is no other way to properly format the source. Line Count—needs moved to store proc section. Do not write procedures that exceed four pages in length excluding comments, headers, and maintenance log, without written justification to the data base administrators. Exceptions to rule are procedures used in conversion or loads of data. Code exceeding this limit should be looked at closer and possibly split into smaller stored procedures or modularized. Star “*” Usage Do not use the star symbol “*” in a Transact-SQL statement, as this is very inefficient. This is prohibited .In all instances, include the appropriate fields in the statements where needed. An example of the wrong way to write a Transact-SQL statement follows: SELECT * FROM T_VENDOR If you must return a count, select one field in the table or use 1. Example below. Select count (1) from T_VENDOR Examples of the right way to write a Transact-SQL statement follows. Specify each field as in the examples: SELECT Field1, Field2, Field3, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 6 of 37 Field4 FROM TABLE_A WHERE nbr_vt = @nbr_vt_recent SELECT PMT.IDN_APPLN_PMT, PMT.AMT_BNFT FROM T_APPLN_PMT PMT INNER JOIN T_VOU_TRNSMTL VT ON VT.IDN_TRNSMTL_VOU = PMT.IDN_TRNSMTL_VOU Compound Statements Compound statements are those that are encased within a BEGIN…END structure. Formatting rules for this structure are as follows: 1. The BEGIN statement aligns directly under the statement above. 2. The END statement is aligned in the same position as its corresponding BEGIN statement. 3. Within the BEGIN…END structure, statements are indented and should be in alignment. IF…ELSE Statement Blocking Treat all “IF” statements (and block “ifs”) as if they were compound statements using the BEGIN…END structure. No exceptions are permitted. An example follows: IF @@ROWCOUNT < 1 BEGIN RETURN -1 END ELSE BEGIN SELECT @RowReturned = @@ROWCOUNT RETURN 0 END Multi-line Parameter Style When you must expand a statement to more than one line (because it exceeds the 80- character line limit standard), use the style in the following example, for consistent readability. It is best to apply this style even when you do not exceed the 80-character limit (though this rule is not strictly enforced). EXECUTE usp_MyStoredProcedure MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 7 of 37 @MyParameter1, @MyParameter2, @MyParameter3 OUTPUT, @MyParameter4 OUTPUT Indenting and Formatting SQL Statements The styling presented in this standard provides for a more consistent look to all stored procedures and triggers. The guidelines presented here (by example) apply to all TransactSQL statements. Use of white space is highly recommended. Example of Select Statement SELECT DISTINCT t1.nbr_rcpt, t2.nam_rcpt FROM T_TABLE1 t1, T_TABLE2 t2 WHERE t1.nbr_value = t2.nbr_value AND t1.cde_value = @ParmKeyVal AND (t1.nbr_value < 1 OR t1.nbr_value > 99) ORDER BY t2.nam_rcpt DESC From the example, it can be seen that: 1. Individual elements are placed in separate lines 2. Elements are indented one tab stop 3. Keywords are to the left whenever possible 4. If the length of the keyword or keywords (ex: “SELECT DISTINCT” and “ORDER BY”) equals or exceeds the tab stop setting (8), the parameter is placed on the subsequent line at the proper indent position 5. Parentheses are used to distinguish logical blocks and are indented at the same level as any other parameter. Other examples follow: Example of Select Statement: SELECT DISTINCT t1.nbr_rcpt, t2.nam_rcpt FROM T_TABLE1 t1,T_TABLE2 t2 MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 8 of 37 WHERE t1.cde_value = t2.cde_value AND t1.nbr_rcpt = @ParmKeyVal AND (t1.cde_value < 1 OR t1.cde_value > 99) ORDER BY t2.nam_rcpt DESC Examples of Insert Statements INSERT INTO T_VENDOR ( nam_vendor, cde_active, dte_crtd, idn_user_crtd ) VALUES ( @Name, @Active, GETDATE(), @ModifiedUser ) Example of Update Statement UPDATE T_VENDOR SET nam_vendor = @Name, cde_active = @Active, dte_crtd = GETDATE(), idn_user_crtd = @ModifiedUser WHERE nbr_vendor = @VendorID Example of Delete Statement DELETE FROM T_VENDOR WHERE nbr_vendor = @VendorID Example of Case Statement MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 9 of 37 CASE @myfield WHEN 'A' THEN 'ABC ' WHEN 'B' THEN 'BCD ' WHEN 'C' THEN 'CDE ' ELSE 'JKL' END Naming Conventions For table and column names please reference the Data Administration Standards on the Data Domain page under Business and Technical Standards on the BIS web site. Table Naming All table names follow the current BIS naming conventions and are prefaced with a T_. Example: T_FIPS_CODE Column Naming All column names follow the current DHS naming conventions. View Naming All view names consist of the prefix VW_ followed by business function they perform or business rule they enforce. Example: VW_SEL_T_FIPS_CODE Variable Formats All variable names are defined in a consistent manner across all the programs. To ensure consistency parameters are descriptive and define correctly according to their usage. @p_IDN_RECON integer, @p_IDN_ALJ integer, @p_HONORABLE varchar(20)= NULL, Parameters can be set to NULL. Instead of using an IF block to check for NULL parameters, NULLS are checked like the example below. Second example is to check a range. (B.CDE_PARTY_RQNG = @p_CDE_PARTY_RQNG OR @p_CDE_PARTY_RQNG IS NULL) MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 10 of 37 ((DTE_FAA_BHA >= @p_DTE_FAA_BHA_FROM AND DTE_FAA_BHA <= @p_DTE_FAA_BHA_TO) OR (@p_DTE_FAA_BHA_FROM Is NUll AND @p_DTE_FAA_BHA_TO Is NUll)) If you have to use a dynamic order by (sort results) pass in the field to sort and the sort order. @p_field_order_sort varchar(50)=null, @p_order_sort varchar(4)=null IF @p_order_sort = 'ASC' BEGIN SELECT m.ProviderName as NAM_PROVR, m.NAM_BUSNS_MP, m.NAM_PROVR_ALT, FROM TBLMEDICHECK m WHERE m.Providername = @p_nam_provr ORDER BY CASE @p_field_order_sort WHEN 'NAM_PROVR' THEN m.ProviderName WHEN 'NAM_BUSNS_MP' THEN m.NAM_BUSNS_MP WHEN 'NAM_PROVR_ALT' THEN NAM_PROVR_ALT END ASC; END ELSE BEGIN SELECT m.ProviderName as NAM_PROVR, m.NAM_BUSNS_MP, m.NAM_PROVR_ALT, FROM TBLMEDICHECK m WHERE m.Providername = @p_nam_provr ORDER BY CASE @p_field_order_sort WHEN 'NAM_PROVR' THEN m.ProviderName WHEN 'NAM_BUSNS_MP' THEN m.NAM_BUSNS_MP WHEN 'NAM_PROVR_ALT' THEN NAM_PROVR_ALT END DESC; END Stored Procedures MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 11 of 37 When coding enterprise applications, the use of stored procedures to separate application logic from database code has been found beneficial. Stored procedures are used for any data access (SELECT) or manipulation (INSERT, DELETE, UPDATE). The advantages include:  Ease of Maintenance – When supporting database structure or application logic changes, the DML is easily accessible directly from the database, can be easily scanned using SQL, and eliminates the need to interrogate each COM or .NET object when searching for a particular database reference. As such, it is recommended that all web-based applications use stored procedures for the majority, if not all, database access.  Enhanced Performance – Stored procedures run directly on the database server, which is optimally tuned to perform such operations.  Reusability – Stored procedures can be called from multiple applications, thereby reducing the time required to design, code and test commonly used application functions. Stored procedures are named according to the business function they perform or business rule they enforce. The name should include a prefix of USP and a business description of the action performed. The name is appropriately abbreviated with items found in the standard abbreviation list. An example of a procedure name is USP_ADD_NEW_T_INDIV. As the name implies, this procedure adds a new row to the T_INDIV table. All stored procedures, begin with one of the following: USP_SELECT USP_UPDATE USP_INSERT USP_DELETE Stored Procedures performs only one function. This means one insert, one update or one delete statement per stored procedure. An “if” clause determining whether an insert or update should be performed, is acceptable. See the DBA team if there are any questions. Multiple update\delete\insert statements can cause locking\blocking, orphan records and unhandled transactions. Do not write store procedures that exceed four pages in length excluding comments, headers, and maintenance log, without written justification to the data base administrators. Exceptions to the rule are procedures used in conversion or loads of data. Analyze code exceeding this limit and split into smaller stored procedures or modularize. For a SELECT, items in yellow are standard. The comment block is simple and contains a brief audit listing of changes that go to production; not a running summary of changes during development. Error Trapping is highly recommended with the Enterprise libraries, because if MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 12 of 37 you have an error, the first thing we ask is what the error log says. Please also list the fields. No SELECT * is allowed. Also, white space for readability is also highly desirable. Example USE [RECON] GO /****** Object: StoredProcedure [RECONSchema].[USP_SELECT_BACK_END_RECON] Script Date: 09/10/2015 09:23:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /************************************************************************** -- Author: George Washington -- Create date: 07/04/2012 -- Description: get all fields for the Back End Screen from the RECON table ***************************************************************************** DATE: Developer Change ---------- ---------------- ------------------------------------------ 01/05/2012 George Washington New 03/25/2013 Tom Jefferson Added DTE_CLER_RTND **************************************************************************/ ALTER PROCEDURE [RECONSchema].[USP_SELECT_BACK_END_RECON] ( @IDN_RECONS Integer ) AS BEGIN SET NOCOUNT ON; BEGIN TRY SELECT [IDN_RECONS] ,[TXT_CASE_NUM] ,[TXT_APPEAL_NUM] ,[TXT_BHA_FILE_NUM] ,[DTE_RCVD_BHA] ,[DTE_MLNG_ORDER_FINAL_SCTRY] ,[DTE_CREATN] ,[NAM_BY_CRTD] ,[DTE_MODFD] ,[NAM_BY_MODFD] FROM [RECON].[dbo].[T_RECON] where IDN_RECONS = @IDN_RECONS END TRY BEGIN CATCH SELECT MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 13 of 37 ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH END For an UPDATE, items in yellow are standard. The comment block is simple and contains a brief audit listing of changes that go to production; not a running summary of changes during development. Error Trapping is highly recommended with the Enterprise libraries, because if you have an error, the first question is what the error log says. Please also list the fields. No SELECT * is allowed. Also, white space for readability is also highly desirable. Example USE [StateFacilityTrackingSystem] GO /****** Object: StoredProcedure [PTS].[USP_UPDATE_BSU] Script Date: 09/10/2015 09:52:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /************************************************************************** Database: StateFacilityTrackingSystem Procedure Name: USP_UPDATE_BSU Date: 10/21/2014 Author: Harry Potter Procedure Desc: This procedure creates new bsu for existing admission. Parameters: see below ************************************************************************** DATE: Developer Change ---------- ---------------- ------------------------------------------ 02/02/2014 PETER PAN New **************************************************************************/ ALTER PROCEDURE [PTS].[USP_UPDATE_BSU] @IDN_PTN_ADMSN AS INT=0, @IDN_BSU AS INT, @PNT_BSU_CODE AS VARCHAR(7), @PNT_BSU_NUMBER AS VARCHAR(7), @PNT_BSU_EFF_DTE AS DATETIME, @CWOPA_ID AS VARCHAR(12)=NULL AS SET NOCOUNT ON; BEGIN TRY MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 14 of 37 UPDATE PTS.T_PNT_BSU SET CDE_BSU=@PNT_BSU_CODE, DTE_EFFV_BSU=@PNT_BSU_EFF_DTE, NBR_CASE_BSU=@PNT_BSU_NUMBER, DTE_UPDTD_RECORD=GETDATE(), IDN_USER_UPDTD_RECORD=@CWOPA_ID WHERE IDN_BSU_PNT=@IDN_BSU END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH An INSERT/UPDATE is also acceptable as long as you are doing either an INSERT or an UPDATE – not both! This is acceptable: IF @P_IDN_USER = ‘cwopa\user’ BEGIN UPDATE PIOS.dbo.T_CW_ASGMT SET IDN_CW = @P_IDN_CW, DTE_ASGMT_CASE = @P_DTE_ASGMT_CASE, IDN_USER_CHANGE_LAST = @P_IDN_USER, DTE_RECORD_CHANGE_LAST = GetDate() WHERE IDN_ASGMT_CW = @P_IDN_ASGMT_CW END ELSE BEGIN INSERT INTO PIOS.dbo.T_CW_ASGMT (IDN_CW ,IDN_STATUS ,DTE_CHANGE_STATUS) VALUES (@P_IDN_CW ,@P_IDN_STATUS ,NULL) END MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 15 of 37 THIS IS NOT ACCEPTABLE: An Insert or UPDATE stored procedure performs one UPDATE or INSERT, and rarely more than that. Check parameters in your .NET code and then an INSERT or UPDATE is called. A Transaction can be set in the .NET code to either commit all INSERTS or none of them if the code has multiple INSERTS/UPDATES. In this case a collection in .NET can be used and the same INSERT could be called as many times as needed as you loop through the collection. USE [RECON] GO /****** Object: StoredProcedure [PTS].[USP_INSERT_DIAGNOSTICS] Script Date: 09/10/2015 10:18:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /********************************************************************************** ** Database : RECON Procedure Name : USP_INSERT_DIAGNOSTICS Date : 10/14/2014 Author : Tom Sawyer Procedure Desc : This procedure inserts records into Diagnostics table. Parameters: see below *********************************************************************************** * DATE: Developer Change ---------- ---------------- ------------------------------------------ 10/14/2014 Will Robinson New *********************************************************************************** */ ALTER PROCEDURE [PTS].[USP_INSERT_DIAGNOSTICS] @idn_pnt AS INT, @idn_admsn_pnt AS INT, @ambulationCode AS VARCHAR(1), @psych_Code_1 AS VARCHAR(7) , @psych_Code_Date_1 AS DATETIME, @psych_Code_NBR_Order_1 AS VARCHAR(1), @psych_Code_2 AS VARCHAR(7), @psych_Code_Date_2 AS DATETIME, @psych_Code_NBR_Order_2 AS VARCHAR(1), @psych_Code_3 AS VARCHAR(7), @psych_Code_Date_3 AS DATETIME, @psych_Code_NBR_Order_3 AS VARCHAR(1), @psych_Code_4 AS VARCHAR(7), @psych_Code_Date_4 AS DATETIME, @psych_Code_NBR_Order_4 AS VARCHAR(1), @psych_Code_5 AS VARCHAR(7), MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 16 of 37 @psych_Code_Date_5 AS DATETIME, @psych_Code_NBR_Order_5 AS VARCHAR(1), @psych_Code_6 AS VARCHAR(7), @psych_Code_Date_6 AS DATETIME, @psych_Code_NBR_Order_6 AS VARCHAR(1), @psych_Code_7 AS VARCHAR(7), @psych_Code_Date_7 AS DATETIME, @psych_Code_NBR_Order_7 AS VARCHAR(1), @substance_Code_1 AS VARCHAR(7), @substance_Code_Date_1 AS DATETIME, @substance_Code_NBR_Order_1 AS VARCHAR(1), @substance_Code_2 AS VARCHAR(7), @substance_Code_Date_2 AS DATETIME, @substance_Code_NBR_Order_2 AS VARCHAR(1), @substance_Code_3 AS VARCHAR(7), @substance_Code_Date_3 AS DATETIME, @CWOPA_ID AS VARCHAR(12) AS SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; INSERT INTO PTS.T_PNT_AMBTN ( CDE_AMBTN, IDN_ADMSN_PNT, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD ) VALUES ( @ambulationCode, @idn_admsn_pnt, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID ) if @psych_Code_1 IS NOT NULL and LTRIM(RTRIM(@psych_Code_1)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 17 of 37 IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_1, 0, @psych_Code_NBR_Order_1, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_1, 1 ) end if @psych_Code_2 IS NOT NULL and LTRIM(RTRIM(@psych_Code_2)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_2, 0, @psych_Code_NBR_Order_2, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_2, 1 ) end if @psych_Code_3 IS NOT NULL and LTRIM(RTRIM(@psych_Code_3)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 18 of 37 DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_3, 0, @psych_Code_NBR_Order_3, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_3, 1 ) end if @psych_Code_4 IS NOT NULL and LTRIM(RTRIM(@psych_Code_4)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_4, 0, @psych_Code_NBR_Order_4, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_4, 1 ) end if @psych_Code_5 IS NOT NULL and LTRIM(RTRIM(@psych_Code_5)) <> '' begin MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 19 of 37 INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_5, 0, @psych_Code_NBR_Order_5, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_5, 1 ) end if @psych_Code_6 IS NOT NULL and LTRIM(RTRIM(@psych_Code_6)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_6, 0, @psych_Code_NBR_Order_6, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_6, 1 ) MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 20 of 37 end if @psych_Code_7 IS NOT NULL and LTRIM(RTRIM(@psych_Code_7)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @psych_Code_Date_7, 0, @psych_Code_NBR_Order_7, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @psych_Code_7, 1 ) end if @substance_Code_1 IS NOT NULL and LTRIM(RTRIM(@substance_Code_1)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @substance_Code_Date_1, 0, @substance_Code_NBR_Order_1, GETDATE(), @CWOPA_ID, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 21 of 37 GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @substance_Code_1, 2 ) end if @substance_Code_2 IS NOT NULL and LTRIM(RTRIM(@substance_Code_2)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @substance_Code_Date_2, 0, @substance_Code_NBR_Order_2, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @substance_Code_2, 2 ) end if @substance_Code_3 IS NOT NULL and LTRIM(RTRIM(@substance_Code_3)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 22 of 37 VALUES ( @substance_Code_Date_3, 0, @substance_Code_NBR_Order_3, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @substance_Code_3, 2 ) end if @substance_Code_4 IS NOT NULL and LTRIM(RTRIM(@substance_Code_4)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @substance_Code_Date_4, 0, @substance_Code_NBR_Order_4, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @substance_Code_4, 2 ) end if @medical_Code_1 IS NOT NULL and LTRIM(RTRIM(@medical_Code_1)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 23 of 37 DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_1, 0, @medical_Code_NBR_Order_1, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_1, 3 ) end if @medical_Code_2 IS NOT NULL and LTRIM(RTRIM(@medical_Code_2)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_2, 0, @medical_Code_NBR_Order_2, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_2, 3 ) end if @medical_Code_3 IS NOT NULL and LTRIM(RTRIM(@medical_Code_3)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 24 of 37 DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_3, 0, @medical_Code_NBR_Order_3, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_3, 3 ) end if @medical_Code_4 IS NOT NULL and LTRIM(RTRIM(@medical_Code_4)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_4, 0, @medical_Code_NBR_Order_4, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_4, 3 ) end MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 25 of 37 if @medical_Code_5 IS NOT NULL and LTRIM(RTRIM(@medical_Code_5)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_5, 0, @medical_Code_NBR_Order_5, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_5, 3 ) end if @medical_Code_6 IS NOT NULL and LTRIM(RTRIM(@medical_Code_6)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_6, 0, @medical_Code_NBR_Order_6, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 26 of 37 @medical_Code_6, 3 ) end if @medical_Code_7 IS NOT NULL and LTRIM(RTRIM(@medical_Code_7)) <> '' begin INSERT INTO PTS.T_PNT_DGNSTC ( DTE_EFFV_DGNSTC, IND_RECORD_ARCD, NBR_ORDER_DGNSTC, DTE_CREATN_RECORD, IDN_USER_CREATN_RECORD, DTE_UPDTD_RECORD, IDN_USER_UPDTD_RECORD, IDN_ADMSN_PNT, IDN_DX, IDN_TYPE_DGNSTC ) VALUES ( @medical_Code_Date_7, 0, @medical_Code_NBR_Order_7, GETDATE(), @CWOPA_ID, GETDATE(), @CWOPA_ID, @idn_admsn_pnt, @medical_Code_7, 3 ) end COMMIT; END TRY BEGIN CATCH ROLLBACK; SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH Triggers Triggers are to be avoided as they have the potential to cause execute multiple SQL statements without other developer knowledge. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 27 of 37 Constraints Planning and creating tables requires identifying how to enforce integrity of the data stored within the columns of the tables. MS SQL Server has the following constraints to enforce integrity: Primary Key Constraints are columns or a column that uniquely identifies a row within a table. All tables should have a primary key and composite primary keys should be avoided. Primary keys should be named PK_tablename_columnname. For most of your OLTP tables, an identity column is the primary key. Example: PK_T_ADDRESS_IDN_ADDR. Foreign Key Constraints are columns or a column that is used to enforce a relation between information in two tables. A link is defined between the two tables when a primary key is referenced by a column or columns in another table. The reference becomes a foreign key in the second table. Foreign keys are named as FK_foreignkeytable_primarykeytable_columnname. Example: FK_T_NAME_T_ADDRESS_IDN_ADDR. Indexes An index is an on-disk structure associated with a table or a view that speeds retrieval of rows from the table or the view. An index contains keys built from one or more columns in the table or the view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. A table or view can contain the following types of indexes: • Clustered o Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order. o The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. • Nonclustered o Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 28 of 37 o The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. Indexes should be named IDX_ tablename_columnname(s). Example: IDX_T_ADDR_STATE_STATE Default Definitions Default Definitions are predefined values created on columns within a table. The default value is used when inserting a new row in a table and a particular column in the insert statement does not have a value. For example you may have a default of all 99999999 that you may want to set up as a default end date. The default has a prefix of D_ followed by a description of what the default does. Example: DF_OPEN_DATE User Defined Functions User defined functions can be created in SQL Server Management Studio to perform actions such as complicated calculations or character string manipulation. User defined functions are created and used for actions repeated within an application. An example is adding dashes to social security number. This user defined function could be created and used any time a correctly formatted social security number is required. User defined functions are named UDF_function. Example: UDF_FORMATSSN Transaction and Error Handling Try…Catch Try…Catch, is the preferred error handling method in SQL Server. This method is used in place of the old RAISEERROR. Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling feature of the Microsoft .NET and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 29 of 37 After the CATCH block handles the exception, control is then transferred to the first TransactSQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger. Transact-SQL statements in the TRY block following the statement that generates an error is not executed. If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger. A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement. One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements. A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement. In Transact-SQL, each TRY block is associated with only one CATCH block. Working with TRY…CATCH When using the TRY…CATCH construct, consider the following guidelines and suggestions: Each TRY…CATCH construct must be inside a single batch, stored procedure, or trigger. For example, you cannot place a TRY block in one batch and the associated CATCH block in another batch. The following script would generate an error: BEGIN TRY SELECT IDN_AGENCY, NAM_AGENCY FROM dbo.T_AGENCY ORDER BY NAM_AGENCY END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH Transact-SQL Restrictions Following is SQL Server functionality that is not used when developing procedures or triggers. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 30 of 37 Cursors Cursors are not allowed. Looping within Stored Procedures. Looping within stored procedures is PROHIBITED. Looping is only performed in developer code and not in SQL stored procedures. An infinite loop can bring down SQL Server. THIS IS NOT ACCEPTABLE! WHILE len(@IDN_USER) > 0 BEGIN SET @ID_USER =LEFT(@IDN_USER, charindex(',', @IDN_USER+',')-1) INSERT INTO dbo.T_COURSE_INSTRR (IDN_OFFNG_COURSE, IDN_USER) VALUES (@IDN_COURSE_OFFRNG, @IDN_USER) END Use of “*=” and “=*” and SQL JOINS- stopping point Do not use of the “*=” and ”=*” join operators in the WHERE clause. Use instead the JOIN keyword in the FROM clause. Microsoft does not recommend the use of “*=” and ”=*” and may not provide support for these in future releases of MS SQL Server. Using this syntax for joins is discouraged (by Microsoft) because of the potential for ambiguous interpretation and because it is nonstandard. Be sure to use the join syntax. Instead of using where a = b and b = c, for example, use inner join on a = b, etc. Use left and right outer joins as well as UNION ALLS sparingly. Pick the join order carefully. The majority of outer joins can successfully be rewritten as inner joins with tremendous performance improvements. Use of SELECT INTO Do not use the SELECT INTO clause to create a table on the fly. Instead, create your table before the SELECT statement and use the INSERT statement followed by the appropriate SELECT. Usage of Column Prefixes It is generally recommended to use prefixes for columns appearing in the SELECT list. This is a coding best practice that leads to more maintainable applications. As an example, it is preferred to have: SELECT a.au_id, a.au_lname FROM dbo.authors a Over this: SELECT au_id, au_lname FROM dbo.authors Database SQL Options MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 31 of 37 Database SQL Options should be configured as recommended to remove deprecated behaviors, be ANSI compliant, and be able to leverage the full feature set (indexed views and indexes on computed columns). The following SQL options should be checked to see if they are configured properly as per Microsoft SQL Options control ANSI compliance options. The following database SQL Options should be ON: • ANSI_NULLS • ANSI_PADDING • ANSI_WARNINGS • ARITHABORT • CONCAT_NULL_YIELDS_NULL • QUOTED_IDENTIFIERS The following should be OFF: • NUMERIC_ROUNDABOUT Use of Defaults and Rules Database Administrators check stored procedures, functions, views and triggers for existence of defaults and rules. These objects have been deprecated in favor of CHECK constraints and are not supported in future releases of SQL Server. Null Comparisons Database Administrators check stored procedures, views, functions and triggers to flag the use of equality and inequality comparisons involving a NULL constant. These comparisons are undefined when ANSI_NULLS option is set to ON. It is recommended to set ANSI_NULLS to ON and use the IS keyword to compare against NULL constants. In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. String = Expression Aliasing Database Administrators check stored procedures, functions, views and triggers for use of column aliases where the name of the expression uses a string value. It is recommended to use quoted identifiers instead. String aliases are not supported in future releases of SQL Server. As an example, the following syntax is not recommended: SELECT 'alias_for_col'=au_id+au_id FROM dbo.authors Recommended alternatives are: MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 32 of 37 SELECT au_id+au_id as "alias_for_col" FROM dbo.authors SELECT au_id+au_id as alias_for_col FROM dbo.authors SELECT au_id+au_id as [alias_for_col] FROM dbo.authors Primary Keys Database Administrators check all user databases to ensure that all tables have defined either a primary key or have a column with a Unique Constraint defined. Tables without a Primary Key defined or a column with a unique constraint defined will not be approved. Foreign Keys Database Administrators check all user databases to ensure that proper foreign key/primary key relationships are created when needed. Foreign keys are also defined as indexes. Order by Clause with Ordinals Database Administrators check stored procedures, functions, views and triggers for use of ORDER BY clause specifying ordinal column numbers as sort columns. As an example, the following syntax is not allowed: SELECT au_id FROM dbo.authors ORDER BY 2, 1 The use of ordinal column numbers is not be allowed. Schema Usage Use Schema Usage only as needed; otherwise, the default schema ‘dbo’, is used. Database Administrators check stored procedures, functions, views and triggers for use of schema qualified names when referencing tables and views. Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the form: [ server_name.[database_name].[schema_name]. | database_name.[schema_name]. | schema_name. ] ] When referencing a specific object, it is not necessary to specify the server, database, and owner (schema) for SQL Server to identify the object. However, it is recommended that at least the schema name be specified to identify a table or view inside a stored procedure, function, view or trigger. When SQL Server looks up a table/view without a schema qualification, it first looks in the default schema and then looks in the 'dbo' schema. The default schema corresponds to the current user for adhoc batches, and corresponds to the schema of a stored procedure MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 33 of 37 when inside one. In either case, SQL Server incurs an additional runtime cost to verify schema binding of unqualified objects. Applications are more maintainable and may experience a slight performance improvement if object references are schema qualified. Top without Order By Database Administrators check stored procedures, functions, views and triggers for usages of TOP in queries without an ORDER BY clause. It is recommended to specify sort criteria when using TOP clause. Otherwise, the results produced are plan dependent and may lead to undesired behavior. Usually TOP without ORDER BY is meaningless. Avoid Stored Procedure Recompiles See next section. Production Adhoc Querying Real time adhoc reporting against the production database is not permitted. Microsoft Access is not allowed to connect to SQL. To do reporting as this causes table locks, performance degradation and creates a security risk. Hard coded SQL Hard coded SQL in web pages or applications is not permitted. Put T-SQL in stored procedures for security reasons, efficiency and ease in debugging. Dynamic SQL is not allowed. Performance Recommendations General Considerations Connections to the SQL Server database are to be brief. A connection is made, a process completed and the connection dropped. No persistent connections to SQL Server are allowed. Stored procedures perform one process and not a series of processes. It is better to create a stored procedure that performs an insert rather than a stored procedure that performs an insert, update and a delete. Executing multiple stored procedures is preferred over executing one, unwieldy, multi-task stored procedure. Stored Procedure Recompiles No stored procedures are to interleave Data Definition Language (DDL) and Data Manipulation Language (DML) operations. In other words, do all CREATE/DROP statements separate from SELECT and UPDATE statements. If data operations are mixed, MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 34 of 37 the SQL server must recompile procedures each time to determine the best plan to use for each new or dropped object. Therefore, coding all creates and drops together reduces the amount of stored procedure compiles. Avoid creating a temporary table in a control-of-flow statement such as an IF…ELSE or WHILE statement. Avoid using a DROP statement for temporary tables in stored procedures, because tables are automatically dropped when the procedure is completed. Specifying the owner of objects such as dbo.Tablename and dbo.Stored procedure name cuts down on recompiles. Temporary Tables Often procedure throughput can be dramatically improved by the use of temporary tables. Restructuring a Transact-SQL statement that involves lengthy and/or complicated joins to perform the same functionally by multiple actions against a temp table will improve performance. Please note: use a temporary table to manipulate small amounts of data, and if data exceeds 500 records add an index. Also if you have > 6 data changes in a temporary table consider using the option (keep plan) to avoid unnecessary recompiles. Table Variable Type The table variable type functions exactly like the temporary table, with the exception that all data is loaded directly in random access memory. Temporary Tables vs. Table Variable Database Administrators check stored procedures and triggers for usages of temporary tables that may be replaced by use of table variables. When a procedure creates a temporary table and has no CREATE INDEX issued on it, and it is dropped all in the same procedure, consider using table variables instead to potentially observe fewer recompilations. Note that if large data volumes are inserted in the temporary table it may still be preferred to use temporary tables over table variables due to parallel execution restrictions and statistics maintenance. Indexes Examine the performance of the statements within the procedure and the entire application to determine if an additional index will justify its overhead. Consider overall index usage all along the development path. Indexes, or lack of indexes, are the direct cause of poor performance 90% of the time. As per Microsoft’s suggestion, all tables that have more than 500 records are to have a clustered index, even if a field is created just to hold the index. Please note when setting an index, pay close attention to the fill factor for the given index. The fill factor is the amount space consumed by the index and more importantly the amount of space that will be kept free for expansion. For an example a 90% fill factor is 90% full with 10% left for expansion. For tables that do a high volume of inserts, updates, deletes consider a lower fill factor, or perhaps not having a clustered index at all. A clustered index slows down inserts but speed up selects. The developer determines what the best tradeoff is. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 35 of 37 Ultimately, it is the responsibility of the developer coding the stored procedures and the SQL code to determine what the best indexes are and how they are used. Execution Plan When debugging and tuning a stored procedure, examine the execution plan for clues as to the performance of the procedure and how it may be improved. The reason for execution plan is to determine whether an index is required. In the example below, execution identifies a missing index that could improve performance. From this, one asks if another index should be created to avoid the scan. If yes, create an index and rerun. SQL Profiler/Database Engine Tuning Advisor Two valuable and necessary tools when tracking and or monitoring performance are SQL Profiler and Database Engine Tuning Advisor. Only the DBA has permissions to run these two tools. The DBA can analyze the output from these tools and give suggestions on what tables or objects have problems and can benefit from index creation. Programmatic Recommendations This section provides recommendations to developers for improving the reliability of procedures and triggers. Use SET NOCOUNT When developing procedures that return a result set and use intermediate processing, be sure to set the SET NOCOUNT ON option to prevent the generation of unwanted result set count information. Use the SET NOCOUNT OFF statement to restore result counting before executing a SELECT statement to return results. Stylistic Recommendations MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 36 of 37 This section provides recommendations to developers for improving the readability of procedures and triggers. Nesting IF statements Avoid, wherever possible, nesting IF statements. Use compound Boolean expressions instead. No more than 4 nested IF statements are allowed. SQL Server 2014 Integration Services SQL Server Data Tools for Visual Studio 2013 is required when performing extract, transform and load (ETL) processing. Data Tools replaces SQL Server Business Intelligence Development Studio and brings enhancements and improvements to creating and executing packages. Naming Packages The name of the SQL Server Integration Services package indicates the database the package belongs to as well as the purpose of the SSIS package. In a development environment, the developer testing the package may own the package but in all other environments SA (System Administrator) owns the package. The package may very well have the same name as the job. The difference between a job and a package is that a job is a scheduled package. A package on its own must be started manually. The package names can be mixed case for ease of readability. Example: CUSTOMERVendorExport SQL Server 2014 Reporting Services SQL Server 2014 Reporting Services (SSRS) is required when creating any reports for use at the DHS. Naming Reports The name of the SQL Server 2014Reporting Services object should indicate the database the report belongs to as well as the purpose of the SSRS package. The report names can be mixed case for ease of readability. Example: CUSTOMERNameAndAddresses Job Naming Standards A job consists of a series of SQL Statements executed as a transaction (as one complete unit). The name of a SQL SERVER job indicates the database to which the job belongs as well as the purpose of the job. The job name is the same name as the SSIS package. MS SQL Server 2012 2014 Naming and Coding Standard.docx Page 37 of 37 SA owns the job in all environments. All jobs need DBA approval prior to being scheduled to run. The job names are mixed case for ease of readability. Example: ACCOUNTINGImportDailyFiles Exemptions from this Standard: There will be no exemptions to this standard. Refresh Schedule: All standards and referenced documentation identified in this standard will be subject to review and possible revision annually or upon request by the DHS Information Technology Standards Team. References: Microsoft SQL Server 2008/2012/2014 Books On Line Standard Revision Log: Change Date Version Change Description Author and Organization 1.0 New document Kiley Milakovic 12/11/2007 1.1 SQL Server 2005 revisions Matt Leitzel 01/13/2011 1.2 SQL Server 2008 revisions Matt Leitzel 09/15/2016 1.3 SQL Server 2012/2014 revisions Steve Isleib and Michael Kraus

http://dhs.pa.gov/cs/groups/webcontent/documents/document/p_032218.pdf

No comments:

Post a Comment