Below points explain some tips for better SQL Server performance.
1. Always have a primary key on every table that you create.
2. Identify those columns which acts as key columns on the table for search. Keep those keys as Non-Clustered indexes.
3. Specify “SET NOCOUNT ON” on each stored procedure that you create, saves time from counting on.
4. Very old and familiar tip, return only those columns that you require it.
5. Reduce the no of round trips you make to the database probably returning multiple datasets.
6. Well-designed indexes can reduce disk I/O operations system resources will be consumed less and also improves performance. A best example of a non-clustered index is imagine a book with clustered index as the index at the start of the book and Non-Clustered index as the index at the end of the book which is sorted by name. Imagine if you don't have index sorted by name at end of book and you want to read about a topic say Polymorphism, you need to completely scan the book to find the topic. This is how exactly the use of non-clustered index in a database which helps to do an optimum search based on a field you generally look for.
7. One of the query tips is to reduce the use of Cursor, one way to achieve is use a CASE statement if it fulfills the requirement.
8. Try to use LEFT JOIN instead of NOT IN for the better performance. But it is not always true that left outer join gives the better results. Always use profile to understand the performance and choose the better option for better results.
9. Reduce Joins as much as possible, every join that table has to make, it has to literally map each and every row in other table which is definitely a huge performance impact.
10. Avoid using IN and NOT IN, instead better use EXISTS and NOT EXISTS
11. Try to reduce sub queries as much as possible.
12. Reduce the usage of triggers instead use CONSTRAINTs or Stored Procedures to perform the job better.
13. Use a table variable instead of temp table if no of rows are less, but if no of rows are more than better use temp table. But the other side temporary table has the ability to have clustered indexes which would definitely boost up the performance , so better test it and use the appropriate one.