Performance Tuning can be applied in various areas of sql server like minimizing the number of calls over network, encapsulating the logic in a stored procedure which helps to use the query plan created by the sql engine or writing to multiple data files depending on requirement. This topic mostly covers tuning from the query analyzer perspective i.e. when you are executing the query in the query analyzer how do u look out for options in performance tuning.
Access the Data that is needed:
Do not access all the data available in the table. Instead access the data or columns which are required in the procedure or the logic.
Never deliberately write a code with SELECT * instead always try to use the columns which are necessary. Even if the logic does not need a column like u can write a code if the rows exist for a particular field as shown below
If exists (select * from table where field =
Instead replace select * by select
Always use filter criteria by which you can minimize the rows fetched in a table.