What is the preferred option, with(nolock) or with(readpast).
Depends. Do you want to read dirty data or skip locked rows entirely. Better option would be to use neither and make sure that your queries are efficient and indexes appropriate. That will reduce the amount of locks taken and needed
What is the preferred option, if exists or if @@rowcount.
Depends what you're doing. However, checking for existence is usually faster than counting all the rows to see if the count is > 1. Especially if there are a lot of rows
What is the preferred option, temporary table or derived table.
Depends. Test both and see which is faster. Sometimes doing a query all in one is fastest. Sometimes putting intermediary pieces into a temp table is fastest
What is the preferred option, static or dynamic query.
Not sure what you mean here
What is the preferred option, top or set rowcount.
Top, as Rowcount is partially deprecated in SQL 2005 and 2008
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass