August 20, 2012 at 6:04 am
Rich Weissler (8/20/2012)
Now, had there been an option of "Table A will return 10 rows and Table B will return 7 rows" ... that could have been interesting.
Probably what the intended 10 for both was!
Knew there was an issue with this, but couldn't remember which way the problem worked. Nice reminder, thanks.
August 20, 2012 at 6:39 am
Rich Weissler (8/20/2012)
Now, had there been an option of "Table A will return 10 rows and Table B will return 7 rows" ... that could have been interesting.
I'll agree with that one! The answers were almost too easy, but that might have been just what I needed on a Monday morning. Thanks for the great question.
August 20, 2012 at 6:49 am
Thank you for a nice and straight forward question for a Monday morning.
🙂
August 20, 2012 at 6:51 am
Toreador (8/20/2012)
Currently 15% of people think that Count(columnname) on a 7-row table will return a value of 10 😉
When I read that answer, for a moment I thought it could be 10 + 3 rows for table A and 7 + 3 rows for table B. Then I took a second look just to be sure how many rows where there in each table.
August 20, 2012 at 7:23 am
bitbucket-25253 (8/19/2012)
QODs using images instead of easily cut and paste text have now been excluded from being published, I would propose to the powers that be that those submitted QODs that can not use either a BOL, MSDN or Technet refrence to support the correct answer(s) also be excluded from publication.
[/rant]
I'm Confused. There was an MSDN reference along with a blog post. Both were included.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 20, 2012 at 7:48 am
Nice question and a good way to start the week.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 20, 2012 at 8:25 am
Nice and easy question to start the week.
I was told once before (by an Oracle DBA) that using COUNT(*) was not a recommended practice as it would cause performance issues because the database engine would load each row of the table into memory to increase the count, and it was a better option to use COUNT(1) instead. Does anybody know anything about this, if it applies to MS SQL?
August 20, 2012 at 8:37 am
Steve Jones - SSC Editor (8/20/2012)
bitbucket-25253 (8/19/2012)
QODs using images instead of easily cut and paste text have now been excluded from being published, I would propose to the powers that be that those submitted QQODsthat can not use either a BOL, MSDN or TTechnetrefrence to support the correct answer(s) also be excluded from publication.
[/rant]
I'm Confused. There was an MSDN reference along with a blog post. Both were included.
Guess I was confused as well, answered too early and without a cup of coffee, so did not notice the second supporting link, and interpreted the sequence of supporting documentation in the order listed. Meaning in my feeble mind their order of importance.
August 20, 2012 at 8:51 am
EL Jerry (8/20/2012)
I was told once before (by an Oracle DBA) that using COUNT(*) was not a recommended practice as it would cause performance issues because the database engine would load each row of the table into memory to increase the count, and it was a better option to use COUNT(1) instead. Does anybody know anything about this, if it applies to MS SQL?
As far as I know (which, in respect to Oracle or ancient SQL Server versions, is admittedly not very far):
1. The Oracle DBA was confusing COUNT(*) with EXISTS (SELECT * FROM ...). Oracle treats COUNT(*) and COUNT(1) exactly equal. Current versions of Oracle also treat EXISTS (SELECT 1 ...) and EXISTS (SELECT * ...) exactly equal, but I have read a few times that older version of Oracle would indeed bring in entire rows for EXISTS (SELECT * ...).
2. For SQL Server, there is no and never has been any difference between COUNT(*) and COUNT(1).
3. For the EXISTS (SELECT * FROM ...) or EXISTS (SELECT 1 FROM ...) debate, I have read conflicting things. SQL Server will definitely not fetch entire rows in either case, but some people insist that ancient versions of SQL Server did hit the metadata tables to expand the SELECT * into a column list, which would then be ignored in the next phase of query optimization - and other people disagree. For current versions of SQL Server, there is definitely no difference at all.
EDIT: I found a link about COUNT(*) vs COUNT(1) on Oracle, saying that there is no difference. If you read the comments as well, you will learn that there was a difference in old (7.x and before) version - and that, in fact, COUNT(1) is slower there, not faster.
EDIT 2: I also ran a quick test to prove that SQL Server does not even evaluate the * or 1 in both the COUNT and the EXISTS situation - you can put something illegal there, and it will still run:
if exists (select cast('a' as int) from sys.objects) print 'yes';
select count(cast('a' as int)) from sys.objects;
August 20, 2012 at 9:26 am
Hugo Kornelis (8/20/2012)
EDIT 2: I also ran a quick test to prove that SQL Server does not even evaluate the * or 1 in both the COUNT and the EXISTS situation - you can put something illegal there, and it will still run:
if exists (select cast('a' as int) from sys.objects) print 'yes';select count(cast('a' as int)) from sys.objects;
Interesting. One could argue that this is a bug in SQL server, since cast('a' as int) logically cannot exist...
Now, try this:
if exists (select CAST('a' as int) from sys.objects) print 'yes'
if exists (select CAST(name as int) from sys.objects) print 'yes -2'
select COUNT(cast('a' as int)) from sys.objects
select COUNT(cast(name as int)) from sys.objects
EDIT: Here are some other tests to muddy the waters
if exists (select CAST('a' as int)+GETDATE() from sys.objects) print 'yes -3'
if exists (select CAST('a' as int)+NEWID() from sys.objects) print 'yes -4'
select COUNT(cast('a' as int)+GETDATE()) from sys.objects
select COUNT(cast('a' as int)+NEWID()) from sys.objects
So the question is, how does SQL Server decide to interpret the expression?
August 20, 2012 at 10:09 am
Good question, altough not clear at all:
I don't know if [Table B] is single-column or multi-column, but if it's multi-column, null values are storeds in [Column Name]? I assumed that it was so.
I was right because my assumption was correct. But I prefer a specification enough clear, instead of the best of my assumptions.
😉
August 20, 2012 at 10:11 am
EL Jerry (8/20/2012)
I was told once before (by an Oracle DBA) that using COUNT(*) was not a recommended practice as it would cause performance issues because the database engine would load each row of the table into memory to increase the count, and it was a better option to use COUNT(1) instead. Does anybody know anything about this, if it applies to MS SQL?
It certainly doesn't apply to MSSQL and probably never has.
But it was true in the bad old days of Oracle 5. Also in those days, the query plan used could depend on which of two indexes was created first 🙂
August 20, 2012 at 10:26 am
sknox (8/20/2012)
Interesting. One could argue that this is a bug in SQL server, since cast('a' as int) logically cannot exist...
On one hand, I tend to agree with you. On the other hand, one could argue that this is a valid optimization shortcut. For EXISTS, the definition is that the existence of a row is checked, so the exact content of what follows the SELECT is irrelevant. For COUNT, there are two different meanings: COUNT(*) counts the number of rows, and COUNT(expression) counts the number of rows for which expression evaluates to a non-NULL value. If the optimizer sees that the expression is a non-NULL constant, there is no need to evaluate this for every row. Personally, I think it should still be evaluated once (to force the expected error in this case), but if you raise a bug on this on connect, expect to see it closed as either "won't fix" (as there are more important things to do), or even as "by design".
Now, try this:
if exists (select CAST('a' as int) from sys.objects) print 'yes'
if exists (select CAST(name as int) from sys.objects) print 'yes -2'
select COUNT(cast('a' as int)) from sys.objects
select COUNT(cast(name as int)) from sys.objects
I'm not surprised at the results. The first two use EXISTS, where the only interpretation is to check the existence of a row; what comes after SELECT is basically ignored. The fourth one is different from the preceding COUNT examples, because a column in the table is referenced. The expression is now not a constant; it could evaluate to NULL for some rows and to something other than NULL for other rows, so the expression has to be evaluated for each row.
EDIT: Here are some other tests to muddy the waters
if exists (select CAST('a' as int)+GETDATE() from sys.objects) print 'yes -3'
if exists (select CAST('a' as int)+NEWID() from sys.objects) print 'yes -4'
select COUNT(cast('a' as int)+GETDATE()) from sys.objects
select COUNT(cast('a' as int)+NEWID()) from sys.objects
The second one is the headscratcher here. I was first surprised to see this one fail. However, I can explain it as well - the reason is that this query causes an error in an earlier stage of query processing than the others.
As you may know, a query goes through several stages:
- Parsing (finding the being and start of keywords, checking that they are in an order that makes sense, and converting the whole thing to an internal format that is easier for the rest of the stages)
- Binding (linking the names of tables and colums in the query to the actual objects in the database)
- Optimizing (pondering the various ways the query can be executed and deciding on one that is good enough)
- Executing (fetching rows, and doing everything else that is needed to actually produce the results)
The conversion error that casting a string as integer forces is a runtime error. This error didn't show up in many queries because the optimizer had made a plan that omitted the conversion, to save some execution time.
The error forced by adding NEWID to the invalid cast (or, as a matter of fact, to anything at all), is produced at parse time. The parser recognises the CAST (... AS int) as an expression that returns an integer, the + as the addition symbol, and NEWID() as a function that returns a uniqueidentifier - and at that point, it balks at an invalid query.
It's easy to see that this error produces a parse-time error, not a run-time error, by creating a batch that starts with a valid query, followed by the invalid query. If the invalid query produces a run-time error, you'll get results from the first query, and then an error message. If the error is found during parsing, binding, or optimization, the error is produced and the batch aborted at that stage, before the first query even runs, so you get only the error message.
August 20, 2012 at 11:42 am
Got it right so I'm not complaining but I agree that there is room for error if there were other choices for the answer.
August 20, 2012 at 3:13 pm
Hugo Kornelis (8/20/2012)[hr
As far as I know {rest of his very useful quote omitted for brevity}
Hugo, when I was in school, everyone tried to copy off my paper. Had you gone to school with me, I'd have tried to copy off your paper! Thanks for the, as usual, great info written very well.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply