SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Predict the total count


Predict the total count

Author
Message
andy.roberts
andy.roberts
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 124
DhruvShah (11/7/2010)
yes Different region will have different answers but when no regions mentioned we normally take US as a default region specially when we talk about dates.


No WE certainly don't - especially when talking about dates.


I assume that when the writer uses the word 'we' without clarifying his default group then his default meaning is 'I'.

--------------------------------------------------------------

“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2157 Visits: 1816
Hugo Kornelis (11/9/2010)
Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)

Why would that be better?

Simply due to sanity reasons. I heard the rumor way back when in the dark years of my career when I had to work with Oracle that it was possible that count(*) extended to columns' sniffing, which was a pure waste in most cases, and therefore, it was best to avoid it. I believe that in those days it was possible to make the select count(*) from the_table statement break miserably should the calling user be denied of select on one of the columns in the table. In a mean time, count(1) guaranteed to return accurate count regardless of the columns and their respective nullability. Actually, Oracle developers of the last millennium frequently preferred to use count('X'), which offered superior performance when compared to

select count(anything_star_included_but_not_the_nullable_column_name) from the_table;



While this is true that in newer versions of Oracle count('X') is no longer special, and in SQL Server it does not make any difference whether count(*) or count(1) is used, many people like me still opt to avoid count(*) at all costs, just for sanity check if anything.

I understand that in SQL Server if the user executing the statement has explicit deny on any of the columns then the statement will fail regardless of whether count(1) or count(*) was used, but this is a rare scenario, which will force such user to use something like

select count(not_nullable_column) from the_table;

In this regard, there is no difference between count(1) and count(*), but I still like count(1) :-)

For example, if I have a user named TestUser in AdventureWorks database and the user has explicit select permission on HumanResources.Department table and explicit deny on ModifiedDate column like this:

grant select on HumanResources.Department to TestUser;
go

deny select on HumanResources.Department (ModifiedDate) to TestUser;
go

-- then

exec ('select count(DepartmentID) RecordCount
from HumanResources.Department') as user = 'TestUser';

-- runs just fine, returning 16 as RecordCount, but either

exec ('select count(1) RecordCount
from HumanResources.Department') as user = 'TestUser';

-- or

exec ('select count(*) RecordCount
from HumanResources.Department') as user = 'TestUser';


fails with:

Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'ModifiedDate' of the object 'Department', database 'AdventureWorks', schema 'HumanResources'.

Oleg
webrunner
webrunner
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7515 Visits: 3993
Peter Rijs (11/5/2010)
I agree with Bob: how should we know which date 40358 is, without executing a command?
Poor question, poor explanation as well (as pointed out in some other reactions).


Yes, I agree. Not just sour grapes. For those who happened to be correct that 40358 corresponds to a valid date, and a date that is one of the ones mentioned, it is hard to see how to get this answer aside from guessing or having chanced upon this value while working on something else.

Having said that, I picked 3 as the answer because I didn't look carefully that one date was 01/07/2010 and the other was 07/01/2010 -- which should have knocked out 3 as an answer right off the bat -- so I had different problems anyway. But I do think for those who are more observant, I think the 40358 critique still applies. :-)

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18167 Visits: 12426
Oleg Netchaev (11/9/2010)
Hugo Kornelis (11/9/2010)
Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)

Why would that be better?

Simply due to sanity reasons.
(...)
While this is true that in newer versions of Oracle count('X') is no longer special, and in SQL Server it does not make any difference whether count(*) or count(1) is used, many people like me still opt to avoid count(*) at all costs, just for sanity check if anything.


Hi Oleg,

I assumed that was the case. The reason I always prefer to use SELECT * in a COUNT (unless I relaly need to know the number of non-NULL values in a column) is that this clearly documents, a lot better than any other option, that I am counting ROWS, not VALUES.
And there is indeed no performance difference. THis might have been true in the dark ages, and on Oracle, but definitely not on current SQL Server versions.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
karlheinz.ulonska
karlheinz.ulonska
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 334
with german settings,I got

datefield intField
2010-07-01 00:00:00.000 1
2010-01-07 00:00:00.000 2
2010-01-07 00:00:00.000 3

and a result of 1, not 2.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search