difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Soura
do
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
David Portas
SQL Server MVP
|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanning the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustered index, the better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.phx.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.codecomments.com ***
|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:

> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Sourado
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
David Portas
SQL Server MVP
--|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any
column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanni
ng the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustere
d index, the better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.ph
x.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.codecomments.com ***|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:

> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Sourado
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
--
David Portas
SQL Server MVP
--|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanning the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustered index, the better.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.phx.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.developersdex.com ***|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>

Difference Between Comma Operator and Crossjoin?

I have two queries that appear to return the same result. Can someone explain how combining two dimensions with the comma operator differs from combining the same two dimensions using the crossjoin operator?

For example, two dimensions, account and sales. When I run this query with the cross join operator

select { [Measures].[Sales Count] } on columns,

{ ([Account].[Name].Allmembers *

[Date].[Month Val].[5] : [Date].[Month Val].

) } on rows

from warehouse

it returns the same results as this query when using the comma operator to create the set:

select { [Measures].[Sales Count] } on columns,

{ ([Account].[Name].Allmembers ,

[Date].[Month Val].[5] : [Date].[Month Val].

) } on rows

As crossjoin states, in example 1 above, every account is combined with every month value. When using the comma operator, I see the exact same results. Are these functionally equivalent? Any reason to use one method over the other?

Thanks....

AS2005 can be very forgiving - perhaps too forgiving - with MDX syntax...

First of all, despite appearances, there is no comma operator: the comma is used as a delimiter within a set and a tuple definition. Crossjoin you're ok with. What you're doing in your queries is producing a set of tuples containing every month crossjoined with every account and for the sake of clarity this is best written as an explicit crossjoin, either using the asterisk operator or the crossjoin function, something like this:

select { [Measures].[Sales Count] } on columns,
[Account].[Name].Allmembers * {[Date].[Month Val].[5] : [Date].[Month Val].}
on rows
from warehouse

In your first query you're putting the above crossjoin operation inside a set of parentheses (), which denote a tuple - and which AS is ignoring because you're producing a set of tuples anyway. In the second query because you've put a set definition rather than a member as the second item in your tuple definition AS must be assuming that you want to do a crossjoin and is again returning the set of tuples you're after, but this behaviour is new to me and from a maintenance point of view should probably be avoided.

Chris

|||

Hi Chris,

I'm wondering whether the comma syntax in the 2nd example (which I also recall being mentioned in Teo Lachev's As 2005 book) is technically a subcube expression, as defined for the SCOPE script statement or a Cell Calculation:

http://msdn2.microsoft.com/en-us/library/ms145989.aspx

>>

Subcube_Expression ::= (Auxiliary_Subcube [, Auxiliary_Subcube,...n])

Auxiliary_Subcube ::=
Limited_Set
| Root([dimension_name])
| Leaves([dimension_name])

>>

Mosha had mentioned in his blog that a subcube could get converted to a set, under certain circumstances (though unsupported):

http://sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

>>

How to work with Dimension Leaves

While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.

>>

|||

Hmmm, possibly... In which case, if Mosha says it's unsupported, it's definitely to be avoided.

Chris

|||Agreed...crossjoin works fine. Thanks for the feedback.

diferent way to a query

is theres a way to return the same result of this query without making the inner joins?

Code Snippet

create view missEeCuts
as
select distinct e.*
from events e
inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

GO

Maybe this, Luis:

Code Snippet

selectdistinct e.*

from events e

where

dbo.module(e.PxMiss,e.PyMiss)>=40 AND

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

ANDEXISTS(

select eventid,sum(px)as sum_px

from isolatedLeptons l

where e.idevent=l.eventid

groupby eventid

)

ANDEXISTS(

select eventid,sum(py)as sum_py

from isolatedLeptons l2

where e.idevent=l2.eventid

groupby eventid

)

Why do you not want the joins?

|||

Luis:

Why do you take "sum_px" and "sum_py" from different joins in:

Code Snippet

inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid

Can these two sums potentially be computed in a single join such as:

Code Snippet

inner join (
select eventid, sum(px) as sum_px, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid

If so, you might be able to eliminate the last INNER JOIN

|||

Dale:

Are "l.sum_px" "l2.sum_py" in scope in this line?

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

I thought these would be out of scope

|||

Ah man. I completely missed that.

Still early...where'd that coffee pot go?

Thanks Kent

determining who is logged into database server

Hi,
There used to be a command in mssql 6.5 that would tell
return the login names of users who had the database open,
ie, they were still in the app that connects to the db. I
think the command was something like @.@.who. Is there and
equivalent command in SQL 7 and 2k?
Also, is it possible to run sql 6.5 on a win2k server? It
keeps telling me the app is not loaded when I try to run
the service pack.
Thanks,
MikeI think you are looking for the system stored procedures
sp_who and sp_who2.
-Sue
On Mon, 3 Nov 2003 18:29:51 -0800, "Mike Stefani"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>There used to be a command in mssql 6.5 that would tell
>return the login names of users who had the database open,
>ie, they were still in the app that connects to the db. I
>think the command was something like @.@.who. Is there and
>equivalent command in SQL 7 and 2k?
>Also, is it possible to run sql 6.5 on a win2k server? It
>keeps telling me the app is not loaded when I try to run
>the service pack.
>Thanks,
>Mike|||Hi,
I think you are referring to SP_WHO system stored procedure. You can use the
same procedure in SQL 7.0 and SQL 2k from Query analyzer to identify
all the current processes (Users) connected to SQL server.
You can defenetely run SQL 6.5 is Win2k server , but as you said there is
lots of problems. Microsoft recommends not to run SQL 6.5 in Win2k.
Thanks
Hari
MCDBA
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike|||its sp_who
--
Shaju Thomas
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike