SQL SERVER – Three Puzzling Questions – Need Your Answer

Last week I had asked three questions on my blog. I got very good response to the questions. I am planning to write summary post for each of three questions next week. Before I write summary post and give credit to all the valid answers. I was wondering if I can bring to notice of all of you this week.

Why SELECT * throws an error but SELECT COUNT(*) does not

This is indeed very interesting question as not quite many realize that this kind of behavior SQL Server demonstrates out of the box. Once you run both the code and read the explanation it totally makes sense why SQL Server is behaving how it is behaving. Also there is connect item is associated with it. Also read the very first comment by Rob Farley it also shares very interesting detail.

Statistics are not Updated but are Created Once

This puzzle has multiple right answer. I am glad to see many of the correct answer as a comment to this blog post. Statistics are very important and it really helps SQL Server Engine to come up with optimal execution plan. DBA quite often ignore statistics thinking it does not need to be updated, as they are automatically maintained if proper database setting is configured (auto update and auto create). Well, in this question, we have scenario even though auto create and auto update statistics are ON, statistics is not updated. There are multiple solutions but what will be your solution in this case?

When to use Function and When to use Stored Procedure

This question is rather open ended question – there is no right or wrong answer. Everybody developer has always used functions and stored procedures. Here is the chance to justify when to use Stored Procedure and when to use Functions. I want to acknowledge that they can be used interchangeably but there are few reasons when one should not do that. There are few reasons when one is better than other. Let us discuss this here. Your opinion matters.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – Three Puzzling Questions – Need Your Answer

  1. Here’s another one for you Pinal…

    This fails:

    select *
    from Production.ProductCategory c
    cross apply (select *) c2;

    This succeeds:
    select *
    from Production.ProductCategory c
    cross apply (select c.*) c2;

    I’ll try to get a blog post sorted on this kind of stuff some time.



  2. As I understand statistics are updated automatically on an as needed basis, not with any specific frequency or timing. So one has to wait until the optimiser get to that table and finds out it has outdated stats. In most cases this is enough..but perhaps if you have a lot of huge tables it can waste time updating stats before it generate the plan so you can update on your own..or if you need non default sampling you should update on your own.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s