• Use TRY-Catch for error handling
  • Try to avoid using SQL Server cursors whenever possible
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)
  • Include SET NOCOUNT ON statement
              CREATE PROC dbo.GetUSersInfo
                AS
                  SET NOCOUNT ON;
                  --Procedure code here
                  SELECT UserName FROM dbo.sec_users
                  -- Reset SET NOCOUNT to OFF
                   SET NOCOUNT OFF;
               END
               GO
  • Do not create your procedure name prefix with sp_ always use different prefix i.e. usp_YourStoreProcEDUREName.
  • Try to avoid SCALAR FUNCTIONS IN SELECT STATEMENTS as much as possible.
  • Implement Pagination
  • Create Non-clustered indexes, wherever it is necessary for tables and temp tables.
  • Avoid sub Queries in select statements and where clause
  • Distinct will give best results compare to Group by

By admin

Leave a Reply

Your email address will not be published. Required fields are marked *