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

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s