select count(*)

Question

Post reply

select count(*)

swati-1123102

Valued Member

Points: 68
More actions
March 31, 2010 at 3:02 am

#100123

Hi All,

I was trying to retreive the row count of the big table which has got 2904003 records.

I used this query

SELECT COUNT(*) comp_qprob_data

and it returned the result as count 1

If you look at the query you will find that syntactically it is wrong.Have missed the FROM clause.

Still SQL server parses the query successfully and returns the result as count 1.When I saw the query execution result I was shocked 🙂

Can anyone please tell me why SQL server parser executed this command successfully?

Note :I am using SQL server 2005 standard edition.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

vaibhav.tiwari SSCommitted Points: 1946 More actions · Answer 1

COUNT(*) is nothing but count(1) or count(2) or any thing.

So whatever you are writing just after Count(*) is alias for the value.

even if you are using tablename it is not considering it table name thats why error is occuring for missing from clause.

I think nothing is wrong with that.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

Arjun Sivadasan

SSCertifiable

Points: 5902

April 1, 2010 at 3:34 am

#1143776

@vaibhav: the query runs fine. It does not cause an error.

@swati: count(*) returns the count in a group. In your query you haven't mentioned a group. So SQL Server merely returns 1 as the result. I don't think it is an error. It is just how it is meant to work.

https://sqlroadie.com/

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 3

The result 1 is obtained only when the table is in the default schema (dbo).

When the table is queried by specifying the schema name like (SELECT COUNT(*) FROM schema_name.table_name), the the following error message is obtained:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

Anyways, its good to know its behaviour in different circumstances.

John

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 4

hi Vaibhav,

U are right...

I tried the following two Queries

SELECT COUNT(*) tbl_object

tbl_object

1

SELECT COUNT(*) dbo.tbl_object

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

SELECT COUNT(0) A2z

A2z

1

So the query will recognise the objects name if FROM clause exists only...otherwise it has considered as Alias name.:-)

vaibhav.tiwari SSCommitted Points: 1946 More actions · Answer 5

Sasidhar Pulivarthi (4/1/2010)

hi Vaibhav,

U are right...

I tried the following two Queries

SELECT COUNT(*) tbl_object

tbl_object

1

SELECT COUNT(*) dbo.tbl_object

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

SELECT COUNT(0) A2z

A2z

1

So the query will recognise the objects name if FROM clause exists only...otherwise it has considered as Alias name.:-)

Yes, with reference to reply of BigM that if schemaname is concatenate with the table name then sql server identify that it is the table name then it will raise the error.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Diyas SSCarpal Tunnel Points: 4845 More actions · Answer 6

Sasidhar Pulivarthi (4/1/2010)

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

hi Vaibhav,

U are right...

I tried the following two Queries

SELECT COUNT(*) tbl_object

tbl_object

1

SELECT COUNT(*) dbo.tbl_object

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

SELECT COUNT(0) A2z

A2z

1

So the query will recognise the objects name if FROM clause exists only...otherwise it has considered as Alias name.

is this right......!

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 7

vaibhav.tiwari (4/1/2010)

Yes, with reference to reply of BigM that if schemaname is concatenate with the table name then sql server identify that it is the table name then it will raise the error.

Folks, I think we are just making assumptions here. How about checking this up with someone who knows the in and out of SQL Server?

@vaibhav: I don't think SQL Server identifies the alias as table name if you concatenate the schema name with it. Try this out.

SELECT COUNT(*) abc.def

I don't have a schema - 'abc' in my DB.

Now try this out

SELECT COUNT(*) abc-def

Both of the above queries give the same error. It is simply because the alias is not well formed. I don't think it is because the alias is identified as a table. Your thoughts?

- arjun

https://sqlroadie.com/

vaibhav.tiwari SSCommitted Points: 1946 More actions · Answer 8

Arjun Sivadasan (4/1/2010)

vaibhav.tiwari (4/1/2010)

Yes, with reference to reply of BigM that if schemaname is concatenate with the table name then sql server identify that it is the table name then it will raise the error.

Folks, I think we are just making assumptions here. How about checking this up with someone who knows the in and out of SQL Server?

@vaibhav: I don't think SQL Server identifies the alias as table name if you concatenate the schema name with it. Try this out.

SELECT COUNT(*) abc.def

I don't have a schema - 'abc' in my DB.

Now try this out

SELECT COUNT(*) abc-def

Both of the above queries give the same error. It is simply because the alias is not well formed. I don't think it is because the alias is identified as a table. Your thoughts?

- arjun

Very true...

and if we are putting this thing in [] then there is no need of formated alias.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Paul White SSC Guru Points: 150457 More actions · Answer 9

swati-1123102 (3/31/2010)

I used this query

SELECT COUNT(*) comp_qprob_data

and it returned the result as 1

If you look at the query you will find that syntactically it is wrong. Have missed the FROM clause.

[font="Verdana"]A SELECT statement without a FROM clause is valid in SQL Server.

You must have written things like SELECT 30 * 50 + 10 in the past, that does not have a from clause either 😉

What you wrote is exactly the same as SELECT COUNT(*) AS comp_qprob_data, since the AS keyword is optional for a column alias.

Notice that constructions like SELECT MIN(5) work too...it's perfectly valid.

The reason is that COUNT and MIN are aggregate functions, and always produce a result, even from an empty set.

For fun, try to predict the output from the following (thank you Rob Farley!):



SELECT  'No rows'

WHERE   1 = 2

HAVING  1 = 1;

If the result surprises you, please look here: http://msmvps.com/blogs/robfarley/archive/2010/01/12/t-sql-tuesday-having-puzzle-answer.aspx[/font]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 10

Arjun Sivadasan

SSCertifiable

Points: 5902

April 1, 2010 at 7:13 am

#1143931

Wow!!! Beautifully explained.

"Using HAVING (or a GROUP BY, DISTINCT or any aggregate function) means that any rows returned refer to groups not records. In this case, there is one group, containing no records."

Thanks a ton Paul.

- arjun

https://sqlroadie.com/

Paul White SSC Guru Points: 150457 More actions · Answer 11

Paul White

SSC Guru

Points: 150457

April 1, 2010 at 7:21 am

#1143940

No worries 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

Paul White NZ (4/1/2010)

If the result surprises you, please look here: http://msmvps.com/blogs/robfarley/archive/2010/01/12/t-sql-tuesday-having-puzzle-answer.aspx[/color][/font]

Paul, Great article:-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)