How to Write Efficient Query? – Interview Question of the Week #300

Question: How to Write Efficient Query?

Answer: There is no right or wrong answer to this question. Honestly, there is no definition of an efficient query as well. I often see this question in many interview sessions as well, I often get this question from many blog readers. Everyone wants to write an efficient query.

How to Write Efficient Query? - Interview Question of the Week #300 Efficient-Query-800x229

Let us quickly discuss what one can do so the query gives optimal performance.

  • Do not retrieve additional rows or columns in the resultset
  • Avoid using views with sequence object which blocks expanding views
  • Check query statistics IO and try to keep it low (Watch the video)
  • Check query statistics TIME and try to keep it low (Watch the video)
  • Avoid using triggers on the table as they tend to slow down performance (Watch the video)
  • Follow the best practices for the query parallelism
  • Use ANSI standard T-SQL for better interoperability
  • Avoid using SELECT * in the query (Watch the video)
  • Use EXISTS over IN to get better performance
  • Keep the transactions as short as possible
  • Avoid using cursors
  • Include SET NOCOUNT ON statement

You can also learn more about it in Performance Challenge – Write Efficient Query – SQL in Sixty Seconds #140.

Well, above are a few of the SQL Server Performance Tips which I can think of which I see often implemented at my client’s place. Well, that’s it for today. If you know any SQL Server Performance Tip which you want to add to the list, please leave a comment and I will update this blog post with the due credit to you.

You can also reach out to me on Twitter, LinkedIn, Facebook, and also on YouTube.

On a separate note, today this blog post completes 14 years of daily blogging and this is the 5286th blog post. I am pretty happy that I am able to blog daily for 14 years. However, the time is changing and the direction of the blog is also going to change and I will write more about it tomorrow.

Reference: Pinal Dave (

Query Rewrite, SQL Milestone, SQL Server
Previous Post
How to Add Constraint With No Validation? – Interview Question of the Week #299
Next Post
What is N in nvarchar? – Interview Question of the Week #301

Related Posts

1 Comment. Leave new

  • It took a very long time to come to the conclusion that the indicator itself should be in terms of price / quality ratio.


Leave a Reply