Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Predict the total count Expand / Collapse
Author
Message
Posted Tuesday, November 09, 2010 3:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 17, 2014 3:43 AM
Points: 146, Visits: 122
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
Post #1017733
Posted Tuesday, November 09, 2010 8:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:53 PM
Points: 1,676, Visits: 1,744
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
Post #1018003
Posted Tuesday, November 09, 2010 2:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 2,290, Visits: 2,544
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


-------------------
"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
Post #1018267
Posted Tuesday, November 09, 2010 5:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Post #1018323
Posted Friday, November 12, 2010 7:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 AM
Points: 49, Visits: 158
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.
Post #1019901
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse