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 (https://blog.sqlauthority.com)
4 Comments. Leave new
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.
Rob
Truely,Very nice information !!
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.
Hi Pinal, Where is the link to the summary post for these questions?