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
>

Diff Datatypes used doubt int and bigint ........SQL SERVER 2005......Any useful links ?

Hello Frdz,

I have doubt regarding the datatypes fields used in SQL SERVER 2005.

The value for bigint Int64 is 18

The value of int Int32 is 9/10

Now,if in int i write : 1234567890 (accepted)

This gives error : 9874565656 (not accepted........why is it so ? )

Why is it so ??

I want to know the perfect size of all the datatypes used in SQLSERVER 2005.

There are also smallint,tinyint....

What's the main difference with all of them ??

Can anyone provide me the nice links which can explain me what m i asking in this post...

Please help me...I want to know all the datatypes used differences...

you cannot store 9874565656 in an int as it exceeds the max value for an int which is: 2,147,483,647

http://msdn2.microsoft.com/en-us/library/ms187745(SQL.90).aspx

|||

thanxs...i think it's helpful link..

I want to also know that in SQL SERVER 2005 can we assign or fix manually the values upto the limit...like,

Id1 int - 4

Id2 bigint - 10

like in varchar we can do

Name Varchar(20) if we take varchar(50) as datatype...

Hope u understand what i ask...this questions are not solved in my mind...

Please help me...

Thanxs again....

|||

varchar is a variable length datatype and you are allowed to set its length

you cannot do that with the integer datatypes.

|||

If you really intend to limit the value <= 4 digits ( = 9999) you can create a constraing on the column to make sure the value <= 9999.

|||

You can also create your own datatype that is based on an integer value type that can store all your possible values and has a constraint.

This may help:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

More complex datatype needs can be done via a CLR UDT, but I'm not sure how well they perform. Like:

http://www.devx.com/dotnet/Article/22644

|||

thanxs all of...

I think it's better to make a constraint......

Nice answers to clear my doubt...