How do you optimize stored procedures in SQL Server 2005

Answer:

1. Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization
2. Select only those fields which really require.
3. Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).