Stored Procedure: It is considered as one of the most optimized database object in SQL Server database. If we implement few best practices, it can give more efficient results.
Best Practices:
1. Use "SET NOCOUNT ON" as the first line of the stored procedure body.
2. Do not use SP_ or sys_ prefixes for naming user stored procedures.
3. Always include schema names prefix for stored procedure and other database objects used in stored procedure. It reduces processing time.
4. Avoid 'SELECT *' and specify the columns in SELECT statement.
5. Use explicit transactions by using BEGIN/END TRANSACTION.
6. Use TRY/CATCH for Error Handling.
7. Defined NULL or NOT NULL for Temporary Table Columns.
8. Last but not least implement best practices for the queries used in Stored Procedure.
Best Practices:
1. Use "SET NOCOUNT ON" as the first line of the stored procedure body.
2. Do not use SP_ or sys_ prefixes for naming user stored procedures.
3. Always include schema names prefix for stored procedure and other database objects used in stored procedure. It reduces processing time.
4. Avoid 'SELECT *' and specify the columns in SELECT statement.
5. Use explicit transactions by using BEGIN/END TRANSACTION.
6. Use TRY/CATCH for Error Handling.
7. Defined NULL or NOT NULL for Temporary Table Columns.
8. Last but not least implement best practices for the queries used in Stored Procedure.
No comments:
Post a Comment