SQL Server T SQL Code Standards

 

USE [dbname]

GO

/****** Object:  StoredProcedure [dbo].[SPInsertCollectionCallDetails]    Script Date: 02/23/2016 11:14:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author:        <Hari Ere>

— Create date: <01/17/2014>

— Description:   This procedure input the package process steps

 

— Excute SP

/*USE [dbname]

GO

DECLARE     @return_value int

EXEC  @return_value = dbo.spinsert

@id = 1,

@ProcessStepInfo = NULL,

@Status = NULL

SELECT      ‘Return Value’ = @return_value

GO

*/–Edits History:

— =============================================

BEGIN TRY

— code here

SELECT  ‘hello world’

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT  @ErrorMessage = ERROR_MESSAGE() ,

@ErrorSeverity = ERROR_SEVERITY() ,

@ErrorState = ERROR_STATE();

 

EXEC sp_send_dbmail @profile_name = ‘Mail_ Profile’,

@recipients = ‘myemail@libertyutilities.com’,

@subject = ‘Error from procedreuname ‘, @body = @ErrorMessage;

END CATCH;

GO


If code required in Transaction, please make sure SET XACT_ABORT ON(rolls back the current transaction when a Transact-SQL statement raises a run-time error )

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION;

code

COMMIT TRANSACTION;

GO

 

 

 

TSQL best practices.

 

  • To improve code readability, use proper indention for the statements.
  • To understand code easily, please add comments with each begging of the logic.
  • Use CAPS for all Sql Server Keywords ( Ex: SELECT,ORDER BY).
  • Use SET NOCOUNT ON option at the top of procedure to avoid messages.
  • Do not use SELECT * all columns, instead specific column names in TSQL.
  • Write object names with fully qualified names.(Databasename.schema.objectname).
  • SYNONYMs provide a layer of abstraction over the referenced object with a simplified alias as a same server resident object.
  • Declare variables in required size, which eliminates consuming memory buffers.
  • Never create stored procedure name with SP, SP is reserved for system SQL server.
  • Try to avoid using cursors, use While loops.
  • Create views if the quires are complex.
  • Avoid sub quires and use CTE, CTE’S are reside on memory for faster access.
  • Use ORDER BY,DISTINCT,TOP only when required.
  • Avoid the temp table in the stored procedure. Stored procedures are usually use a cached execution plan to increase the performance. When you use the temp table it will do the compilation every time.
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.(ex: EXECUTE sp_executesql @Query)
  • Keep the transaction as short as possible, with SET XACT_ABORT ON. This will roll back when error raise.
  • USE TRY CATCH BLOCK to trap the errors, and send alerts.
  • Use table variables inside the SP, for small datasets. If the data sets are larger, use temp tables and add index for better performance.
  • Create backup tables into a separate database, it will help DBA’s for cleaning.
  • To avoid deadlocks Always access tables in the same order in all your Stored Procedures and triggers consistently, keep your transactions as short as possible.

Never, ever wait for user input in the middle of a transaction.

  • Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime.
  • Dropping a temp table
  • Ex:

IF OBJECT_ID(‘tempdb..#CpuCounters1’) IS NOT NULL

    DROP TABLE #CpuCounters1

GO

 

 

 

SQL Server Installation Planing,configurations and checks

Base Line of current work loads.

Number of user,logins,connections at a given point.

Estimated growth in work load.

Minimum software and Hardware Requirements.

Storage system sizing and I/O requirements.

Choosing SQL Server editions/License.

Service Accounts Selection.

Password Policy.

Limit using of SA during Upgrades with third party vendors.

Hardening systems with company security policy.

Isolate drives for system databases,Temp db and user data and log files for better performance.

The number of allocated data files in the TempDB database depends on the logical or physical CPUs number on your server.

Configure Min and Max SQL Server Memory, initially sql server acquires minimum memory.  as work load requests from users it will consume memory to max limit.

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

define Databases default paths.

Configure MAX Degree of parallelism according to your work needs, please refer Microsoft Best Practices. (https://support.microsoft.com/en-ca/kb/2806535)

configure Sql Server error logs,

Configure Database Mail with public and private profiles.

Configure SQL Server agent operator for Alerts purpose.

Create default alerts for severities (16-25).

Database maintenance,backup and recovery steps.

Offsite backup plans.

Minimum up time and SLA’s.

Store SA/Service accounts password in vaults/key pass.

Create complete document if High available solution is required (Cluster,Always on groups, Log shipping).

Disaster Recovery Strategy.

Configure Daily health checks Sql server Systems. If required buy third party tools.

Configure Performance Monitoring tools. Store DMV’s in a single location of network share for peridoic helath checks by DBA’s

Create scripts for Daily refresh on DEv and QA environments.

Secure Backups by enforcing Encryption.

Develop plan on periodic backup validationand admin passwords rest

install Service packs and CU’s

for more information please refer to the Microsoft

https://msdn.microsoft.com/en-us/library/bb500442(v=sql.120).aspx