improve query performance

Checklist for fast queries

• Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones
• In the WHERE-clause use the least likely true AND expression first
• Avoid using OR in the WHERE-clause if not all colums have an index
• Avoid using UNION if UNION ALL also does the trick
• Avoid using UNION of two subsets from the same table. Instead use OR in the WHERE-clause
• Avoid using SELECT * FROM when only a few columns are needed. Try to specify each column
• Avoid using COUNT(*) to check the existence of a record. Instead use EXIST
• Always try to use a WHERE-clause in your query to narrow the results
• Try to use the best performing operator as possible
• Avoid using NOT IN. Instead use EXIST, NOT EXIST, IN or LEFT OUTER JOIN with a check for a NULL condition
• Avoid using IN when EXISTS is also possible
• Avoid using IN when BETWEEN is also possible
• In case using IN try to order the list of values so that the most frequently found values are placed first
• Avoid using SUBSTRING in the WHERE-clause. If possible use LIKE instead
• Sometimes consider rewriting a query using a OR to multiple queries combined with a UNION ALL
• Don't use ORDER BY if you don't really need it
• Keep the width and/or number of sorted columns to the minimum
• Keep the number of rows to be sorted to a minimum
• When sorting a specific column often conside making that column a clustered index
• In case of using HAVING try to minimize the amount of rows using a WHERE clause
• In case using LIKE on CHAR of VARCHAR colums quite often consider using the full-text search option
• In case using GROUP BY without an aggregate function try using DISTINCT instead
• Avoid using variables in a WHERE clause in case the query is located in a batch-file

As Kev said, it depends on the query, and also what you mean by "improve performance".

A TOP clause will often reduce query time by limiting the number of rows returned. Of course, reducing the time the query takes to complete will not "improve performance" if it means you get incorrect results (asking for all instances of some condition and then limiting it to the top 2 will give you the wrong answer if that condition happens more than twice). And TOP will not speed things up if the limit set in the top clause is greater than what would have been returned anyway.

A where condition, like a TOP clause, will often reduce query time by reducing the number of rows returned. But a complex where condition can take time to evaluate and so increase the overall query time (especially if the query winds up returning most of the rows anyway.) In terms of performance, that again is meaningless if you are not getting the results that actually answer the question you need to ask.

Now, you can have two different where clauses that return the same results but take dramatically different execution times. This frequently comes up when a join is involved. In that case, it is fully meaningful to talk about improving performance, but that is a very deep topic.

As for using the column names in your query, it can definitely help if by listing the columns (instead of using *) you are reducing the number of columns returned. But if you list out every single column in the table(s), you will not get measurably better performance than using select *.

Listing out the column names can still be useful even if you list every single one though. It helps future readers know what your actual intent was and can help make the code more robust against columns added to the table in the future.

So, in short the answer to all 3 is the infamous "It depends." I have a fairly general article about improving database performance at An Informal Look at Database Performance that might help.