Last week, I posed three questions on my blog and received an overwhelming response. I would like to take this opportunity to thank all the participants for their valuable input. In the coming week, I will be writing a summary post for each of the three questions and giving credit to all the valid answers. However, before I do that, I wanted to bring to your attention one of the questions that generated a lot of interest and discussion.
Why SELECT * Throws an Error but SELECT COUNT(*) Does Not
This question highlights an interesting behavior of SQL Server that many may not be aware of. When running a SELECT * query, it may throw an error, whereas a SELECT COUNT(*) query does not. This behavior can be puzzling at first, but once you run both queries and read the explanation, it becomes clear why SQL Server behaves the way it does. I would also like to draw your attention to a related Connect item and the insightful comment by Rob Farley.
Statistics are Not Updated but are Created Once
Another question that received multiple correct answers is related to statistics in SQL Server. Statistics play a crucial role in helping the SQL Server Engine come up with an optimal execution plan. However, in this particular scenario, even though the auto create and auto update statistics settings are enabled, the statistics are not being updated. There are multiple solutions to this problem, and I am glad to see many of you sharing your solutions in the comments section of the blog post.
When to Use Functions and When to Use Stored Procedures
The third question is more open-ended, as there is no right or wrong answer. As developers, we have always used both functions and stored procedures. However, this is an opportunity to discuss and justify when it is appropriate to use a stored procedure over a function, and vice versa. While they can be used interchangeably, there are certain scenarios where one is more suitable than the other. I invite you all to share your opinions and experiences on this topic.
Thank you once again for your active participation. I look forward to summarizing the answers to these questions in the upcoming blog posts. Stay tuned!