Stored procedures in SQL
-
What are the best practices for writing stored procedures in SQL which improves the performance of the server?:)
Try to restrict the queries result set by using the WHERE clause. Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns. Use views and stored procedures instead of heavy-duty queries. Try to avoid using SQL Server cursors, whenever possible. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement. Try to use constraints instead of triggers, whenever possible. Use table variables instead of temporary tables. Try to avoid the HAVING clause, whenever possible. Try to avoid using the DISTINCT clause, whenever possible. Include SET NOCOUNT ON statement into your stored procedures to stop the message Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows. Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows. UNION ALL statement instead of UNION, whenever possible. Ricardo Casquete
-
What are the best practices for writing stored procedures in SQL which improves the performance of the server?:)
Hi Deeksha, Guess the following links will help you. http://vyaskn.tripod.com/coding\_conventions.htm http://www.extremeexperts.com/SQL/Articles/BestPractices.aspx Thanks and Regards Pani