SQL SERVER – Rules for Optimizining Any Query – Best Practices for Query Optimization And Sql Avoid Cursors alternate to While Loop

SQL SERVER – Rules for Optimizining Any Query – Best Practices for Query Optimization

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Non-clustered index should be created on columns of table based on query which is running
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original source table
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure instead
• Check if there is atleast 30% HHD is empty – it improves the performance a bit
• If possible move the logic of UDF to SP as well
• Remove * from SELECT and use columns which are only necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

Sql Avoid Cursors alternate to While Loop

set nocount on;

declare @tbl_record table(record_id int)

declare @record_id int

insert into @tbl_record

select top 3 record_id from tbl_record order by 1

select top 1 @record_id=record_id From @tbl_record

while(@@ROWCOUNT!=0)

begin

select * from @tbl_record

print @record_id

delete from @tbl_record where record_id=@record_id

select top 1 @record_id=record_id From @tbl_record

end