1. Use UNION ALL statement instead of UNION, wherever possible.
UNION ALL statement is quite faster than UNION, because UNION ALL statement does not check duplicate rows, while the UNION statement always check for duplicate rows, whether duplicate record is exists or not.
2. Include SET NOCOUNT ON statement into your stored procedures
Usually T-SQL will show message regarding affected row but with the help of SET NOCOUNT ON statement we can stop these message. This can reduce network traffic, as your client will not receive the message indicating the number of rows affected by a T-SQL statement. (more…)