Some notice to run sql server query, store procedure faster

Here is some notice, we can use to run query sql faster:

• 1. Operate on small result sets – Don’t use “SELECT *”, instead limit the number of columns by including only those columns that are required. Also, try to use a highly restrictive condition in the WHERE clause to only include the required data. In short, retrieve only the rows and columns that are needed.
• 2. Avoid cursors – Avoid using cursors if you can, and try to use a Temp table with identity column to implement looping mechanism. I always create a temp table with an identity column and use a while loop to iterate over data sets.
• 3. Fully qualify Database objects – Always fully qualify database objects with the owner. This will reduce the overhead of name resolution and might also avoid execution plan recompiles.
• 4. Avoid implicit datatype conversions in the query – Implicit conversions can prevent the indexes from being used by the optimizer and will also add overhead by costing extra CPU cycles for datatype conversions.
• 5. Don’t prefix stored procedure name with sp – Many Developers are used to prefixing stored procedure names with sp_. If a stored procedure having an sp_ prefix is executed, SQL Server always looks in the master database first to find the stored procedure. Also, let’s say you have a stored procedure named sp_Test, if Microsoft decides to use this name, all the references to this stored procedure will break, so never begin the name of a SP with sp_.
• 6. Use SET NOCOUNT – SQL Server sends messages(count of the number of rows affected) to the client after each T-SQL statement is executed. If you are using stored procedure, there is no need pass this information and using this option will turn off the messages that are sent back to the client. Though this is not a huge thing, it is definitely something to consider.